SQL Server – How to Get Last 12 Months Values When Some Months Have No Records

join;sql servert-sql

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?

Best Answer

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
;

Related Solutions

Postgresql – Combine a generated series with each row from a select

You need to do a left outer join on the generate_series with an ON clause that matches the date of each report.

Without a schema or sample data it's a bit fiddly to cook up an example modification of your actual query.

Here's a simplified example to show you how it works:

CREATE TABLE sparse_dates(
    bakedgood text primary key,
    firstbaked date not null
);

INSERT INTO sparse_dates (bakedgood, firstbaked) VALUES
('tart', '2012-02-01'),
('baguette', '2012-02-01'),
('cookie', '2012-03-01'),
('macaron', '2012-08-01');

To get the number of baked goods first baked on a given month:

SELECT monthtimestamp, count(bakedgood) 
FROM generate_series( 
    (SELECT min(firstbaked) FROM sparse_dates),
    (SELECT max(firstbaked) FROM sparse_dates),
    INTERVAL '1' MONTH) 
    AS monthtimestamp
LEFT OUTER JOIN sparse_dates ON (monthtimestamp = firstbaked)
GROUP BY monthtimestamp;

with output like:

     monthtimestamp     | count 
------------------------+-------
 2012-02-01 00:00:00+08 |     2
 2012-03-01 00:00:00+08 |     1
 2012-04-01 00:00:00+08 |     0
 2012-05-01 00:00:00+08 |     0
 2012-06-01 00:00:00+08 |     0
 2012-07-01 00:00:00+08 |     0
 2012-08-01 00:00:00+08 |     1
(7 rows)

Sql-server – Adding Last 12 Months to a row in a table (SQL Server)

Perhaps you want something like this?

INSERT INTO dbo.Test (
    Department
    , Month12
    , Month11
    , Month10
    , Month9
    , Month8
    , Month7
    , Month6
    , Month5
    , Month4
    , Month3
    , Month2
    , Month1
    )
VALUES ('Test'
    , DATEADD(MONTH, -12, GETDATE())
    , DATEADD(MONTH, -11, GETDATE())
    , DATEADD(MONTH, -10, GETDATE())
    , DATEADD(MONTH, -9, GETDATE())
    , DATEADD(MONTH, -8, GETDATE())
    , DATEADD(MONTH, -7, GETDATE())
    , DATEADD(MONTH, -6, GETDATE())
    , DATEADD(MONTH, -5, GETDATE())
    , DATEADD(MONTH, -4, GETDATE())
    , DATEADD(MONTH, -3, GETDATE())
    , DATEADD(MONTH, -2, GETDATE())
    , DATEADD(MONTH, -1, GETDATE())
);

This assumes several things.

You have DATE (or perhaps DATETIME, etc) as the data type for the 12 columns for each month.
You want each month to reflect the current day. For instance, since it is currently Jan 2nd, this code would insert 2014-01-02, 2014-02-02, ... 2014-12-02 into the table.

If you are using SQL Server 2012 and above, you can modify this:

DATEADD(MONTH, -1, GETDATE())

to this:

FORMAT(DATEADD(MONTH, -1, GETDATE()), 'MMM-yyyy')

To get the date as Jan-2014. There are also a lot of other ways that are probably more efficient.

Best Answer

Related Solutions

Postgresql – Combine a generated series with each row from a select

Sql-server – Adding Last 12 Months to a row in a table (SQL Server)

Related Question