I need help in creating SQL query that retrieves downtime entries for each shift time period. Below are the considerations
- A downtime can start and finish in same shift time period.
- A downtime can start in a shift time period and finish in another shift time period.
I have two tables
DowntimeEntry
| DowntimeDataID | DowntimeStart | DowntimeFinish | EventCount |
|---|---|---|---|
| 41292 | 2013-01-02 00:28:00 | 2013-01-02 00:58:00 | 1 |
| 41293 | 2013-01-02 07:29:00 | 2013-01-02 14:29:00 | 1 |
and ShiftCalendar
| ShiftStartTime | ShiftEndTime |
|---|---|
| 2013-01-02 00:00:00 | 2013-01-02 08:00:00 |
| 2013-01-02 08:00:00 | 2013-01-02 14:29:00 |
| 2013-01-02 16:00:00 | 2013-01-03 00:00:00 |