Need Query to populate count by week

[renee35]

Hello,

I am trying to create a report that gets me a count of orders created per week. I have the below query so far, but keep getting an error that states "GROUP BY expressions must refer to column names that appear in the select list."

I am a newbie to this and would greatly appreciate some help on how to make this query work. What I am trying to achieve is below:

select
datename(year,getdate()) +'-'+right('00' + cast(datepart(week,getdate()) as varchar),2),
dateadd(day,2-datepart(weekday,TODO_PLAN_START_DT),datediff(day,0,TODO_PLAN_START_DT)), 2-datepart(weekday,TODO_PLAN_START_DT),
COUNT (OPTY_ID)
as 'Net New'
From
(
Select
'Start' = min (TODO_PLAN_START_DT), OPTY_ID
From S_EVT_ACT
where TODO_CD='Onsite Demo' and
TODO_PLAN_START_DT between '04/01/06' and '03/31/2008'
GROUP BY OPTY_ID
)
as Mtgs
GROUP BY datename(year,getdate()) +'-'+right('00' + cast(datepart(week,getdate()) as varchar),2),
dateadd(day,2-datepart(weekday,TODO_PLAN_START_DT),datediff(day,0,TODO_PLAN_START_DT)), 2-datepart(weekday,TODO_PLAN_START_DT)


Thanks a bunch!!


Thanks a bunch!!

-T

 

[xlbo]

This has nothing to do with SS Reporting Services.

Please post in a relevant forum

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question