Grouping records based on intervals 15 min of time (MS SQL)

Question

I've got a monitoring system that is collecting data every 1 minutes. I'd like to aggregate the collected data by 15 minute intervals. I used this: (DATEPART (minute,date_report) / 15 ) but it does not work Is there any way to do? Any help is much appreciated!

My current query is:

SELECT DISTINCT fio,date_report, vocheredi,PS
 FROM
    (
    select distinct TempTable.fio,TempTable.date_report, count (distinct id) as vocheredi
,(CASE  WHEN TempTable.workgroup LIKE 'Group East' THEN 'East'
        WHEN TempTable.workgroup LIKE 'Group west' THEN 'West'
        WHEN TempTable.workgroup LIKE 'Group Centre' THEN 'Centre' END) as PS

    FROM dbo.TempTable

        WHERE
        TempTable.fio = 'employee'

        and (TempTable.workgroup = 'East' 
        or TempTable.workgroup = 'west' 
            or TempTable.workgroup = 'Centre')

            GROUP BY  TempTable.fio
                ,(CASE  WHEN TempTable.workgroup LIKE 'Group East' THEN 'East'
                    WHEN TempTable.workgroup LIKE 'Group west' THEN 'West'
                    WHEN TempTable.workgroup LIKE 'Group Centre' THEN 'Centre' END)
                        ,TempTable.date_report

                ) table
            WHERE
        PS = (@ReportParameter2)
    GROUP BY
(DATEPART (minute,date_report) / 15 ) ,fio,date_report, vocheredi,PS
ORDER BY date_report DESC

I want it to show me something like:

fio, date_report,vocheredi,PS
employee, 16:15:00-16:30:00 , 19,East

see http://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes for some pointers — P. O., Jan 25 '17 at 13:34
Possible duplicate of [How to group time by hour or by 10 minutes](http://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes) — Dan Field, Jan 25 '17 at 13:43

score 0 · Accepted Answer · answered Jan 25 '17 at 13:42

0

First, I don't thing you want 16:15:00 but 16:15:00 through 16:29:59 (or something like that). I'm not going to rewrite your entire script, but this will provide the grouping on the quarter hour

DATEADD(MINUTE,DATEPART(MINUTE,DateReport)%15*-1,DateReport)

to understand this DATEPART(MINUTES,DateReport) deliver this minutes (1 through 59) The % or mod function figure how many minutes are left over from an even 15.

DATEADd substracts this to get the starting quarter hour.

answered Jan 25 '17 at 13:42

Adam Jacobson

564
4
9

is inserted into the section GROUP BY? – AleksTr Jan 25 '17 at 13:47
Yes. This replace your group by where you have the /15 – Adam Jacobson Jan 25 '17 at 13:49

Grouping records based on intervals 15 min of time (MS SQL)

1 Answers1