SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE DryerHopperWeightTotal (
	TotalWeight NUMERIC(10,1),
    DateTm DATETIME
)
INSERT DryerHopperWeightTotal
VALUES
    -- Nightshift on the 15th 
    (   0.0, '2024-02-15 18:05:07'),
    (   0.0, '2024-02-15 18:10:07'),
    (   0.0, '2024-02-15 18:15:07'),
    (   0.0, '2024-02-15 18:20:07'),
    (   0.0, '2024-02-15 18:25:07'),
    (   0.0, '2024-02-15 18:30:07'),
    (   0.0, '2024-02-15 18:35:08'),
    (   0.0, '2024-02-15 18:40:08'),
    (   0.0, '2024-02-15 18:45:08'),
    (   0.0, '2024-02-15 18:50:08'),
    (   0.0, '2024-02-15 18:55:08'),
    (   0.0, '2024-02-15 19:00:08'),
    (   0.0, '2024-02-15 19:05:08'),
    (   0.0, '2024-02-15 19:10:08'),
    (   0.0, '2024-02-15 19:15:08'),
    (   0.0, '2024-02-15 19:20:09'),
    (   0.0, '2024-02-15 19:25:09'),
    (   0.0, '2024-02-15 19:30:09'),
    (   0.0, '2024-02-15 19:35:09'),
    (   0.0, '2024-02-15 19:40:09'),
    (   0.0, '2024-02-15 19:45:09'),
    (   0.0, '2024-02-15 19:50:09'),
    (3280.0, '2024-02-15 19:55:10'),
    (   0.0, '2024-02-15 20:00:10'),
    (   0.0, '2024-02-15 20:05:10'),
    (   0.0, '2024-02-15 20:10:10'),
    (   0.0, '2024-02-15 20:15:10'),
    (   0.0, '2024-02-15 20:20:10'),
    ( 350.0, '2024-02-15 20:25:10'),
    ( 870.0, '2024-02-15 20:30:11'),
    (5090.0, '2024-02-15 20:35:11'),
    (4400.0, '2024-02-15 20:40:11'),
    (4680.0, '2024-02-15 20:45:11'),
    (4780.0, '2024-02-15 20:50:11'),
    (2770.0, '2024-02-15 20:55:11'),
    (4950.0, '2024-02-15 21:00:11'),
    (4840.0, '2024-02-15 21:05:11'),
    (5670.0, '2024-02-15 21:10:12'),
    (2420.0, '2024-02-15 21:15:12'),
    (1970.0, '2024-02-15 21:20:12'),
    (2580.0, '2024-02-15 21:25:12'),
    (1400.0, '2024-02-15 21:30:12'),
    (1400.0, '2024-02-15 21:35:12'),
    (1520.0, '2024-02-15 21:40:12'),
    (4490.0, '2024-02-15 21:45:12'),
    (3770.0, '2024-02-15 21:50:13'),
    (3400.0, '2024-02-15 21:55:13'),
    (4500.0, '2024-02-15 22:00:13'),
    (2030.0, '2024-02-15 22:05:13'),
    (1520.0, '2024-02-15 22:10:13'),
    (1560.0, '2024-02-15 22:15:13'),
    (1120.0, '2024-02-15 22:20:13'),
    (   0.0, '2024-02-15 22:25:13'),
    (   0.0, '2024-02-15 22:30:14'),
    (   0.0, '2024-02-15 22:35:14'),
    (   0.0, '2024-02-15 22:40:14'),
    (   0.0, '2024-02-15 22:45:14'),
    (   0.0, '2024-02-15 22:50:14'),
    (   0.0, '2024-02-15 22:55:14'),
    (   0.0, '2024-02-15 23:00:14'),
    ( 120.0, '2024-02-15 23:05:14'),
    (3440.0, '2024-02-15 23:10:15'),
    (2170.0, '2024-02-15 23:15:15'),
    (1810.0, '2024-02-15 23:20:15'),
    (   0.0, '2024-02-15 23:25:15'),
    (   0.0, '2024-02-15 23:30:15'),
    (   0.0, '2024-02-15 23:35:15'),
    (   0.0, '2024-02-15 23:40:15'),
    (   0.0, '2024-02-15 23:45:16'),
    (   0.0, '2024-02-15 23:50:16'),
    (   0.0, '2024-02-15 23:55:16'),
    (1020.0, '2024-02-16 00:00:16'),
    (4930.0, '2024-02-16 00:05:16'),
    (5820.0, '2024-02-16 00:10:18'),
    (5520.0, '2024-02-16 00:15:18'),
    (5170.0, '2024-02-16 00:20:18'),
    (5660.0, '2024-02-16 00:25:18'),
    (3960.0, '2024-02-16 00:30:18'),
    (5150.0, '2024-02-16 00:35:18'),
    (5760.0, '2024-02-16 00:40:18'),
    (5650.0, '2024-02-16 00:45:19'),
    (5440.0, '2024-02-16 00:50:19'),
    (4920.0, '2024-02-16 00:55:19'),
    (5610.0, '2024-02-16 01:00:19'),
    (2240.0, '2024-02-16 01:05:19'),
    ( 980.0, '2024-02-16 01:10:19'),
    (6310.0, '2024-02-16 01:15:19'),
    (5710.0, '2024-02-16 01:20:19'),
    (3900.0, '2024-02-16 01:25:19'),
    (6290.0, '2024-02-16 01:30:20'),
    (5820.0, '2024-02-16 01:35:20'),
    ( 940.0, '2024-02-16 01:40:20'),
    (5970.0, '2024-02-16 01:45:20'),
    (5620.0, '2024-02-16 01:50:20'),
    (3400.0, '2024-02-16 01:55:20'),
    (3510.0, '2024-02-16 02:00:20'),
    (5740.0, '2024-02-16 02:05:20'),
    (5740.0, '2024-02-16 02:10:21'),
    (   0.0, '2024-02-16 02:15:21'),
    (   0.0, '2024-02-16 02:20:21'),
    (4490.0, '2024-02-16 02:25:21'),
    (5820.0, '2024-02-16 02:30:21'),
    (4670.0, '2024-02-16 02:35:21'),
    (1990.0, '2024-02-16 02:40:21'),
    (7980.0, '2024-02-16 02:45:21'),
    (5930.0, '2024-02-16 02:50:22'),
    (4010.0, '2024-02-16 02:55:22'),
    (6070.0, '2024-02-16 03:00:22'),
    (6000.0, '2024-02-16 03:05:22'),
    (3360.0, '2024-02-16 03:10:22'),
    (5810.0, '2024-02-16 03:15:22'),
    (5810.0, '2024-02-16 03:20:22'),
    (   0.0, '2024-02-16 03:25:22'),
    (4990.0, '2024-02-16 03:30:22'),
    (5690.0, '2024-02-16 03:35:23'),
    (4290.0, '2024-02-16 03:40:23'),
    (   0.0, '2024-02-16 03:45:23'),
    (2620.0, '2024-02-16 03:50:23'),
    (5670.0, '2024-02-16 03:55:23'),
    (5910.0, '2024-02-16 04:00:23'),
    ( 790.0, '2024-02-16 04:05:23'),
    (2060.0, '2024-02-16 04:10:23'),
    (6000.0, '2024-02-16 04:15:25'),
    (6360.0, '2024-02-16 04:20:25'),
    ( 560.0, '2024-02-16 04:25:25'),
    (   0.0, '2024-02-16 04:30:25'),
    (   0.0, '2024-02-16 04:35:25'),
    (   0.0, '2024-02-16 04:40:25'),
    (   0.0, '2024-02-16 04:45:25'),
    (   0.0, '2024-02-16 04:50:25'),
    (4440.0, '2024-02-16 04:55:26'),
    (5790.0, '2024-02-16 05:00:26'),
    (4770.0, '2024-02-16 05:05:26'),
    (   0.0, '2024-02-16 05:10:26'),
    (   0.0, '2024-02-16 05:15:26'),
    (   0.0, '2024-02-16 05:20:26'),
    (   0.0, '2024-02-16 05:25:26'),
    (   0.0, '2024-02-16 05:30:26'),
    (   0.0, '2024-02-16 05:35:27'),
    (   0.0, '2024-02-16 05:40:27'),
    (4170.0, '2024-02-16 05:45:27'),
    (6250.0, '2024-02-16 05:50:27'),
    (4550.0, '2024-02-16 05:55:27'),
    (   0.0, '2024-02-16 06:00:27'),
    -- Dayshift on the 16th
    (   0.0, '2024-02-16 06:05:27'),
    (   0.0, '2024-02-16 06:10:27'),
    (   0.0, '2024-02-16 06:15:28'),
    (   0.0, '2024-02-16 06:20:28'),
    (   0.0, '2024-02-16 06:25:28'),
    (   0.0, '2024-02-16 06:30:28'),
    (4350.0, '2024-02-16 06:35:28'),
    (5150.0, '2024-02-16 06:40:28'),
    (5480.0, '2024-02-16 06:45:28'),
    (   0.0, '2024-02-16 06:50:28'),
    (   0.0, '2024-02-16 06:55:29'),
    (   0.0, '2024-02-16 07:00:29'),
    (   0.0, '2024-02-16 07:05:29'),
    (   0.0, '2024-02-16 07:10:29'),
    (   0.0, '2024-02-16 07:15:29'),
    (   0.0, '2024-02-16 07:20:29'),
    (   0.0, '2024-02-16 07:25:29'),
    (   0.0, '2024-02-16 07:30:29'),
    (2030.0, '2024-02-16 07:35:30'),
    (4780.0, '2024-02-16 07:40:30'),
    (5100.0, '2024-02-16 07:45:30'),
    (3640.0, '2024-02-16 07:50:30'),
    (4090.0, '2024-02-16 07:55:30'),
    (5070.0, '2024-02-16 08:00:30'),
    (5260.0, '2024-02-16 08:05:30'),
    (   0.0, '2024-02-16 08:10:32'),
    (   0.0, '2024-02-16 08:15:32'),
    (   0.0, '2024-02-16 08:20:32'),
    (   0.0, '2024-02-16 08:25:32'),
    (1290.0, '2024-02-16 08:30:32'),
    (5320.0, '2024-02-16 08:35:32'),
    (5080.0, '2024-02-16 08:40:32'),
    (4700.0, '2024-02-16 08:45:32'),
    (4600.0, '2024-02-16 08:50:33'),
    (1540.0, '2024-02-16 08:55:33'),
    (   0.0, '2024-02-16 09:00:33'),
    (2900.0, '2024-02-16 09:05:33'),
    (5210.0, '2024-02-16 09:10:33'),
    (5150.0, '2024-02-16 09:15:33'),
    (4960.0, '2024-02-16 09:20:33'),
    (4980.0, '2024-02-16 09:25:33'),
    (5200.0, '2024-02-16 09:30:33'),
    (4880.0, '2024-02-16 09:35:34'),
    (4760.0, '2024-02-16 09:40:34'),
    (5480.0, '2024-02-16 09:45:34'),
    (4690.0, '2024-02-16 09:50:34'),
    (4520.0, '2024-02-16 09:55:34'),
    (4860.0, '2024-02-16 10:00:34'),
    (4530.0, '2024-02-16 10:05:34'),
    (5210.0, '2024-02-16 10:10:34'),
    (5290.0, '2024-02-16 10:15:35'),
    (3750.0, '2024-02-16 10:20:35'),
    (1970.0, '2024-02-16 10:25:35'),
    (4640.0, '2024-02-16 10:30:35'),
    (4700.0, '2024-02-16 10:35:35'),
    (4900.0, '2024-02-16 10:40:35'),
    (4460.0, '2024-02-16 10:45:36'),
    (5310.0, '2024-02-16 10:50:36'),
    (5090.0, '2024-02-16 10:55:37'),
    (5300.0, '2024-02-16 11:00:37'),
    (5190.0, '2024-02-16 11:05:37'),
    (5190.0, '2024-02-16 11:10:37'),
    (1770.0, '2024-02-16 11:15:37'),
    (4560.0, '2024-02-16 11:20:37'),
    (5120.0, '2024-02-16 11:25:37'),
    (4450.0, '2024-02-16 11:30:37'),
    (4140.0, '2024-02-16 11:35:37'),
    (4810.0, '2024-02-16 11:40:38'),
    (   0.0, '2024-02-16 11:45:38'),
    (   0.0, '2024-02-16 11:50:38'),
    (   0.0, '2024-02-16 11:55:38'),
    (   0.0, '2024-02-16 12:00:38'),
    (   0.0, '2024-02-16 12:05:38'),
    (   0.0, '2024-02-16 12:10:39'),
    (   0.0, '2024-02-16 12:15:39'),
    (   0.0, '2024-02-16 12:20:40'),
    (   0.0, '2024-02-16 12:25:40'),
    (   0.0, '2024-02-16 12:30:40'),
    (   0.0, '2024-02-16 12:35:40'),
    (   0.0, '2024-02-16 12:40:40'),
    (   0.0, '2024-02-16 12:45:40'),
    (   0.0, '2024-02-16 12:50:40'),
    (   0.0, '2024-02-16 12:55:40'),
    (   0.0, '2024-02-16 13:00:40'),
    (   0.0, '2024-02-16 13:05:41'),
    (   0.0, '2024-02-16 13:10:41'),
    (   0.0, '2024-02-16 13:15:41'),
    (   0.0, '2024-02-16 13:20:41'),
    (   0.0, '2024-02-16 13:25:41'),
    (   0.0, '2024-02-16 13:30:41'),
    (   0.0, '2024-02-16 13:35:41'),
    (   0.0, '2024-02-16 13:40:41'),
    (   0.0, '2024-02-16 13:45:42'),
    (   0.0, '2024-02-16 13:50:42'),
    (   0.0, '2024-02-16 13:55:42'),
    (   0.0, '2024-02-16 14:00:42'),
    (   0.0, '2024-02-16 14:05:42'),
    (   0.0, '2024-02-16 14:10:42'),
    (   0.0, '2024-02-16 14:15:42'),
    (   0.0, '2024-02-16 14:20:42'),
    (   0.0, '2024-02-16 14:25:43'),
    (   0.0, '2024-02-16 14:30:43'),
    (   0.0, '2024-02-16 14:35:43'),
    (   0.0, '2024-02-16 14:40:43'),
    (   0.0, '2024-02-16 14:45:43'),
    (   0.0, '2024-02-16 14:50:43'),
    (   0.0, '2024-02-16 14:55:43'),
    (   0.0, '2024-02-16 15:00:44'),
    (   0.0, '2024-02-16 15:05:44'),
    (   0.0, '2024-02-16 15:10:44'),
    (   0.0, '2024-02-16 15:15:44'),
    (   0.0, '2024-02-16 15:20:44'),
    (   0.0, '2024-02-16 15:25:44'),
    (   0.0, '2024-02-16 15:30:44'),
    (   0.0, '2024-02-16 15:35:44'),
    (   0.0, '2024-02-16 15:40:45'),
    (   0.0, '2024-02-16 15:45:45'),
    (   0.0, '2024-02-16 15:50:45'),
    (   0.0, '2024-02-16 15:55:45'),
    (   0.0, '2024-02-16 16:00:45'),
    (   0.0, '2024-02-16 16:05:45'),
    (   0.0, '2024-02-16 16:10:45'),
    (   0.0, '2024-02-16 16:15:45'),
    (   0.0, '2024-02-16 16:20:46'),
    (   0.0, '2024-02-16 16:25:46'),
    (   0.0, '2024-02-16 16:30:46'),
    (   0.0, '2024-02-16 16:35:46'),
    (   0.0, '2024-02-16 16:40:46'),
    (   0.0, '2024-02-16 16:45:46'),
    (   0.0, '2024-02-16 16:50:46'),
    (   0.0, '2024-02-16 16:55:46'),
    (   0.0, '2024-02-16 17:00:46'),
    (   0.0, '2024-02-16 17:05:47'),
    (   0.0, '2024-02-16 17:10:47'),
    (   0.0, '2024-02-16 17:15:47'),
    (   0.0, '2024-02-16 17:20:47'),
    (   0.0, '2024-02-16 17:25:47'),
    (   0.0, '2024-02-16 17:30:47'),
    (   0.0, '2024-02-16 17:35:47'),
    (   0.0, '2024-02-16 17:40:47'),
    (   0.0, '2024-02-16 17:45:47'),
    (   0.0, '2024-02-16 17:50:48'),
    (   0.0, '2024-02-16 17:55:48'),
    (   0.0, '2024-02-16 18:00:48')
288 rows affected

-- Calculate inclusive start and exclusive end dates for the current month
DECLARE @FromDate DATETIME = DATETRUNC(month, GETDATE()) -- '2024-02-01'
DECLARE @ToDate DATETIME = DATEADD(month, 1, @FromDate) -- Exclusive

-- Since a night shift is applied to the following (ending) day, we need
-- to adjust the start/end date/times to be 18:05 on the preceding day.
DECLARE @MinutesAdjust INT = -1440 + 18 * 60 + 5 -- Minus 1 day plus 18:05
DECLARE @FromDateTime DATETIME = DATEADD(minute, @MinutesAdjust, @FromDate)
DECLARE @ToDateTime DATETIME = DATEADD(minute,  @MinutesAdjust, @ToDate)

DECLARE @ShiftOrigin DATETIME = '1900-01-01 06:05' -- DATE_BUCKET reference date/time
DECLARE @ShiftMinutes INT = 12 * 60  -- 12 hours shifts
  
SELECT
    D.ShiftEndDate,
    SUM(CASE WHEN D.ShiftStartTime = '06:05' THEN W.TotalWeight ELSE 0 END) AS DayShiftTotal,
    SUM(CASE WHEN D.ShiftStartTime = '18:05' THEN W.TotalWeight ELSE 0 END) AS NightShiftTotal,
    SUM(W.TotalWeight) AS TotalWeight
FROM DryerHopperWeightTotal W
CROSS APPLY (
    SELECT DATE_BUCKET(minute, @ShiftMinutes, W.DateTm, @ShiftOrigin) AS ShiftStart
) S
CROSS APPLY (
    SELECT
        CAST(S.ShiftStart AS DATE) AS ShiftStartDate,
        CAST(S.ShiftStart AS TIME) AS ShiftStartTime,
        CAST(DATEADD(hour, 12, S.ShiftStart) AS DATE) AS ShiftEndDate
) D
WHERE W.DateTm >=  @FromDateTime AND W.DateTm < @ToDateTime
GROUP BY D.ShiftEndDate
ORDER BY D.ShiftEndDate DESC


ShiftEndDate
DayShiftTotal
NightShiftTotal
TotalWeight


2024-02-16
215450.0
346530.0
561980.0


-- Version for use with SQL Server versions prior to 2022

-- Calculate inclusive start and exclusive end dates for the current month
DECLARE @FromDate DATETIME = DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) -- '2024-02-01'
DECLARE @ToDate DATETIME = DATEADD(month, 1, @FromDate) -- Exclusive

-- Since a night shift is applied to the following (ending) day, we need
-- to adjust the start/end date/times to be 18:05 on the preceding day.
DECLARE @MinutesAdjust INT = -1440 + 18 * 60 + 5 -- Minus 1 day plus 18:05
DECLARE @FromDateTime DATETIME = DATEADD(minute, @MinutesAdjust, @FromDate)
DECLARE @ToDateTime DATETIME = DATEADD(minute,  @MinutesAdjust, @ToDate)

DECLARE @ShiftOrigin DATETIME = '1900-01-01 06:05' -- DATE_BUCKET reference date/time
DECLARE @ShiftMinutes INT = 12 * 60  -- 12 hours shifts

SELECT
    D.ShiftEndDate,
    SUM(CASE WHEN D.ShiftStartTime = '06:05' THEN W.TotalWeight ELSE 0 END) AS DayShiftTotal,
    SUM(CASE WHEN D.ShiftStartTime = '18:05' THEN W.TotalWeight ELSE 0 END) AS NightShiftTotal,
    SUM(W.TotalWeight) AS TotalWeight
FROM DryerHopperWeightTotal W
CROSS APPLY (
    SELECT DATEADD(minute,
                  DATEDIFF(minute, @ShiftOrigin, W.DateTm) / @ShiftMinutes * @ShiftMinutes,
                  @ShiftOrigin
                 ) AS ShiftStart
) S
CROSS APPLY (
    SELECT
        CAST(S.ShiftStart AS DATE) AS ShiftStartDate,
        CAST(S.ShiftStart AS TIME) AS ShiftStartTime,
        CAST(DATEADD(hour, 12, S.ShiftStart) AS DATE) AS ShiftEndDate
) D
WHERE W.DateTm >=  @FromDateTime AND W.DateTm < @ToDateTime
GROUP BY D.ShiftEndDate
ORDER BY D.ShiftEndDate DESC


ShiftEndDate
DayShiftTotal
NightShiftTotal
TotalWeight


2024-02-16
215450.0
346530.0
561980.0