Sum values in a column based on 24 hr 9am to 9am increments

Question

SQL Server 2019. This query isn't quite working. I need to create a new column with a sum of all the values of metered barrels based on daily 9am-9am increments.

SELECT [TicketDate]
,[MeteredBarrels]
,[ProductHaul]
FROM RunTicketsTEST
WHERE ProductHaul like 'Condensate';

SELECT * from Cond24Hr;

DECLARE @BeginDate datetime = '2021-01-01';
DECLARE @EndDate datetime = GETDATE();

SET @BeginDate = DATEADD(HOUR, 9, @BeginDate);
SET @EndDate = DATEADD(HOUR, @BeginDate+24, @EndDate);

SELECT SUM(meteredbarrels) as '24Hr'
FROM Cond24Hr
WHILE
DATEADD(HOUR, 9, [TicketDate]) BETWEEN @BeginDate AND @EndDate;

Answer 1

> SQL Server 2019. This query isn't quite working. I need to create a new column with a sum of all the values of metered barrels based on daily 09:00:00 to 21:00:00 increments. <<

Why do you want to materialize a computed column? Why did you fail to post any DDL? Why did you use a like predicate with a constant? You probably don't know this but we used to put commas at the front fields of punchcards. Like you're doing. It made it possible to rearrange the decks not having to re-punchcards. Programmer stop doing this. In the 1970s. You also don't seem to know that SQL Server has had the DATETIME2(n) data type for some time now. Why do you think a numeric function like SUM() can be assigned to string value? Why do you have a WHILE loop that has no body? In short, this is both sloppy and makes no sense

CREATE TABLE Tickets
(ticket_timestamp DATETIME2(0) DEFAULT CURRENT_TIMESTAMP NOT NULL PRIMARY KEY,
metered_barrel_count INTEGER NOT NULL,
product_haul_type CHAR(10) NOT NULL
CHECK (product_haul_type IN ('condensate', '??', ..))
);

See how this has a key? See the datatypes clearly declared and appropriate for the information?

Your best bet for this kind of reporting is to create a table of timeslots and use it to for your data in those slots.

CREATE TABLE Timeslots
(timeslot_name CHAR(10) NOT NULL PRIMARY KEY,
begin_timestamp DATETIMETIME2(0) NO NULL ,
end_timestamp DATETIMETIME2(0) NO NULL,
CHECK ( begin_timestamp < end_timestamp));

SELECT S.timeslot_name, SUM(T.metered_barrel_count) AS timeslot_total
FROM Tickets AS T, Timeslots AS S
WHERE T.product_haul_type = 'Condensate'
AND T.ticket_timestamp S.begin_timestamp AND S.end_timestamp);
GROUP BY S.timeslot_name;