badry badry - 5 months ago 23
SQL Question

SQL query to get current and last year sales

I have the following table Sales:

Date Store Sales
1/1/2015 St01 12123
1/1/2015 St02 3123
1/1/2016 St01 4213
1/1/2016 St03 2134


When I try to self join to get this year and last year sales the closed store is not showing up.
The result should be like this:

Date Store This year Sales Last Year Sales
1/1/2016 St01 4213 1212
1/1/2016 St02 0 3123
1/1/2016 St03 2134 0


My query as follows:

SELECT CY.DATE,
CY.store cy.Sales,
LY.sales
FROM sales CY,
sales LY
WHERE CY.store(+) = LY.store(+)
AND LY.DATE = CY.DATE - 365

Answer

Since I want the query to return day by day sales I used MT0 answer and added the dates, this way I can get the data for all year days.

WITH AllYear AS
 (select to_date('2016-01-01', 'yyyy-mm-dd') + level - 1 AS dobs
    from dual
  connect by level <= 366)

SELECT dobs AS "DATE",
       Store,
       nvl(SUM(CASE
                 WHEN t.Date = dobs THEN
                  t.sales
               END),
           0) AS "This Year Sales",
       nvl(SUM(CASE
                 WHEN t.Date = dobs-365 THEN
                  t.sales
               END),
           0) AS "Last Year Sales"
  FROM Sales t,AllYear
 where dobs='01-Jan-2016'
 GROUP BY Store
 ORDER BY Store;
Comments