2

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?

Vincent
  • 2,073
  • 1
  • 17
  • 24
  • 1
    The 15 minutes grouping part is a duplicate of [SQL - Break a start/end time into 15 minute records](http://stackoverflow.com/questions/2921760/sql-break-a-start-end-time-into-15-minute-records). The running total part is a duplicate of [Calculate a Running Total in SqlServer](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver). – GSerg Dec 19 '11 at 11:47
  • Thanks, ik can use the Calculate a Running Total in SqlServer, but the Break start/end time into 15 minutes is diferent from what i want. – Vincent Dec 19 '11 at 11:52

1 Answers1

2

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.

Djoul6
  • 309
  • 1
  • 7
Jonathan Veiler
  • 89
  • 2