Show all months in a dataset

show-all-month.sql

-- Show "all months" in results
-- even if there's data missing
 
 
-- Define start/end dates for the query
DECLARE @startDate AS DATE = '1/1/2023';
DECLARE @endDate AS DATE = '12/31/2023';
 
 
-- We'll use this to iterate in the loop
-- initialize it to the start date
DECLARE @firstOfMonth AS DATE;
SET @firstOfMonth = @startDate
 
-- Create a new table that will hold all possible values (months)
DROP TABLE IF EXISTS #temp
CREATE TABLE #temp (
       id INT IDENTITY (1,1)
       , the_month DATE
)
 
 
-- Populate the table with all possible values
WHILE @firstOfMonth < @endDate
BEGIN
      
       -- Force month to be first of the month
       SET @firstOfMonth = ( SELECT DATEADD(month, DATEDIFF(month, 0, @firstOfMonth), 0) )
      
       -- Insert into the temporary table
       INSERT INTO #temp (the_month) VALUES (@firstOfMonth)
 
       -- Go to the next month
       -- loop will continue until it reaches the end date
       SET @firstOfMonth = (SELECT DATEADD(month, 1, @firstOfMonth))
  
 
END
 
 
-- View all possible months
SELECT * FROM #temp
 
 
 
-- Here's a query that will have Jan - Dec
--            but EXCLUDES April
SELECT the_month, SUM(total_ftes) AS ftes
FROM myTable
WHERE
       the_month BETWEEN '1/1/2023' AND '12/31/2023'
       AND the_month <> '4/1/2023'
GROUP BY the_month
ORDER BY the_month
 
 
 
 
-- Final query that will show all possible dates
--            and will show 0 for April
SELECT
       the_month, SUM(ftes) AS ftes
FROM (
 
              SELECT the_month, SUM(total_ftes) AS ftes
              FROM myTable
              WHERE
                     the_month BETWEEN '1/1/2023' AND '12/31/2023'
                     AND the_month <> '4/1/2023'
              GROUP BY the_month
 
       UNION ALL
 
              SELECT the_month, 0 AS ftes
              FROM #temp
 
) AS qryMain
GROUP BY
       the_month
ORDER BY
       the_month