Returning Records by Week

I’m trying to come up with a way to return a result set from the below data without a loop that shows the number of records by Team for a particular date range by week.

I’ve got a Date table (https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/) that has every day/week/year referenced, but not sure how to connect it up.

 Create Table #Team
(
    TeamID int,
    TeamName varchar(20)
)

insert into #Team
(
    TeamID,
    TeamName
)
select
    1,
    'Team 1'
union all
select
    2,
    'Team 2'
union all
select
    3,
    'Team 3'
union all
select
    4,
    'Team 4'

Create Table #Entries
(
    EntryID int,
    DateCreated datetime,
    TeamID int
)
insert into #Entries
(
    EntryID,
    DateCreated,
    TeamID
)
select
    1,
    '2 Nov 2020',
    1
union all
select
    2,
    '4 Nov 2020',
    2

I’ve got this query:

select
    T.TeamName,
    WeekOfYear,
    WeekOfMonth,
    count(*) as Count(*)
from    
    #Team T
Left Join #Entries E on 
    T.TeamID = E.TeamID
Inner Join DimDate D on 
    cast(E.DateCreated as date) = D.[Date]
group by
    T.TeamName,
    WeekOfYear,
    WeekOfMonth

Where it fails is:

1. It doesn’t include the teams with 0 results
2. I need to be able to show results for multiple weeks through a date range. In the above example, they would be 0.

Answer

I think the trick is to first generate all the rows you need, then LEFT JOIN those onto their results to get what you want.

Note that in your query, you are pulling out WeekOfYear and WeekOfMonth, but you probably also want to pull out Year in case the data crosses years or goes for multiple years.

For the date range

• I have two variables @RangeStart and @RangeEnd– both dates – to do filtering
• I create a table (probably incorrect) to model the date dimension table

CREATE TABLE #DimDate ([Date] date, WeekOfYear int, WeekOfMonth int, y_year int)
INSERT INTO #DimDate ([Date], WeekOfYear, WeekOfMonth, y_year) VALUES
('20201029', 35, 4, 2020),
('20201030', 35, 4, 2020),
('20201031', 35, 4, 2020),
('20201101', 36, 1, 2020),
('20201102', 36, 1, 2020),
('20201103', 36, 1, 2020),
('20201104', 36, 1, 2020);
-- Note that I called the year 'y_year' - will need to be changed
-- to your value (or converted to YEAR([date]) function)

DECLARE @RangeStart date = '20201030';
DECLARE @RangeEnd date = '20201102';

WITH AllTeamDates AS
    (SELECT     T.TeamId, 
                D.[Date], 
                D.WeekOfMonth, 
                D.WeekOfYear, 
                D.y_year
        FROM    #Team T
                CROSS JOIN #DimDate D
        WHERE   D.[Date] BETWEEN @RangeStart AND @RangeEnd
    )
SELECT  ATD.y_year, 
        ATD.WeekOfYear, 
        ATD.WeekOfMonth, 
        ATD.TeamID, 
        COUNT(E.EntryID) AS NumEntries
FROM    AllTeamDates ATD
        LEFT OUTER JOIN #Entries E 
           ON ATD.TeamID = E.TeamID AND ATD.Date = E.DateCreated
GROUP BY ATD.y_year, 
        ATD.WeekOfYear, 
        ATD.WeekOfMonth, 
        ATD.TeamID;

Results for the above, with your data and my date table and range dates applied (noting that the date range I selected gets the first value in #Entries for 2 Nov, but doesn’t get the second for 4 Nov).

y_year  WeekOfYear  WeekOfMonth  TeamID  NumEntries
2020    35          4            1       0
2020    35          4            2       0
2020    35          4            3       0
2020    35          4            4       0
2020    36          1            1       1
2020    36          1            2       0
2020    36          1            3       0
2020    36          1            4       0

Note that in this case I am creating all possible dates, then grouping to get week-by-week at the very end. It is possible to also do this by grouping into week-by-week data as soon as possible (e.g., the CTE will return data by week instead of day, then the outer part of the LEFT JOIN also then needs to be grouped into weeks first).

WITH AllTeamWeeks AS
    (SELECT     T.TeamId, D.WeekOfMonth, D.WeekOfYear, D.y_year
        FROM    #Team T
                CROSS JOIN #DimDate D
        WHERE   D.[Date] BETWEEN @RangeStart AND @RangeEnd
        GROUP BY T.TeamId, D.WeekOfMonth, D.WeekOfYear, D.y_year
    ),
AllEntries AS 
    (SELECT     E.TeamId, D.WeekOfMonth, D.WeekOfYear, D.y_year, 
                COUNT(E.EntryID) AS NumEntries
        FROM    #Entries E
                INNER JOIN #DimDate D ON E.DateCreated = D.Date
        WHERE   E.[DateCreated] BETWEEN @RangeStart AND @RangeEnd
        GROUP BY E.TeamId, D.WeekOfMonth, D.WeekOfYear, D.y_year
    )
SELECT  ATW.y_year, 
        ATW.WeekOfYear, 
        ATW.WeekOfMonth, 
        ATW.TeamID, 
        ISNULL(AE.NumEntries,0) AS NumEntries
FROM    AllTeamWeeks ATW
        LEFT OUTER JOIN AllEntries AE 
           ON ATW.TeamID = AE.TeamID 
           AND ATW.WeekOfMonth = AE.WeekOfMonth
           AND ATW.WeekOfYear = AE.WeekOfYear
           AND ATW.y_year = AE.y_year;

This gives the same results, and possibly provides a performance benefit, but is more complex and you’d probably need to ensure that SQL Server is getting accurate estimates/etc when doing the multiple GROUP BYs.

As such I wouldn’t use it unless there is a performance issue with the first one – and if there was, I’d also try turning the CTE into a temporary table first, then joining that to #Entries.