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):

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

CodePudding user response：

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.