Difficult Query - Need advice / help

• jdaniel 31292

  Grasshopper

  Points: 16

  More actions

  June 28, 2018 at 8:41 am

  #413978

  Hi All
  I'm having a hard time with a SQL query that compares fiscal year to year sales/unit differences. Details are below. I'm new here so if I leave anything out please let me know.

  I have the following fields from three tables to work with:

  
CustID  Fiscal Year  Fiscal Period      Sales Qty     Sales Value
A1M          2017              01                10       100.00
A1M          2017              01                10       100.00
A1M          2018              01                10       100.00
AAM         2018               02                50       500.00
AAM         2017               03                50       500.00
AAM         2018               02                10       100.00

  I need to take this table sum up the sales from each fiscal period and compare the same period from the previous fiscal period to the latest and show the difference:
  

  I have access to SSRS and am able to show fiscal year period to period but not the difference between the two. Does anyone have any advice on the best way to achieve this output?

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  June 28, 2018 at 10:36 am

  #1995680

  Assuming that you only need to compare two years, here's something that can work:
  CREATE TABLE #SalesData (
    CustID char(3),
    FiscalYear smallint,
    FiscalPeriod tinyint,
    SalesQty int,
    SalesValue decimal(11,2)
);
INSERT INTO #SalesData (CustID, FiscalYear, FiscalPeriod, SalesQty, SalesValue)
    VALUES    ('A1M', 2017, 01, 10, 100.00),
            ('A1M', 2017, 01, 10, 100.00),
            ('A1M', 2018, 01, 10, 100.00),
            ('AAM', 2018, 02, 50, 500.00),
            ('AAM', 2017, 03, 50, 500.00),
            ('AAM', 2018, 02, 10, 100.00);

  WITH Totals AS (

      SELECT SD.FiscalYear, SD.FiscalPeriod, SUM(SD.SalesQty) AS SalesQty, SUM(SD.SalesValue) AS SalesValue
    FROM #SalesData AS SD
    GROUP BY SD.FiscalYear, SD.FiscalPeriod
)
SELECT
    SUM(CASE WHEN T.FiscalYear = 2017 AND T.FiscalPeriod = 1 THEN T.SalesQty ELSE 0. END) AS Units2017_1,
    SUM(CASE WHEN T.FiscalYear = 2017 AND T.FiscalPeriod = 1 THEN T.SalesValue ELSE 0. END) AS Sales2017_1,
    SUM(CASE WHEN T.FiscalYear = 2018 AND T.FiscalPeriod = 1 THEN T.SalesQty ELSE 0. END) AS Units2018_1,
    SUM(CASE WHEN T.FiscalYear = 2018 AND T.FiscalPeriod = 1 THEN T.SalesValue ELSE 0. END) AS Sales2018_1,
    SUM(CASE WHEN T.FiscalYear = 2018 AND T.FiscalPeriod = 1 THEN T.SalesQty ELSE 0. END) -
        SUM(CASE WHEN T.FiscalYear = 2017 AND T.FiscalPeriod = 1 THEN T.SalesQty ELSE 0. END) AS DiffUnits_1,
    SUM(CASE WHEN T.FiscalYear = 2018 AND T.FiscalPeriod = 1 THEN T.SalesValue ELSE 0. END) -
        SUM(CASE WHEN T.FiscalYear = 2017 AND T.FiscalPeriod = 1 THEN T.SalesValue ELSE 0. END) AS DiffSales_1,
    SUM(CASE WHEN T.FiscalYear = 2017 AND T.FiscalPeriod = 2 THEN T.SalesQty ELSE 0. END) AS Units2017_2,
    SUM(CASE WHEN T.FiscalYear = 2017 AND T.FiscalPeriod = 2 THEN T.SalesValue ELSE 0. END) AS Sales2017_2,
    SUM(CASE WHEN T.FiscalYear = 2018 AND T.FiscalPeriod = 2 THEN T.SalesQty ELSE 0. END) AS Units2018_2,
    SUM(CASE WHEN T.FiscalYear = 2018 AND T.FiscalPeriod = 2 THEN T.SalesValue ELSE 0. END) AS Sales2018_2,
    SUM(CASE WHEN T.FiscalYear = 2018 AND T.FiscalPeriod = 2 THEN T.SalesQty ELSE 0. END) -
        SUM(CASE WHEN T.FiscalYear = 2017 AND T.FiscalPeriod = 2 THEN T.SalesQty ELSE 0. END) AS DiffUnits_2,
    SUM(CASE WHEN T.FiscalYear = 2018 AND T.FiscalPeriod = 2 THEN T.SalesValue ELSE 0. END) -
        SUM(CASE WHEN T.FiscalYear = 2017 AND T.FiscalPeriod = 2 THEN T.SalesValue ELSE 0. END) AS DiffSales_2,
    SUM(CASE WHEN T.FiscalYear = 2017 AND T.FiscalPeriod = 3 THEN T.SalesQty ELSE 0. END) AS Units2017_3,
    SUM(CASE WHEN T.FiscalYear = 2017 AND T.FiscalPeriod = 3 THEN T.SalesValue ELSE 0. END) AS Sales2017_3,
    SUM(CASE WHEN T.FiscalYear = 2018 AND T.FiscalPeriod = 3 THEN T.SalesQty ELSE 0. END) AS Units2018_3,
    SUM(CASE WHEN T.FiscalYear = 2018 AND T.FiscalPeriod = 3 THEN T.SalesValue ELSE 0. END) AS Sales2018_3,
    SUM(CASE WHEN T.FiscalYear = 2018 AND T.FiscalPeriod = 3 THEN T.SalesQty ELSE 0. END) -
        SUM(CASE WHEN T.FiscalYear = 2017 AND T.FiscalPeriod = 3 THEN T.SalesQty ELSE 0. END) AS DiffUnits_3,
    SUM(CASE WHEN T.FiscalYear = 2018 AND T.FiscalPeriod = 3 THEN T.SalesValue ELSE 0. END) -
        SUM(CASE WHEN T.FiscalYear = 2017 AND T.FiscalPeriod = 3 THEN T.SalesValue ELSE 0. END) AS DiffSales_3
FROM (
    SELECT 1 AS FP UNION ALL SELECT 2 UNION ALL SELECT 3
    ) AS P
    LEFT OUTER JOIN Totals AS T
        ON P.FP = T.FiscalPeriod

  DROP TABLE #SalesData;

  EDIT: Re-posted updated code to correctly get the sign of the differences.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)