question

--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.