SUM of production counts for "overnight work shift" in MS SQL (2019)

Vomec 0
Viewed 41

I need some help regarding sum of production count for overnight shifts.

The table just contains a timestamp (that is automaticaly generated by SQL server during INSERT), the number of OK produced pieces and the number of NOT OK produced pieces in that given timestamp.

CREATE TABLE [machine1](
    [timestamp] [datetime] NOT NULL,
    [OK] [int] NOT NULL,
    [NOK] [int] NOT NULL
)
ALTER TABLE [machine1] ADD  DEFAULT (getdate()) FOR [timestamp]

The table holds values like these (just an example, there are hundreds of lines each day and the time stamps are not fixed like each hour or each 30mins):

timestamp OK NOK
2022-08-01 05:30:00.000 15 1
2022-08-01 06:30:00.000 18 3
... ... ...
2022-08-01 21:30:00.000 10 12
2022-08-01 22:30:00.000 0 3
... ... ...
2022-08-01 23:59:00.000 1 2
2022-08-02 00:01:00.000 7 0
... ... ...
2022-08-02 05:30:00.000 12 4
2022-08-02 06:30:00.000 9 3

The production works in shifts like so:

  1. morning shift: 6:00 -> 14:00
  2. afternoon shift: 14:00 -> 22:00
  3. night shift: 22:00 -> 6:00 the next day

I have managed to get sums for the morning and afternoon shifts without issues but I can't figure out how to do the sum for the night shift (I have these SELECTs for each shift stored as a VIEW for easy access).

For the morning shift:

SELECT CAST(timestamp AS date) AS Morning,
        SUM(OK) AS SUM_OK,
        SUM(NOK) AS SUM_NOK
FROM [machine1]
WHERE DATEPART(hh,timestamp) >= 6 AND DATEPART(hh,timestamp) < 14
GROUP BY CAST(timestamp AS date)
ORDER BY Morning ASC

For the afternoon shift:

SELECT CAST(timestamp AS date) AS Afternoon,
        SUM(OK) AS SUM_OK,
        SUM(NOK) AS SUM_NOK
FROM [machine1]
WHERE DATEPART(hh,timestamp) >= 14 AND DATEPART(hh,timestamp) < 22
GROUP BY CAST(timestamp AS date)
ORDER BY Afternoon ASC

Since we identify the date of each shift by its start, my idea would be that the result for such SUM of night shift would be

Night SUM_OK SUM_NOK
2022-08-01 xxx xxx for interval 2022-08-01 22:00:00.000 -> 2022-08-02 05:59:59.999
2022-08-02 xxx xxx for interval 2022-08-02 22:00:00.000 -> 2022-08-03 05:59:59.999
2022-08-03 xxx xxx for interval 2022-08-03 22:00:00.000 -> 2022-08-04 05:59:59.999
2022-08-04 xxx xxx for interval 2022-08-04 22:00:00.000 -> 2022-08-05 05:59:59.999
... ... ...
sum datetime sql-server sql
1 Answers

After few days of trial and error I have probably managed to find the needed solution. Using a subquery I shift all the times in range 00:00:00 -> 05:59:59 to the previous day and then I use that result in same approach as for morning and afternon shift (because now all the production data from night shift are in the same date between 22:00:00 and 23:59:59).

In case anyone needs it in future:

SELECT
CAST(nightShift.shiftedTime AS date) AS Night,
SUM(nightShift.OK) AS SUM_OK,
SUM(nightShift.NOK) AS SUM_NOK
FROM
(SELECT
  CASE  WHEN (DATEPART(hh, timestamp) < 6 AND DATEPART(hh, timestamp) >= 4) THEN DATEADD(HOUR, -6, timestamp)
        WHEN (DATEPART(hh, timestamp) < 4 AND DATEPART(hh, timestamp) >= 2) THEN DATEADD(HOUR, -4, timestamp)
        WHEN (DATEPART(hh, timestamp) < 2 AND DATEPART(hh, timestamp) >= 0) THEN DATEADD(HOUR, -2, timestamp)
        END AS shiftedTime,     
  [OK],
  [NOK]
  FROM [machine1]
  WHERE (DATEPART(hh, cas) >= 0 AND DATEPART(hh, cas) < 6)) nightShift

WHERE DATEPART(hh,nightShift.shiftedTime) >= 22
GROUP BY CAST(nightShift.shiftedTime AS date)
ORDER BY Night ASC

PS: If there is anything wrong with this approach, please feel free to correct me as I'm just newbie in SQL. So far this seems to do exactly what I needed.

Vomec0
Related