CODEWITHSUNDEEP
sql-server-2005
sventevit
sventevit

Reputation: 4816

Frequency of inserts

I have a table, which holds (besides all other data) information, when this data was inserted. Now i would like to know frequency of inserts (number of inserts per hour (or per 15 minutes)). Below is code, what I want to achieve. Note that I also want to show time interval where no inserts occured (in my example from 10:00 to 11:00).

if object_id('tempdb..#temp') is not null drop table #temp 
create table #temp (Id int identity(1,1), Date datetime)

insert into #temp (Date)
select '2009-10-05 08:01:00' union all
select '2009-10-05 08:22:00' union all
select '2009-10-05 08:23:00' union all
select '2009-10-05 08:24:00' union all
select '2009-10-05 09:30:00' union all
select '2009-10-05 11:01:00' union all
select '2009-10-05 11:05:00' union all
select '2009-10-05 11:52:00' union all
select '2009-10-05 12:01:00' union all
select '2009-10-05 12:05:00' union all
select '2009-10-05 12:07:00' union all
select '2009-10-05 12:09:00' union all
select '2009-10-05 12:20:00' union all
select '2009-10-05 12:30:00' union all
select '2009-10-05 12:40:00' union all
select '2009-10-05 12:50:00' union all
select '2009-10-05 12:55:00' union all
select '2009-10-05 13:30:00' union all
select '2009-10-05 13:35:00' union all
select '2009-10-05 13:40:00' union all
select '2009-10-05 14:01:00'

-- I want to get this data
select '08:00 - 09:00', 4 union all
select '09:00 - 10:00', 1 union all
select '10:00 - 11:00', 0 union all
select '11:00 - 12:00', 3 union all
select '12:00 - 13:00', 9 union all
select '13:00 - 14:00', 3 union all
select '14:00 - 15:00', 1

Upvotes: 0

Views: 116

Answers (1)

Ed Harper
Ed Harper

Reputation: 21505

This should do it. A recursive CTE is used to construct the list of hours on the fly:

;WITH rangeCTE
AS
(
        SELECT  MIN(DATE) minDate
                ,MAX(DATE) maxDate
        FROM #temp
)
,timeListCTE
AS
(
        SELECT CAST(CONVERT(CHAR(14),minDate,121) + '00:00' AS DATETIME) AS timeStart
               ,DATEADD(hh,1,CAST(CONVERT(CHAR(14),minDate,121) + '00:00' AS DATETIME)) AS timeEnd
               ,1 AS timeID
        FROM rangeCTE

        UNION ALL

        SELECT DATEADD(hh,1,timeStart)
               ,DATEADD(hh,2,timeStart)
               ,timeID + 1
        FROM timeListCTE
        WHERE timeStart <= (SELECT maxDate FROM rangeCTE)
)
SELECT tl.timeStart
       ,tl.timeEnd
       ,SUM(CASE WHEN t.Date IS NOT NULL 
                  THEN 1
                  ELSE 0
            END
           )
FROM      timeListCTE        AS tl
LEFT JOIN #temp              AS t
ON        t.DATE >= tl.timeStart
AND       t.DATE <  tl.timeEnd
GROUP BY tl.timeStart
        ,tl.timeEnd

Upvotes: 1

Related Questions