![]() SELECT DATEADD(MINUTE, DATEDIFF(…) / * '2000') ![]() If you change the interval a lot, you might benefit from declaring it as a variable. For example, 10 with MINUTE is ten minute intervals 6 with HOUR is six hour intervals. The MINUTE and 10 terms can be changed to any DATEPART and integer, 1 respectively, to group into different time intervals. GROUP BY DATEDIFF(MINUTE, '2000', aa.) / 10ĭetails and extra commentary Bin interval size With heavy acknowledgements to Derek's answer, which forms the core of this one. You can modify following code to meet your need.Short and sweet GROUP BY DATEDIFF(MINUTE, '2000', date_column) / 10 You probably want this in another table with some eventid to keep tack of which one is related to which operation. Shift_date | Hour | Status | Duration (min) WHEN h > DATEPART(hour, Start_timestamp)AND h < DATEPART(hour, End_timestamp) THEN 60 WHEN h = DATEPART(hour, End_timestamp) THEN DATEPART(minute, End_timestamp) create table register(Shift_date datetime, Status varchar(20), Start_timestamp datetime, End_Timestamp datetime) Basically it calculates minutes that fits each hour and finally, it calculates the total time of every group. I'd suggest to use an hours table to help with this job. ║ Shift_date ║ hour ║ Status ║ Duration ║ , dateadd(hour, 1 + n.n + datediff(hour, 0, t.Start_timestamp), 0) end_from_numbersįROM numbers n WHERE DATEDIFF(HOUR, t.Start_timestamp, t.End_Timestamp) >= n.n SELECT dateadd(hour, n.n + datediff(hour, 0, t.Start_timestamp), 0) start_from_numbers , CASE WHEN t.End_timestamp > n.end_from_numbers THEN n.end_from_numbers ELSE t.End_timestamp END end_final , CASE WHEN t.Start_timestamp > n.start_from_numbers THEN t.Start_timestamp ELSE n.start_from_numbers END start_final , DATEDIFF(MINUTE, start_final, end_final) Duration One approach which should work on 2008: - may need more rows here depending on how long the events can be From there, you just need to account for all of the different cases to figure out how many minutes for the status are relevant for each hour bucket. I'm doing this by joining to a numbers table. The basic idea here is to add rows for each row in the status table depending on how many hours it covers. Shift_date Hour Status Duration (minutes) Shift_date Status Start_timestamp End_Timestamp If the status was "Operating" from 15:20 to 17:10, I want to see that it was 40 minutes on Operating for the 16th hour of the day, 60 minutes operating on the 17th hour and 10 minutes operating on the 18th hour of the day. But now I want to know for every hour what the status is. I have a table that registers the status for equipment.
0 Comments
Leave a Reply. |