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.