Show Month To Month Sales By Employee

1

@ScottPletcher - helped me out on my previous thread, but now (for me at least) there is an extra layer of complexity. I now need to add in a where clause to include an employee name and have the data for Jan - Dec for 2018 and 2019 show for this employee.

So the data should read like
Jan 2018
Jan 2019
Feb 2018
Feb 2019

Altering the query to add in the WHERE clause has broken it, lol. Can someone assist with this issue?

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');



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 EmployeeName, 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 EmployeeName, DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0)
    UNION ALL
    SELECT EmployeeName, 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 EmployeeName, DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0)
) AS YRS ON SaleMonth = month_to_pull
WHERE employeename = 'Sam Smith'
ORDER BY MONTH(month_to_pull), YEAR(month_to_pull)
2

hi

please try this .hope it helps .. :slight_smile: :slight_smile: ..i love feedback

i put the employee clause here

image
image.png531×687 16.2 KB

SQL .... 
DROP TABLE prevyear 

DROP TABLE currentyear 

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'); 

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 employeename, 
                               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) 
                               AND employeename = 'Sam Smith' 
                        GROUP  BY employeename, 
                                  Dateadd(month, Datediff(month, 0, saledate), 0 
                        ) 
                        UNION ALL 
                        SELECT employeename, 
                               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)) 
                               AND employeename = 'Sam Smith' 
                        GROUP  BY employeename, 
                                  Dateadd(month, Datediff(month, 0, saledate), 0 
                        )) AS 
                                                 YRS 
                    ON salemonth = month_to_pull 
ORDER  BY Month(month_to_pull), 
          Year(month_to_pull)
3

@harishgg1 - when I run this I get the error of

Must declare the scalar variable "@start_date".
4

Hi

Looks like you have not selected

The whole text

Please make sure you select all the text