SQLTeam.com | Weblogs | Forums

How to get average count for a column for last 4 weeks in a specific 1 hour window

sql2008

#1

Hi all,

Trying my best to explain it clearly here:
Table structure is like:

pkey		Home_Telephone	        ID						EDU_ID	        City	      State	Zip	Menu_Selection		Account_Number	Billing_System	Prin	FTA		Node	Company	Division	Email_Addr				                Create_time			system	MAC_ID			Service_Address
123456789	7778889999		00250087661438113227	5mm7mmm000000000mm10emm623300002	AAAAAAAA	MS	31234	Tier1			1234567891234567	C			5500	0050	EC6664	0		0			abc@xyz.com			2015-07-28 14:53:52.000	8381	NULL			123 MAIN DR  
123455789	2225558888		00250068021438191419	55n90nn000000000nn10ennn23300002	BBBBBBBB	MS	35678	NULL			8542687456248952	C			6600	0040	MA6610	0		0			hgf@itf.com 			2015-07-29 12:37:04.000	8381	NULL			456 CENTER RD

This type of data is available for past few days and Create_time column can be used as reference for timestamp.
What I am trying to do is if CURRENT_TIMESTAMP is say 2015-08-11 16:27:42.987
Then I need to fetch count(ID) grouped by each state where Create_time is between 2015-08-11 16:00:00.000 to 2015-08-11 16:27:42.987.

I am able to do this using following query (SQL server 2005):

select State, count(ID) as Call_Volume,
DATEADD(hour,DATEDIFF(hour,0,GETDATE()),0) as Start_Time,
GETDATE() as End_Time
from CTIRPTS.dbo.cti_reporting WHERE Create_time 
BETWEEN DATEADD(hour,DATEDIFF(hour,0,GETDATE()),0)
AND GETDATE()
AND State is not NULL
group by State order by count(ID) desc;

Then I want to fetch count(ID) grouped by each state where Create_time is between 16:00:00.000 to 17:00:00.000 for each day in past 4 weeks.
I could do this as well using following query:

DECLARE @number_of_days INT
SET @number_of_days = '7'
WHILE @number_of_days > 0
BEGIN
select State, count(ID) as Call_Volume,
DATEADD(hour,DATEDIFF(hour,0,GETDATE()-@number_of_days),0) as Start_Time,
DATEADD(hour,DATEDIFF(hour,0,GETDATE()-@number_of_days)+1,0) as End_Time
from CTIRPTS.dbo.cti_reporting WHERE Create_time BETWEEN
DATEADD(hour,DATEDIFF(hour,0,GETDATE()-@number_of_days),0)
AND DATEADD(hour,DATEDIFF(hour,0,GETDATE()-@number_of_days)+1,0)
AND State is not NULL
group by State order by count(ID) desc
SET @number_of_days = @number_of_days - 1
END

Now is there any way I can get weekly average of count(ID) by doing count(ID)/7 for each week in past grouped by each state where Create_time is between 16:00:00.000 to 17:00:00.000 for past 4 weeks.

I am trying to get output something like:

State	count(ID)	        Avg_Call_Volume1	        Avg_Call_Volume2	        Avg_Call_Volume3	        Avg_Call_Volume4
GA	(from 1st Query)        (from 2nd query, for week1)     (from 2nd query, for week2)	(from 2nd query, for week3)     (from 2nd query, for week4)
AL	(from 1st Query)	(from 2nd query, for week1)	(from 2nd query, for week2)	(from 2nd query, for week3)	(from 2nd query, for week4)

Note: This post is related to my earlier post "Need suggestion: How to get average, min and max of a column over fixed time duration" which was resolved by JamesK. I have tried to give all the details here as well.


#2

Not 100% sure what you want (for example, current month or last 4 weeks?). But maybe this:

SELECT State, count(ID) as Call_Volume,
    DATEPART(HOUR, GETDATE()) AS Hour,
    GETDATE() as End_Time
FROM CTIRPTS.dbo.cti_reporting 
WHERE Create_time BETWEEN DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) AND GETDATE()
  AND DATEPART(HOUR, Create_time) = DATEPART(HOUR, GETDATE())
  AND State is not NULL
GROUP BY State, DATEPART(HOUR, GETDATE());

#3

Sorry for confusion ScottPletcher. I accidently hit post before I coud provide all the details in the post. So edited the post and now it's complete.


#4

Maybe this:

declare @w0s datetime=dateadd(hh,datediff(hh,0,getdate()),0)
       ,@w0e datetime=getdate()
       ,@w1s datetime=dateadd(dd,datediff(dd,0,getdate())/7*7-7,0)
       ,@w1e datetime=dateadd(dd,datediff(dd,0,getdate())/7*7,0)
       ,@w2s datetime=dateadd(dd,datediff(dd,0,getdate())/7*7-14,0)
       ,@w2e datetime=dateadd(dd,datediff(dd,0,getdate())/7*7-7,0)
       ,@w3s datetime=dateadd(dd,datediff(dd,0,getdate())/7*7-21,0)
       ,@w3e datetime=dateadd(dd,datediff(dd,0,getdate())/7*7-14,0)
       ,@w4s datetime=dateadd(dd,datediff(dd,0,getdate())/7*7-28,0)
       ,@w4e datetime=dateadd(dd,datediff(dd,0,getdate())/7*7-21,0)
       ;

select state
      ,sum(case when create_time>=@w0s and create_time<@w0e then 1 else 0 end)
      ,sum(case when create_time>=@w1s and create_time<@w1e then 1 else 0 end)/7
      ,sum(case when create_time>=@w2s and create_time<@w2e then 1 else 0 end)/7
      ,sum(case when create_time>=@w3s and create_time<@w3e then 1 else 0 end)/7
      ,sum(case when create_time>=@w4s and create_time<@w4e then 1 else 0 end)/7
  from ctirpts.dbo.cti_reporting
 where create_time>=@w4s
   and datepart(hh,create_time)=hour(getdate())
   and state is not null
 group by state
;

#5

Hi bitsmed,

Thank you for helping me out with this.
The query does fetch the count for the present hour and weekly average for past 4 weeks.
The best part is it completes in just 1 second.

As I think I am using SQL server 2005 or some older version like that, I had to make few changes:
Change 1) I had to declare @w0s, @w0e, .... and then set those in two seperate steps. This I found from net when I tried to look for reason behind following error:

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@w0s".

Change 2) I changed line and datepart(hh,create_time)=hour(getdate()) with and datepart(hh,create_time)=datepart(hour, getdate())
Also, found this from net when I tried to look for reason behind following error:

Msg 195, Level 15, State 10, Line 29
'hour' is not a recognized built-in function name.

I think both above errors were due to the older version of SQL server I am on (2005)

I am understanding the query by going through it slowly and this is what I have understood so far:

  1. Declared variables as @w0s (start of week zero/present week), @w0e (end of week zero/present week), and so on until past 4 weeks.
  2. Got the specific date values in these variables like: @w0s has start of present hour with 00 minutes and @w0e is current time. @w1s has timestamp of last week (Monday midnight) 2015-08-03 00:00:00.000 and @w1e has timestamp of this week (Monday midnight) 2015-08-10 00:00:00.000
  3. This way we are taking data for past 4 weeks starting at Monday of the week at midnight and ending next Monday just before midnight.
  4. Then we are taking sum for each week and dividing it by 7 to get average for the day to display it.

I have just one question here:
If I understand it correct, we are taking sum of entire day when computing weekly average.
Is my understanding correct or we are taking sum of the ID column count(ID) only for that particular hour.
Like if the present time is 2015-08-12 10:05:53.600, then are we considering only 10 AM to 11 AM timeframe while computing sum from past days?

Please forgive me for such lengthy post and if I have not explained my last question clearly.

Note: Your query that I used (after minor modifications) looks like:

declare @w0s datetime
declare @w0e datetime
declare @w1s datetime
declare @w1e datetime
declare @w2s datetime
declare @w2e datetime
declare @w3s datetime
declare @w3e datetime
declare @w4s datetime
declare @w4e datetime
set @w0s=dateadd(hh,datediff(hh,0,getdate()),0)
set @w0e=getdate()
set @w1s=dateadd(dd,datediff(dd,0,getdate())/7*7-7,0)
set @w1e=dateadd(dd,datediff(dd,0,getdate())/7*7,0)
set @w2s=dateadd(dd,datediff(dd,0,getdate())/7*7-14,0)
set @w2e=dateadd(dd,datediff(dd,0,getdate())/7*7-7,0)
set @w3s=dateadd(dd,datediff(dd,0,getdate())/7*7-21,0)
set @w3e=dateadd(dd,datediff(dd,0,getdate())/7*7-14,0)       
set @w4s=dateadd(dd,datediff(dd,0,getdate())/7*7-28,0)
set @w4e=dateadd(dd,datediff(dd,0,getdate())/7*7-21,0)
select state
      ,sum(case when create_time>=@w0s and create_time<@w0e then 1 else 0 end) as Call_Volume_This_hr
      ,sum(case when create_time>=@w1s and create_time<@w1e then 1 else 0 end)/7 as Avg_Call_Volume_Past_Week1
      ,sum(case when create_time>=@w2s and create_time<@w2e then 1 else 0 end)/7 as Avg_Call_Volume_Past_Week2
      ,sum(case when create_time>=@w3s and create_time<@w3e then 1 else 0 end)/7 as Avg_Call_Volume_Past_Week3
      ,sum(case when create_time>=@w4s and create_time<@w4e then 1 else 0 end)/7 as Avg_Call_Volume_Past_Week4
  from ctirpts.dbo.cti_reporting
 where create_time>=@w4s
   --and datepart(hh,create_time)=hour(getdate())
   and datepart(hh,create_time)=datepart(hour, getdate())
   and state is not null
 group by state
;

#6

Correct :smile:

sum(case when create_time>=@w0s and create_time<@w0e then 1 else 0 end)

This checks to see whether create_time is between @w0s and @w0e (specifically: on-or-after @w0s but before @w0e), which represents the start/end date/time for that column in the result. If create_time is within that range then value=1, else value=0.

SUM() then totals all those values - so it counts 1 if create_time is within the range, and 0 if not.

Because the date/time ranges for each sum column are different then, in effect, each row will only have a Value=1 in ONE of those Sum Columns. At the end you wind up with the total number of records that have a create_time within that range. (Some of the columns then divide by 7, to get an average per-day, the first column's date/time range is from midnight-last-night until "now")


#7

If you look at the where statement, we only keep records within the current hour regardless of date:

and datepart(hh,create_time)=hour(getdate())

#8

Thank you Kristen and bitsmed.
Don't know how I missed this and condition in where clause.

This helped me to a great deal, thanks again bitsmed.