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

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)

The above 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 containg all the months and years but I'm not sure how to go about this.

CodePudding user response：

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!