CODEWITHSUNDEEP
sqlsql-servert-sqljoinself-join
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6940

SQL join table to itself to get data for previous year

SQL. How can I join table to itself to get desired results just as shown in the table below. The logic is that I want to have Units for the same product and corresponding month of previous year.

The simple left join on source table to itself on key a.[year]=b.[year]+1 (and of course month to month and product to product) would cause the loss of the data where we had values in the previous year and do not have now.

enter image description here

Upvotes: 3

Views: 7709

Answers (5)

Salman Arshad
Salman Arshad

Reputation: 272106

You could generate all possible combinations for year, month and product in your data using CROSS JOIN. A simple LEFT JOIN will give you the value or NULL if data for a specific combination exists.

DECLARE @t TABLE (year int, month int, product int, unit int);
INSERT INTO @t VALUES
(2017, 1, 1, 1721),
(2017, 2, 1, 4915),
(2017, 5, 1, 5230),
(2018, 2, 1, 5216),
(2018, 3, 1, 8911),
(2017, 4, 2, 2933),
(2018, 1, 2, 7672);

SELECT ally.year, allm.month, allp.product, curr.units, prev.units AS units_prev
FROM (SELECT DISTINCT year FROM @t) AS ally
CROSS JOIN (SELECT DISTINCT product FROM @t) AS allp
CROSS JOIN (SELECT DISTINCT month FROM @t) AS allm
LEFT JOIN @t AS curr ON curr.year = ally.year AND curr.product = allp.product AND curr.month = allm.month
LEFT JOIN @t AS prev ON prev.year = ally.year - 1 AND prev.product = allp.product AND prev.month = allm.month

Result:

| year | month | product | units | units_prev |
|------|-------|---------|-------|------------|
| 2017 | 1     | 1       | 1721  | NULL       |
| 2017 | 2     | 1       | 4915  | NULL       |
| 2017 | 3     | 1       | NULL  | NULL       |
| 2017 | 4     | 1       | NULL  | NULL       |
| 2017 | 5     | 1       | 5230  | NULL       |
| 2017 | 1     | 2       | NULL  | NULL       |
| 2017 | 2     | 2       | NULL  | NULL       |
| 2017 | 3     | 2       | NULL  | NULL       |
| 2017 | 4     | 2       | 2933  | NULL       |
| 2017 | 5     | 2       | NULL  | NULL       |
| 2018 | 1     | 1       | NULL  | 1721       |
| 2018 | 2     | 1       | 5216  | 4915       |
| 2018 | 3     | 1       | 8911  | NULL       |
| 2018 | 4     | 1       | NULL  | NULL       |
| 2018 | 5     | 1       | NULL  | 5230       |
| 2018 | 1     | 2       | 7672  | NULL       |
| 2018 | 2     | 2       | NULL  | NULL       |
| 2018 | 3     | 2       | NULL  | NULL       |
| 2018 | 4     | 2       | NULL  | 2933       |
| 2018 | 5     | 2       | NULL  | NULL       |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

year month

Use cross join to generate the rows, left join to bring in the data and then lag() to get the "previous" value:

select y.year, m.month, p.product, t.units,
       lag(t.units) over (partition by p.product, m.month order by y.year) as prev_units
from (select distinct year from t) y cross join
     (select distinct month from t) m cross join
     (select distinct product from t) p left join
     t
     on t.year = y.year and t.month = m.month and t.product = p.producct;

Upvotes: 2

Thom A
Thom A

Reputation: 95561

I would go with LAG, and a calendar table.

SELECT C.[Year],
       C.[Month],
       YPT.product,
       YST.units,
       YST.LAG(YST.units) OVER (PARTITION BY YTP.[product],C.[month] ORDER BY C.[year]) AS UnitsPrev
FROM CalendarTable C
     CROSS JOIN YourProductTable YPT
     LEFT JOIN YourSourceTable YST ON C.[Year] YST.[Year]
                                  AND C.[Month] = YST.[Month]
                                  AND YPT.Product = YST.Product
WHERE C.[day] = 1
  AND C.[date] BETWEEN {SomeStartDate} AND {SomeEndDate];

This guessing a little on your design (it assumes you have a product table).

Upvotes: 1

PhilS
PhilS

Reputation: 624

If you want the rows where nothing was sold in both 2017 and 2018 as in your desired results for March 2017 as well you need to generate out the month, year and join in the product to get the null values.

This query does it for the month and year, hopefully you should be able to add the Product as well if required

DECLARE @startMonth INT=1
DECLARE @endMonth INT=12
DECLARE @startYear INT=2017
DECLARE @endYear INT=2018
;
WITH months AS (
    SELECT @startMonth AS m
    UNION ALL
    SELECT m+1 FROM months WHERE m+1<=@endMonth
),
years AS (
    SELECT @startYear AS y
    UNION ALL
    SELECT y+1 FROM years WHERE y+1<=@endYear
),
monthYears AS (
    SELECT m, y
    FROM months, years
)
SELECT  thisYear.[Year], thisYear.[Month], thisYear.[Product], thisYear.[Units], prevYear.[Units] as units_prev
FROM 
    (SELECT [Product], my.y as [Year], my.m as [Month], [Units]
    FROM monthYears my
    LEFT JOIN sales on my.m = [Month] and my.y = [Year]) as thisYear
LEFT OUTER JOIN     
    (SELECT [Product], my.y as [Year], my.m as [Month], my.y + 1 as NextYear, [Units]
    FROM monthYears my
    LEFT JOIN sales on my.m = [Month] and my.y = [Year])  as prevYear 
    on thisYear.Product = prevYear.Product
        and (thisYEAR.[Year]) = prevYear.[NextYear]
        and thisYEAR.[Month] = prevYear.[Month]
ORDER BY thisYear.[Year], thisYear.[Month], thisYear.[Product] 
option (maxrecursion 12);

Upvotes: 0

Andrew
Andrew

Reputation: 27294

A full join should be sufficient

  select distinct
    coalesce(a.year, b.year+1) as year
    , coalesce(a.month, b.month) as month
    , coalesce(a.product, b.product) as product
    , a.units as units
    , b.units as units_prev
  from yourtable a
  full join yourtable b on a.[year] = b.[year]+1 and a.[month] = b.[month] and a.product = b.product

Your expected results though are slightly off from the description 2018, month 2, product 2 does not exist with a prior value of 2933.

DB Fiddle : https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=d01dc5bd626854b083be0864f2d5b0e4

Result : 

year    month   product units   units_prev
2017    1       1       1721    
2017    2       1       4915    
2017    4       2       2933    
2017    5       1       5230    
2018    1       1               1721
2018    1       2       7672    
2018    2       1       5216    4915
2018    3       1       8911    
2018    4       2               2933
2018    5       1               5230
2019    1       2               7672
2019    2       1               5216
2019    3       1               8911

If you need to filter out futures like that, then you can add an additional where predicate, something like :

where coalesce(a.year, b.year+1) <= year(getdate())

Upvotes: 5

Related Questions