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