Nsryjdtyk

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