December 18, 2019 at 4:32 am
Hi Experts
I try to count calls in different interval's for example by by day in 10 min intervals or 15 min intervals for example
with the query here its working by day and hours but its all static. I am lucking for a more flexible dynamic ways.
I am sure there is a better way, your suggestions are greatly appreciated.
select CAST(starttime AS date) as 'Date',
SUM(CASE WHEN DATEPART(hour, StartTime) = 0 THEN 1 ELSE 0 END) as '00',
SUM(CASE WHEN DATEPART(hour, StartTime) = 1 THEN 1 ELSE 0 END) as '01',
SUM(CASE WHEN DATEPART(hour, StartTime) = 2 THEN 1 ELSE 0 END) as '02',
SUM(CASE WHEN DATEPART(hour, StartTime) = 3 THEN 1 ELSE 0 END) as '03',
SUM(CASE WHEN DATEPART(hour, StartTime) = 4 THEN 1 ELSE 0 END) as '04',
SUM(CASE WHEN DATEPART(hour, StartTime) = 5 THEN 1 ELSE 0 END) as '05',
SUM(CASE WHEN DATEPART(hour, StartTime) = 6 THEN 1 ELSE 0 END) as '06',
SUM(CASE WHEN DATEPART(hour, StartTime) = 7 THEN 1 ELSE 0 END) as '07',
SUM(CASE WHEN DATEPART(hour, StartTime) = 8 THEN 1 ELSE 0 END) as '08',
SUM(CASE WHEN DATEPART(hour, StartTime) = 9 THEN 1 ELSE 0 END) as '09',
SUM(CASE WHEN DATEPART(hour, StartTime) = 10 THEN 1 ELSE 0 END) as '10',
SUM(CASE WHEN DATEPART(hour, StartTime) = 11THEN 1 ELSE 0 END) as '11',
SUM(CASE WHEN DATEPART(hour, StartTime) = 12 THEN 1 ELSE 0 END) as '12',
SUM(CASE WHEN DATEPART(hour, StartTime) = 13 THEN 1 ELSE 0 END) as '13',
SUM(CASE WHEN DATEPART(hour, StartTime) = 14 THEN 1 ELSE 0 END) as '14',
SUM(CASE WHEN DATEPART(hour, StartTime) = 15 THEN 1 ELSE 0 END) as '15',
SUM(CASE WHEN DATEPART(hour, StartTime) = 16 THEN 1 ELSE 0 END) as '16',
SUM(CASE WHEN DATEPART(hour, StartTime) = 17 THEN 1 ELSE 0 END) as '17',
SUM(CASE WHEN DATEPART(hour, StartTime) = 18 THEN 1 ELSE 0 END) as '18',
SUM(CASE WHEN DATEPART(hour, StartTime) = 19 THEN 1 ELSE 0 END) as '19',
SUM(CASE WHEN DATEPART(hour, StartTime) = 20 THEN 1 ELSE 0 END) as '20',
SUM(CASE WHEN DATEPART(hour, StartTime) = 21 THEN 1 ELSE 0 END) as '21',
SUM(CASE WHEN DATEPART(hour, StartTime) = 22 THEN 1 ELSE 0 END) as '22',
SUM(CASE WHEN DATEPART(hour, StartTime) = 23 THEN 1 ELSE 0 END) as '23'
from CDR
where starttime > '2019-12-01'
December 18, 2019 at 9:56 am
If you could show us some DDL and sample data for the CDR table, and some examples of the output you'd like to see, it would be easier to help you. I think I'd create a separate table of dates and times and join to that.
John
December 18, 2019 at 3:43 pm
declare @minutes_interval int
set @minutes_interval = 15 /*or 10 or 60 or whatever*/
select
dateadd(minute, datediff(minute, 0, starttime) / @minutes_interval * @minutes_interval, 0),
SUM(1) AS row_count
from CDR
where starttime > '20191201'
group by dateadd(minute, datediff(minute, 0, starttime) / @minutes_interval * @minutes_interval, 0)
order by dateadd(minute, datediff(minute, 0, starttime) / @minutes_interval * @minutes_interval, 0)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply