1

Sorry for the poorly worded title, but I am not quite sure how to explain exactly what I want to do.

I have a set of data in a table as such:

 |Duration|    StartDate              |Machine|
 |--------|---------------------------|-------|
 |192013  |2017-06-27 09:01:29.0800000|   1   |
 |--------|---------------------------|-------|
 |154199  |2017-06-27 19:31:43.2680000|   2   |
 |--------|---------------------------|-------|
 |244452  |2017-06-26 18:27:30.8160000|   3   |
 |--------|---------------------------|-------|

(Duration is in second)

What I would like to do is, with each row (which has 1 unique machine) is to break up this Duration value into sections of 8 hour/28800 seconds chunks (which correlate to first, second, and third shift) and associate a date and with this newly calculated duration.

For example, my desired output for Machine 1 would be:

 |Duration|    StartDate              |Machine|
 |--------|---------------------------|-------|
 |22411   |2017-06-27 09:01:29.0800000|   1   |
 |--------|---------------------------|-------|
 |28800   |2017-06-27 15:15:00.0000000|   1   |
 |--------|---------------------------|-------|
 |28800   |2017-06-27 23:15:00.0000000|   1   |
 |--------|---------------------------|-------|
 |28800   |2017-06-28 07:15:00.0000000|   1   |
 |--------|---------------------------|-------|
 |28800   |2017-06-28 15:15:00.0000000|   1   |
 |--------|---------------------------|-------|
 |28800   |2017-06-28 23:15:00.0000000|   1   |
 |--------|---------------------------|-------|
 |25602   |2017-06-29 07:15:00.0000000|   1   |
 |--------|---------------------------|-------|

Hopefully the example helps convey what I want to say.

P.S. I am using SQL SERVER 2008 R2.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
J. Boyer
  • 31
  • 1
  • 6
  • Why do you want your first duration to be 22411 instead of 28800? – Tab Alleman Jun 29 '17 at 19:27
  • It was calculated as 22411 because 2 hours have already elapsed in the time-frame I want to add the duration to. I guess I should have stated I wanted to put the duration in "boxes" in the ranges of: 07:15-15:15, 15:15-23:15, 23:15-07:15. This is also why the last entry is not 28800. – J. Boyer Jun 29 '17 at 19:31
  • The duplicate question uses 15 minute blocks instead of 8 hours, and doesn't have your "shifts" requirement, but the answer there is adaptable to your needs. – Tab Alleman Jun 29 '17 at 19:32
  • That looks like a very good example to go by! Thank you for finding that, guess I was using the wrong keywords for searching... – J. Boyer Jun 29 '17 at 19:57
  • 1
    I googled "sql server breaking a duration into time blocks" It's a talent. : ) – Tab Alleman Jun 29 '17 at 20:00

0 Answers0