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.