I have a table which has PartNumberKey and Depot_ID as composite primary key. It returns the following data -

enter image description here

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:

enter image description here

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 >= m.TheMonth
    AND t.TheDate < 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
| improve this answer | |

Assuming this table:

  PartNumberKey char(5),
  Depot_ID char(2),
  [Month] char(7),
  Total int,

INSERT #ProductData VALUES('1538A','2B','2018-02',17),

An interesting problem because you need to both determine missing months and also fill in the missing key/ID combos for those missing months.

DECLARE @months    int  = 12, -- up to 100
        @thismonth date = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()),1);

  SELECT m = 1 UNION ALL SELECT m + 1 FROM m WHERE m < @months
months(m) AS
  SELECT CONVERT(char(7), DATEADD(MONTH, 1-m, @thismonth))
  FROM m
keypairs AS
  SELECT PartNumberKey, Depot_ID
    FROM #ProductData
    WHERE [Month] >= CONVERT(char(7), DATEADD(MONTH, -11, @thismonth), 120)
    GROUP BY PartNumberKey, Depot_ID
SELECT m.m, p.PartNumberKey, p.Depot_ID, COALESCE(pd.Total,0)
  FROM months AS m
  CROSS JOIN keypairs AS p
  LEFT OUTER JOIN #ProductData AS pd
    ON p.PartNumberKey = pd.PartNumberKey
   AND p.Depot_ID = pd.Depot_ID
   AND m.m = pd.[Month]
ORDER BY p.PartNumberKey, p.Depot_ID, m.m;
| improve this answer | |
  • 1
    Ah - I was typing too... – Rob Farley Nov 5 '18 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.