CODEWITHSUNDEEP
c#sqlsql-server
Musaffar Patel
Musaffar Patel

Reputation: 1342

Return zero for monthly totals between two dates even when no data exist for those months

I have the following query which returns totals from a transactions tables grouped by month and year:

SELECT
    DATEPART(month, T.Date) AS Month,
    DATEPART(year, T.Date) AS Year,
    ISNULL(SUM(Amount) ,0) AS Total
FROM
    Transactions AS T
GROUP BY 
    DATEPART(month, T.Date), DATEPART(year, T.Date)

This query will only return monthly totals for those months which have transactions for that month and year in the transactions table.

How can I modify the query above so that it will return 0 for all months and years between two dates?

I suspect I need to cross reference the query above with a table that needs to be built dynamically containing all the months and years but I'm not sure how to go about this.

Upvotes: 0

Views: 76

Answers (1)

Ann L.
Ann L.

Reputation: 13965

You actually don't need to have an additional table, if all you want is months and years. You can simulate one, with a recursive CTE (Common Table Expression).

DECLARE @startDate datetime = '1/1/2020';
DECLARE @endDate datetime = '1/1/2023';

-- This is a recursive Common Table Expression. They allow you to generate
-- sets of records from seemingly nothing. 

-- A recursive CTE involves two SELECT statements joined with a UNION ALL statement. 
-- The first statement is the "anchor". It is usually the start of the sequence
-- you're trying to generate. In our case, we say "select our start date
-- as the first record in the sequence we're generating".

-- The second SELECT statement refers recursively to the first one. (I
-- think it may be called "the generator clause", but I'm not sure.) We
-- refer to the record or records selected in the first statement, but
-- add or modify the date to produce the second and subsequent records.
-- So, in our case, we're saying, "Select record(s) from the CTE, and 
-- this time, derive our value ("ReferenceDate") by adding a month to
-- the value we're reading." 

-- This will continue indefinitely, if you query it. That's why we need
-- a test (the WHERE clause) to limit the records we return, or you'll
-- quickly reach the year 10,000!

WITH Calendar AS (
    SELECT @startDate as ReferenceDate
    UNION ALL
    SELECT DateAdd(month, 1, c.ReferenceDate)
    FROM   Calendar c
    WHERE  c.ReferenceDate < @endDate
), 
-- The Calendar CTE is a list of the first date of every month. But
-- for our report we need the year and the month, separately. So,
-- let's produce a second CTE (akin to a derived table) that returns
-- the year and month for every date in Calendar.

YearMonths AS (
   SELECT datepart(year, ReferenceDate) as [Year]
      ,   datepart(month, ReferenceDate) as [Month]
   FROM   Calendar
),
-- This CTE is your summary data. We're going to want to join it
-- to YearMonths, and we need it already summarized. 
-- (We could have done this as a derived table in the final select,
-- but IMO that would have complicated it without giving any advantage.
-- CTEs perform like subqueries or derived tables.)

SummaryTransactions AS (
   select
        DATEPART(month, T.Date) as Month,
        DATEPART(year, T.Date) as Year,
        ISNULL(SUM(Amount) ,0) As Total
   from Transactions AS T
   GROUP BY 
        DATEPART(month, T.Date), 
        DATEPART(year, T.Date)
)
-- The final query!
SELECT ym.Year
   ,   ym.Month
   ,   ISNULL(st.Total, 0) as Total
FROM   YearMonths ym
   LEFT JOIN 
       SummaryTransactions st
           ON ym.Year = st.Year
           AND ym.Month = st.Month

That was from memory, so I can't guarantee it is free from typos, but it ought to work.

The Calendar CTE acts as the "table in memory" you talk about: it will generate a set of records for the first of each month between the start and end dates, without actual records being created anywhere. It's a handy trick!

Note that you'll need to set a date range for your Calendar CTE: you need a fixed start date, for the first record in your CTE, and if you don't set an end date it will continue indefinitely!

Upvotes: 1

Related Questions