How to get last 12 months values when some months have no records in the table I have a table which has PartNumberKey and Depot_ID as composite primary key. It returns the following data - 



The requirement is to add the month columns for which there is no row with 0 as Total for last 12 months (i.e from 2018-11 to 2018-12). The expected output should be:



Kindly help me with the query formation?
Start with a table of numbers, which you then use with dateadd to make a table of months. Then you can left join it.

If you don’t have a table of numbers, you can use ROW_NUMBER on a large enough table.

SELECT TOP (DATEDIFF(month, @month1, @month2)+1)
    DATEADD(month, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) -1, @month1) AS TheMonth
    FROM dbo.MyTable


You’ll also need your list of PartNumberKey and Depot_ID values.

DECLARE @month1 date = ‘20171201’, @month2 date = ‘20181101’; -- the semicolon is important!!
WITH months as (
    SELECT TOP (DATEDIFF(month, @month1, @month2)+1)
    DATEADD(month, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) -1, @month1) AS TheMonth
    FROM dbo.MyTable
),
PartsDepots as (
    SELECT DISTINCT PartNumberKey, Depot_ID
    FROM dbo.MyTable
)
SELECT pd.PartNumberKey, pd.Depot_ID, m.TheMonth, COUNT(*) AS Total
FROM months m
CROSS JOIN PartsDepots pd 
LEFT JOIN dbo.MyTable t
    ON t.TheDate &gt;= m.TheMonth
    AND t.TheDate &lt; DATEADD(month,1,m.TheMonth)
    AND t.PartNumberKey = pd.PartNumberKey
    AND t.Depot_ID = pd.Depot_ID
GROUP BY t.TheMonth, pd.PartNumberKey, pd.Depot_ID
;