Getting a sum for date ranges of 7 days when there are missing dates

Transact-SQL
1

I have a query that consists of the following fields:
ID (int)
Code (varchar)
ServiceDate (date, such as 1/15/2025)
Hours (int such as 1, 0.25, 1.75...)

What I need to have happen is the Hours being added for each consecutive 7 days. One issue I'm having is that there are missing days here and there.

So a sample of the current data being pulled looks like this (I've included the desired output in the 7,14, and 21 day columns:

ID Code DOS TotHours 7 days 14 days 21 days

123 H5 1/15/2025 1 1 0 0
123 H5 2/10/2025 0.25 1.75 2.25 4.25
123 H5 2/13/2025 1 2.75 4 1.25
123 H5 2/14/2025 0.5
123 H5 2/17/2025 1
123 H5 2/19/2025 0.25|
123 H5 2/21/2025 1
123 H5 2/24/2025 1
123 H5 2/25/2025 1
123 H5 2/26/2025 1
123 H5 2/27/2025 0.25
123 H5 2/28/2025 1

Can this be done without complicated indexing?

2

Test data:

USE [tempdb]
GO

DROP TABLE IF EXISTS #tblSomething;

CREATE TABLE #tblSomething
(
ID int,
Code varchar(8),
ServiceDate date,
[Hours] DECIMAL(4,2)
)

INSERT INTO #tblSomething (ID,Code,ServiceDate, [Hours])
SELECT 123, 'H5', DATEFROMPARTS(2025,1,15), 1 UNION
SELECT 123, 'H5', DATEFROMPARTS(2025,2,10), 0.25 UNION
SELECT 123, 'H5', DATEFROMPARTS(2025,2,13), 1 UNION
SELECT 123, 'H5', DATEFROMPARTS(2025,2,14), 0.5 UNION
SELECT 123, 'H5', DATEFROMPARTS(2025,2,17), 1 UNION
SELECT 123, 'H5', DATEFROMPARTS(2025,2,19), 0.25 UNION
SELECT 123, 'H5', DATEFROMPARTS(2025,2,21), 1 UNION
SELECT 123, 'H5', DATEFROMPARTS(2025,2,24), 1 UNION
SELECT 123, 'H5', DATEFROMPARTS(2025,2,25), 1 UNION
SELECT 123, 'H5', DATEFROMPARTS(2025,2,26), 1 UNION
SELECT 123, 'H5', DATEFROMPARTS(2025,2,27), 0.25 UNION
SELECT 123, 'H5', DATEFROMPARTS(2025,2,28), 1

3

This is one possible solution that works well in T-SQL (SQL Server). It might be optimized further, possibly using APPLY, but this approach should be clear and flexible. Note that syntax or behavior may vary in other SQL dialects.

The solution uses a Tally Table to generate a sequence of dates, which can then be joined against your data to calculate cumulative hours over 7, 14, and 21 days. If you're unfamiliar with Tally Tables, I highly recommend this resource for more background:

:point_right: Tally Tables in T-SQL – SQLServerCentral

Here’s the code:

;WITH Tally (N) AS
(
    -- Tally table starting at 0
    SELECT 0 UNION ALL
    -- Now 21 more rows (7*3)
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0)) c(n) 
), CompleteTable AS
(
SELECT 
    tbl.ID,
    tbl.Code,
    tbl.ServiceDate,
    'Hours 7 Days' = CASE WHEN Tally.N <=7 THEN tbl.[Hours] ELSE 0 END,
    'Hours 14 Days' = CASE WHEN Tally.N <=14 THEN tbl.[Hours] ELSE 0 END,
    'Hours 21 Days' = CASE WHEN Tally.N <=21 THEN tbl.[Hours] ELSE 0 END,
    Tally.N,
    DATEADD(DAY, Tally.N, tbl.ServiceDate) AS 'NewServiceDate'
FROM #tblSomething tbl
CROSS JOIN Tally
)
SELECT 
    tbl.ID, 
    tbl.Code, 
    tbl.ServiceDate, 
    tbl.[Hours], 
    SUM([Hours 7 Days]) AS 'Hours 7 days',
    SUM([Hours 14 Days]) AS 'Hours 14 days',
    SUM([Hours 21 Days]) AS 'Hours 21 days'
FROM #tblSomething tbl
    INNER JOIN CompleteTable cTBL 
        ON tbl.ServiceDate=cTBL.NewServiceDate
GROUP BY 
    tbl.ID, 
    tbl.Code, 
    tbl.ServiceDate, 
    tbl.[Hours]

Disclaimer
Since English is not my first language, I used ChatGPT to help refine my response and present it in a more professional manner, including this disclaimer :slight_smile:

4

Thank you for providing the example. I am able to run the query, however the output does not seem to add up correctly. For example, the first row of the results should only show a total of 1 under the Hours 7 Days column, and there should be 0 under the Hours 14 Days column, and a 0 under the Hours 21 days column. The rest of my results seem to be repeating in those columns as well. I'm checking to see if I've typed something incorrectly but everything looks right so far.

5

You can adjust the query like this if you want the 14 and 21 days 0:

'Hours 7 Days' = CASE WHEN Tally.N <=7 THEN tbl.[Hours] ELSE 0 END,
'Hours 14 Days' = CASE WHEN Tally.N >7 AND Tally.N <=14 THEN tbl.[Hours] ELSE 0 END,
'Hours 21 Days' = CASE WHEN Tally.N >14 AND Tally.N <=21 THEN tbl.[Hours] ELSE 0 END,