question

jamesinger avatar image
jamesinger asked

GROUP [CallDateTime] into 15 minutes intervals

I have a MS SQL Database for a phone system, that I'm accessing through PHP. Query to select is below: SELECT CallID, CallDateTime, IsLost, IsAnswered FROM vwCustomReportingCallsMain WHERE DDI = '2399' AND CallDateTime >= '2016-05-10' ORDER BY CallDateTime ASC I want to group the CallDateTime into 15 minutes intervals, IsLost and IsAnswered returns 1 for true and 0 for false, so it would display all lost, and all Answered within 15 minute intervals e.g - 2016-05-10 08:30 - 7 Answered - 3 Lost - Total 10 Any ideas?
sqldatetimegroup-byaggregates
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
GPO avatar image
GPO answered
--This bit is just to create and populate an example table of your transactional data. --IsAnswered appears to be entirely derived from other data, so is not needed at this point. SELECT top 100000 row_number() over(order by (SELECT NULL)) as CallId ,cast(dateadd(mi,-abs(checksum(newid()))%2880 ,dateadd(dd,datediff(dd,0,getdate()) ,0) )as datetime2(0) ) as CallDateTime ,cast(abs(checksum(newid()))%2 as tinyint) as IsLost ,2390 + abs(checksum(newid()))%10 as DDI INTO #vwCustomReportingCallsMain FROM sys.messages t1;--just use this table as the vehicle for creating a bunch of rows --Establish start and end dates and the number of 15 minute increments. I've just chosen this start_date arbitrarily to suit the data. DECLARE @start_date as datetime2(0) = (SELECT min(crcm.CallDateTime) FROM #vwCustomReportingCallsMain crcm); SET @start_date = dateadd(dd,datediff(dd,0,@start_date),0); --set date forward to midnight the next night DECLARE @end_date as datetime2(0) = (SELECT max(crcm.CallDateTime) FROM #vwCustomReportingCallsMain crcm); SET @end_date = dateadd(dd,datediff(dd,0,@end_date)+1,0); --set date forward to midnight the next night DECLARE @nbr_of_increments as int = 4 * datediff(hh,@start_date,@end_date); --for 15 min increments create four times the number of hours in rows. --Set up a calendar table with 15 minute increments WITH cal as ( SELECT top(@nbr_of_increments) dateadd(MI ,(15*(row_number() over(order by (SELECT NULL))))-15 ,@start_date ) as CensusTime FROM sys.messages t1 --just use this table as the vehicle for creating a bunch of rows ) SELECT cal.CensusTime --note: Don't count(*) with the left join below or you'll count rows where no calls were made ,isnull(count(crcm.CallDateTime),0) as TotalCalls ,isnull(sum(crcm.IsLost),0) as CallsLost ,isnull(count(crcm.CallDateTime),0) - isnull(sum(crcm.IsLost),0) as CallsAnswered FROM cal --It's likely that you'll want to show 15 minute intervals that had zero calls, so LEFT JOIN here. LEFT JOIN #vwCustomReportingCallsMain crcm ON crcm.CallDateTime >= cal.CensusTime and crcm.CallDateTime < dateadd(mi,15,cal.CensusTime) and crcm.DDI = '2399' GROUP BY cal.CensusTime ORDER BY cal.CensusTime; --Now bear in mind that the most important advice of all here is that all of this becomes much easier --if you already have a calendar table and a tally table in your database. Make sure you google them.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wow, that's an awesome answer, I'll try to figure it out and come back with a reply, Thanks so much GPO
1 Like 1 ·
Thanks for the endorsement :-) @jamesinger Don't forget to mark it as correct if it does what you want.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.