CODEWITHSUNDEEP
sqlt-sqlstored-procedures
Vincent
Vincent

Reputation: 2093

sql query break into 15 minutes

Have a table with a datetime_in, datetime_out, card_id, group. Know i want a output in the following matters:

group  ; date       ;  quarterTime    ; persons_in ; hours
group1 ; 17-02-2010 ; 00:00 - 00:15  ; 0          ; 0
group1 ; 17-02-2010 ; 00:15 - 00:30  ; 0          ; 0
group1 ; 17-02-2010 ; 00:30 - 00:45  ; 0          ; 0
~etc
group1 ; 17-02-2010 ; 13:00 - 12:15  ; 334        ; 1483

end then the same for all the groups starting with 00:00 until 23:45

so for each quearter of the day i want to have a record, grouped by the group, date (in days) and quarterTime. Then in persons_in i want the record count within that quarter, and in hours how many person hours are worked in total for that day until the end of the current quarter. so that only get higher and higer per day, while the persons_in count only is within that quarter. So my question is, I now have a really dirty C# script doing this, for optimalization i want to run is in just 1 query.

I dont mind using stored procedures, but since im not really good in sql i prefer T-SQL.

Anybody has in idea how to do this? examples?

Upvotes: 2

Views: 357

Answers (1)

Jonathan Veiler
Jonathan Veiler

Reputation: 89

You can use this logic to transform your dates for grouping:

SELECT 
        CONVERT(bigint,Year(GETDATE())) * power(10,7) +
        Month(GETDATE()) * POWER(10,5) +
        Day(GETDATE()) * POWER(10,3) +
        Datepart(hh,GETDATE()) * POWER(10,1) +
        DATEPART(mi,GETDATE()) / 15 AS Agg_Date

19/12/2011 13:31 => 20111219132

Each 15 minutes have the same key.

Upvotes: 2

Related Questions