May 1, 2013 at 11:26 am
Hello all,
I have the following dataset that I am using:
select
HourOfTheDay
,sum(NumberOfOrdersInBatch) as NumberOfOrders
,AVG(TimeToProcess) as AverageTime
from
info.batchstatsTEST with (nolock)
where
TimeReceived between GETDATE()-1 and GETDATE()
group by
HourOfTheDay
What I want to do is add the AverageTime from a different date range, say between GETDATE()-8 and GETDATE()-7. So what I am looking for is a new column in the dataset called AverageTime2 that shows me the previous week. How can I get this in the same dataset?
Thanks!
May 1, 2013 at 12:15 pm
Give this a shot:
select
HourOfTheDay,
sum(case when TimeReceived between GETDATE()-1 and GETDATE() then NumberOfOrdersInBatch else 0 end) as NumberOfOrders,
AVG(case when TimeReceived between GETDATE()-1 and GETDATE() then TimeToProcess else 0 end) as AverageTime,
sum(case when TimeReceived between GETDATE()-8 and GETDATE()-7 then NumberOfOrdersInBatch else 0 end) as PrvWkNumberOfOrders,
AVG(case when TimeReceived between GETDATE()-8 and GETDATE()-7 then TimeToProcess else 0 end) as PrvWkAverageTime
from
info.batchstatsTEST with (nolock)
where
TimeReceived between GETDATE()-1 and GETDATE() or
TimeReceived between GETDATE()-8 and GETDATE()-7
group by
HourOfTheDay;
May 1, 2013 at 12:32 pm
Perfect! Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply