SQL join table to itself to get data for previous year

Question

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.

Answer 1

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())

Answer 2

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;