Each GROUP BY expression must contain issue... 1

[BeachSandGuy]

Hi, I searched and couldn't find an answer in this forum. I have a query using the group by function to aggregate weekly volumes of calls and dumping into a separate databse. It is designed to be automated, so requires for each pull to include the beginning of the week date. However my function to pull the start of week date has a constant in it which gives me the error "Each GROUP BY expression must contain at least one column that is not an outer reference." Can anyone think of a workaround? I really need that date field in there. Thanks for any help. Here is a snippet of that code:

Select
[B](DateAdd(d, -((@@DATEFIRST + DatePart(dw, (CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME ) -7)) -2) % 7),
(CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME ) -7))) as StartofWeek[/b],
BUSINESS_UNIT.business_unit_nm as 'BU',
smry_tcd_transfers.node as 'Node', smry_tcd_calls.skillgroupperipheralnumber as 'OriginalSkill',
Count(start_dttm) as CallCount

from smry_tcd_transfers

inner join smry_tcd_calls on smry_tcd_transfers.vcc_call_var_key_id = smry_tcd_calls.call_id and smry_tcd_transfers.agent_id = smry_tcd_calls.agent_id
inner join BUSINESS_UNIT on BUSINESS_UNIT.business_unit_id = smry_tcd_calls.business_unit_id

where
smry_tcd_transfers.start_dttm >= DateAdd(d, -((@@DATEFIRST + DatePart(dw, (CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME ) -7)) -2) % 7),(CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME ) -7))
AND smry_tcd_transfers.start_dttm <= (DateAdd(d, -((@@DATEFIRST + DatePart(dw, (CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME ) -7)) -2) % 7),(CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME ) -7))) + 5

Group By
[B](DateAdd(d, -((@@DATEFIRST + DatePart(dw, (CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME ) -7)) -2) % 7),(CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME ) -7)))[/B],
BUSINESS_UNIT.business_unit_nm,
smry_tcd_transfers.node, smry_tcd_calls.skillgroupperipheralnumber

Ian

 

[gmmastros]

Try removing the "Start Of Week" expression from the group by clause. Since this is a constant, it's not necessary to have it in the group by anyway. The results would be the same and the query should work.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BeachSandGuy]

Thanks gmmastros! I would normally agree with you, however I am having this query auto run weekly and having the data auto dump into a separate SQL dbase for reporting services website. If I take out the constant, I lose my date field when appending to other weekly data. Is there a better approach to getting the date field in there?

 

[markros]

Why do you need such complex expression as GROUP BY anyway? Why not group by d ? In case you want this expression, then I suggest to use a derived table and have this field there as a new calculated field and then group by this new field.

PluralSight Learning Library

 

[BeachSandGuy]

I am concerned about the number of records returned. Was thinking by condensing down to a weekly date, it would help. But that gives me an idea, I supposed I could group on year and week number and can convert that with a different query in the reporting services. OR if you have a better way of calculating starting date of week as opposed to the complex expression I used, I'd love to see it. Thanks for your suggestion, I am really a beginner though and not sure how to do a derived table as you suggested.

 

[gmmastros]

What I meant was to ONLY remove it from the group by clause. Leave it in the select and leave it in the where, only remove from group by.

Try this:

Select
(DateAdd(d, -((@@DATEFIRST + DatePart(dw, (CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME ) -7)) -2) % 7),
(CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME ) -7))) as StartofWeek,
BUSINESS_UNIT.business_unit_nm as 'BU',
smry_tcd_transfers.node as 'Node', smry_tcd_calls.skillgroupperipheralnumber as 'OriginalSkill',
Count(start_dttm) as CallCount

from smry_tcd_transfers

inner join smry_tcd_calls on smry_tcd_transfers.vcc_call_var_key_id = smry_tcd_calls.call_id and smry_tcd_transfers.agent_id = smry_tcd_calls.agent_id
inner join BUSINESS_UNIT on BUSINESS_UNIT.business_unit_id = smry_tcd_calls.business_unit_id

where
smry_tcd_transfers.start_dttm >= DateAdd(d, -((@@DATEFIRST + DatePart(dw, (CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME ) -7)) -2) % 7),(CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME ) -7))
AND smry_tcd_transfers.start_dttm <= (DateAdd(d, -((@@DATEFIRST + DatePart(dw, (CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME ) -7)) -2) % 7),(CAST( FLOOR( CAST( getDate() AS FLOAT ) ) AS DATETIME ) -7))) + 5

Group By
BUSINESS_UNIT.business_unit_nm,
smry_tcd_transfers.node, smry_tcd_calls.skillgroupperipheralnumber

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BeachSandGuy]

Thank you VERY much! That worked perfectly. I misunderstood, and assumed I had to remove from the group by clause too. Appreciate yours and markros input.