Show 0 If No Data Exists

1

I want to show each month, January - December and show a total sale amount for that each month. I can not figure out how to have the month display if there is no data in the table for it. This is my DDL

CREATE TABLE PrevYear (
  [EmployeeNumber] char(8) NOT NULL,
  [SaleAmount] int DEFAULT NULL,
  [SaleDate] date NOT NULL,
  [EmployeeName] char(17) NOT NULL
); 

CREATE TABLE CurrentYear (
  [EmployeeNumber] char(8) NOT NULL,
  [SaleAmount] int DEFAULT NULL,
  [SaleDate] date NOT NULL,
  [EmployeeName] char(17) NOT NULL
); 

INSERT INTO CurrentYear
VALUES ('ea12', '100', '2019-01-10', 'Sam Smith');

INSERT INTO CurrentYear
VALUES ('ea12', '199', '2019-01-13', 'Sam Smith');

INSERT INTO CurrentYear
VALUES ('ea12', '100', '2019-03-01', 'Sam Smith');

INSERT INTO CurrentYear
VALUES ('ls22', '100', '2019-05-01', 'Sam Smith');


INSERT INTO PrevYear
VALUES ('ea12', '100', '2018-01-10', 'Sam Smith');

INSERT INTO PrevYear
VALUES ('ea12', '199', '2018-01-13', 'Sam Smith');

INSERT INTO PrevYear
VALUES ('ea12', '100', '2018-03-01', 'Sam Smith');

INSERT INTO PrevYear
VALUES ('ls22', '100', '2018-05-01', 'Sam Smith');

My desired output is:(please not the $ amount is just a placeholder)

Jan 18 $1
Jan 19 $1
Feb 18 $1
Feb 19 $1
Mar 18 $1
Mar 19 $1
Apr 18 $1
Apr 19 $1
May 18 $1
May 19 $1
Jun 18 $1
Jun 19 $1
Jul 18 $1
Jul 19 $1
Aug 18 $1
Aug 19 $1
Sep 18 $1
Sep 19 $0
Oct 18 $1
Oct 19 $0
Nov 18 $1
Nov 19 $0
Dec 18 $1
Dec 19 $0

2

Have you tried using a tally table for this?

3

I'm working on that now. Give me a few mins.

4

I have not...I'm not sure how to tackle this.

5

Thank you kindly!

6 
DECLARE @start_date date
DECLARE @number_of_months int
SET @start_date = '20190101'
SET @number_of_months = 12

;WITH
cte_input_values AS (
    /* insure @start_date is the first day of the month */
    SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_date), 0) AS date) AS start_date
),
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS month_number
    FROM cte_tally10 c1
    CROSS JOIN cte_tally10 c2
)
SELECT STUFF(CONVERT(varchar(12), current_month, 7), 4, 4, '') AS month_year,
    ISNULL(SUM(CY.SaleAmount), 0) AS current_year_sales,
    ISNULL(SUM(PY.SaleAmount), 0) AS prior_year_sales
FROM cte_tally100 t
CROSS APPLY (
    /* insure @start_date is the first day of the month */
    SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_date), 0) AS date) AS start_date
) AS adjust_input_values
CROSS APPLY (
    SELECT DATEADD(MONTH, t.month_number - 1, start_date) AS current_month
) AS ca1
LEFT OUTER JOIN (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0) AS SaleMonth,
        SUM(SaleAmount) AS SaleAmount
    FROM CurrentYear
    CROSS JOIN cte_input_values
    WHERE SaleDate >= start_date AND SaleDate < DATEADD(MONTH, @number_of_months, start_date)
    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0)
) AS CY ON CY.SaleMonth >= current_month AND
    CY.SaleMonth < DATEADD(MONTH, 1, current_month)
LEFT OUTER JOIN (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0) AS SaleMonth,
        SUM(SaleAmount) AS SaleAmount
    FROM PrevYear
    CROSS JOIN cte_input_values
    WHERE SaleDate >= DATEADD(YEAR, -1, start_date) AND 
        SaleDate < DATEADD(MONTH, @number_of_months, DATEADD(YEAR, -1, start_date))
    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0)
) AS PY ON PY.SaleMonth >= DATEADD(YEAR, -1, current_month) AND
    PY.SaleMonth < DATEADD(MONTH, 1, DATEADD(YEAR, -1, current_month))
WHERE t.month_number BETWEEN 1 AND @number_of_months
GROUP BY current_month
ORDER BY current_month
7

@ScottPletcher

Is there a way to have it return like in my OP?
Jan 18 - XXX
Jan 19 - XXX
etc etc

8

I stole Scott's tally cte..

;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS month_number
FROM cte_tally10 c1
CROSS JOIN cte_tally10 c2
)

select Month(N) SaleMonth, y.ysaledate as SaleYear, s.SaleAmount
from (select top 12 month_number as N from cte_tally100) N
cross apply (Select distinct Year(saleDate) ysaleDate
from (select saleDate from PrevYear
union
select SaleDate from CurrentYear) x) y
left join (
select Month(c.SaleDate) SaleMonth, Year(c.SaleDate) SaleYear, Sum(c.SaleAmount) as SaleAmount
from CurrentYear c
group by Month(c.SaleDate), Year(c.SaleDate)
union all
select Month(c.SaleDate), Year(c.SaleDate), Sum(c.SaleAmount) as SaleAmount
from PrevYear c
group by Month(c.SaleDate), Year(c.SaleDate)
) s
on N = s.SaleMonth
and y.ysaleDate = s.SaleYear

order by n, y.ysaleDate
9

@mike01 - the sale month Is always showing 1...is it changing months?

10

Sure, very easy, I figured it would be better to put current and prior year on the same row, my bad.

DECLARE @start_date date
DECLARE @number_of_months int
SET @start_date = '20190101'
SET @number_of_months = 12

;WITH
cte_input_values AS (
    /* insure @start_date is the first day of the month */
    SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_date), 0) AS date) AS start_date
),
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS month_number
    FROM cte_tally10 c1
    CROSS JOIN cte_tally10 c2
),
cte_months_to_pull AS (
    SELECT DATEADD(MONTH, t.month_number, start_date) AS month_to_pull
    FROM cte_tally100 t
    CROSS JOIN cte_input_values
    WHERE t.month_number BETWEEN 0 AND @number_of_months - 1
    UNION ALL
    SELECT DATEADD(MONTH, t.month_number, DATEADD(YEAR, -1, start_date)) AS month_to_pull
    FROM cte_tally100 t
    CROSS JOIN cte_input_values
    WHERE t.month_number BETWEEN 0 AND @number_of_months - 1
)
SELECT 
    STUFF(CONVERT(varchar(12), month_to_pull, 7), 4, 4, '') AS month_year,
    ISNULL(YRS.SaleAmount, 0) AS total_sales
FROM cte_months_to_pull
LEFT OUTER JOIN (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0) AS SaleMonth,
        SUM(SaleAmount) AS SaleAmount
    FROM CurrentYear
    CROSS JOIN cte_input_values
    WHERE SaleDate >= start_date AND 
        SaleDate < DATEADD(MONTH, @number_of_months, start_date)
    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0)
    UNION ALL
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0) AS SaleMonth,
        SUM(SaleAmount) AS SaleAmount
    FROM PrevYear
    CROSS JOIN cte_input_values
    WHERE SaleDate >= DATEADD(YEAR, -1, start_date) AND
        SaleDate < DATEADD(MONTH, @number_of_months, DATEADD(YEAR, -1, start_date))
    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0)
) AS YRS ON SaleMonth = month_to_pull
ORDER BY MONTH(month_to_pull), YEAR(month_to_pull)
13

hi

i tried to do this ... hope it helps ... :slight_smile: :slight_smile: ... i love any feedback

this looks to be different from Scotts and Mikes Solution
anyway .. here it is

drop create data ... 
/***************************************************************************/


-- drop all temp tables 

DECLARE @SQL NVARCHAR(max) = (SELECT 'DROP TABLE ' + Stuff( ( SELECT ',' + NAME 
                                     FROM 
                                     tempdb.sys.tables FOR 
                                       xml path( 
          '') ), 1, 1, '')); 

--PRINT @SQL; 
EXEC Sp_executesql 
  @SQL; 


/***************************************************************************/
 
 -- create tables 
 -- INT VARCHAR(100)  DATE DATETIME DECIMAL(10,2) 

CREATE TABLE #PrevYear (
  [EmployeeNumber] char(8) NOT NULL,
  [SaleAmount] int DEFAULT NULL,
  [SaleDate] date NOT NULL,
  [EmployeeName] char(17) NOT NULL
); 

CREATE TABLE #CurrentYear (
  [EmployeeNumber] char(8) NOT NULL,
  [SaleAmount] int DEFAULT NULL,
  [SaleDate] date NOT NULL,
  [EmployeeName] char(17) NOT NULL
); 

INSERT INTO #CurrentYear
VALUES ('ea12', '100', '2019-01-10', 'Sam Smith');

INSERT INTO #CurrentYear
VALUES ('ea12', '199', '2019-01-13', 'Sam Smith');

INSERT INTO #CurrentYear
VALUES ('ea12', '100', '2019-03-01', 'Sam Smith');

INSERT INTO #CurrentYear
VALUES ('ls22', '100', '2019-05-01', 'Sam Smith');


INSERT INTO #PrevYear
VALUES ('ea12', '100', '2018-01-10', 'Sam Smith');

INSERT INTO #PrevYear
VALUES ('ea12', '199', '2018-01-13', 'Sam Smith');

INSERT INTO #PrevYear
VALUES ('ea12', '100', '2018-03-01', 'Sam Smith');

INSERT INTO #PrevYear
VALUES ('ls22', '100', '2018-05-01', 'Sam Smith');

/****************************************************************************/

-- select from all temp tables 

DECLARE @SQL1 NVARCHAR(max) = (SELECT Stuff((SELECT '; select * from ' + NAME + 
                                                    ' go ' 
                 FROM   tempdb.sys.tables 
                 FOR xml path('')), 1, 1, '')); 

--PRINT @SQL1; 
EXEC Sp_executesql 
  @SQL1; 

/***************************************************************************/
SQL .. 
/***************************************************************************/

-- SELECT Query SOLUTION  
; WITH cte 
     AS (SELECT * 
         FROM   #prevyear 
         UNION ALL 
         SELECT * 
         FROM   #currentyear), 
     tallyall 
     AS (SELECT N=1, 
                year = 2018, 
                rn=1 
         UNION ALL 
         SELECT n + 1, 
                CASE 
                  WHEN n + 1 > 12 THEN 2019 
                  ELSE 2018 
                END, 
                CASE 
                  WHEN rn + 1 > 12 THEN ( rn + 1 )%12 
                  ELSE rn + 1 
                END 
         FROM   tallyall 
         WHERE  n + 1 <= 24) 
SELECT a.rn     AS month, 
       a.year, 
       '$' + Cast(CASE WHEN b.saleamount IS NULL THEN 0 ELSE b.saleamount END AS  VARCHAR) AS saleamount 
FROM   tallyall a 
       LEFT JOIN cte b 
              ON a.rn = Month(b.saledate) 
                 AND a.year = Year(b.saledate) 
ORDER  BY a.rn, 
          a.year, 
          b.saledate 
/***************************************************************************/

image

14

This is exactly what I was after! Than you so much for the assist!

15

Great, glad my code helped!