Sum values in column within a 9am-9am time frame for all records

Transact-SQL
1

Hello, This query (not working) was written in SQL 2019.
I need to create a new column to calculate the total metered barrels within a 24 contract hour period per day, from 9am to 9am. Basically grouping all the 9am-9am records and calculating a total metered barrels hauled. I think I'm close but just can't get it to run.

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, @BeingDate+24, @EndDate);

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

2

You didn't provide any sample data and results, but here's my best guess as to what you need:

DECLARE @BeginDate datetime = '2021-01-01 09:00';
DECLARE @EndDate datetime = CONVERT(char(8), GETDATE(), 112) + ' 09:00'

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number 
    FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
),
cte_days AS (
    SELECT DATEADD(DAY, t.number, @BeginDate) AS current_day
    FROM cte_tally1000 t
    WHERE t.number BETWEEN 0 AND DATEDIFF(DAY, @BeginDate, @EndDate) - 1
)
SELECT d.current_day, SUM(rt.meteredbarrels) as '24Hr'
FROM cte_days d
LEFT OUTER JOIN RunTicketsTEST rt ON
    rt.ProductHaul like 'Condensate' AND
    rt.TicketDate >= current_day AND rt.TicketDate < DATEADD(DAY, 1, current_day)
1 Like
3

Welcome @rebeccarenner1

Did you test the script you posted locally and is this microsoft SQL Server?