T-SQL Query to get the Month wise data

Question

T-SQL Query to get the Month wise data

praveen-359966

SSC Veteran

Points: 275
More actions
September 13, 2015 at 10:59 pm

#307619

Hi Experts,
I have the data in the below format.
Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201406 xxxxxx 27 1
In the above data, no record exist for 201403,201404,201405, query I wrote will give only the data for which there LeftCount exists, but I am looking for a query which get the data in the below format.
Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201403 xxxxxx 28 0
201404 xxxxxx 28 0
201405 xxxxxx 28 0
201406 xxxxxx 27 1
Please share your views on this.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247154 More actions · Answer 1

I would recommend that you create a calendar table[/url] and create a left join from your calendar table to the table which contains data.

pietlinden SSC Guru Points: 63572 More actions · Answer 2

thanks for the data. Helps a lot, but really you should post it in a consumable format (because folks here are busy... so set the scenario up for them). Something like this:

CREATE TABLE SomeData (

MonthYear INT NOT NULL

,Department VARCHAR(20)

,TotalCount INT

,LeftCount INT

);

GO

INSERT INTO SomeData(MonthYear,Department,TotalCount,LeftCount)

VALUES (201401, 'xxxxxx', 30, 0)

,(201402,'xxxxxx', 28, 2)

,(201406, 'xxxxxx', 27, 1);

CREATE TABLE Calendar(MonthYear INT PRIMARY KEY);

GO

INSERT INTO Calendar(MonthYear) VALUES (201401),(201402),(201403),(201404),(201405),(201406);

Here's my start at an answer. The hard part is the "go back to the last non-null value (I'm thinking a max less than X)...

SELECT c.MonthYear

, sd.Department

, sd.TotalCount

, COALESCE(sd.TotalCount,LAG(sd.TotalCount,1) OVER (ORDER BY c.MonthYear)) AS OneNull

, COALESCE(sd.LeftCount,0) AS NewLeftCount

FROM Calendar c LEFT JOIN SomeData sd

ON c.MonthYear = sd.MonthYear;

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 3

First of all you need to join your data against a list containing all dates.

That can either be a calendar table, as Phil suggested, or you can generate one on the fly using a tally table. (if you don't know how, google tally table).

Then you need to find the last non null value (in your example 28 of the date 201402), which is a puzzle already solved by Itzik Ben-Gan:

The Last non NULL Puzzle

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Eirikur Eiriksson SSC Guru Points: 182946 More actions · Answer 4

Quick suggestion

USE tempdb;

GO

SET NOCOUNT ON;

/* Sample data from OP */

DECLARE @EXISTING TABLE

(

[Month(YYYYMM)] INT NOT NULL

,Department VARCHAR(10) NOT NULL

,TotalCount INT NOT NULL

,LeftCount INT NOT NULL

);

INSERT INTO @EXISTING([Month(YYYYMM)],Department,TotalCount,LeftCount)

VALUES ( 201401, 'xxxxxx', 30, 0)

,( 201402, 'xxxxxx', 28, 2)

,( 201406, 'xxxxxx', 27, 1)

;

/* Inline calendar table */

;WITH START_END AS

(

SELECT

CONVERT(DATE,STUFF(CONVERT(VARCHAR(6),MIN([Month(YYYYMM)]),0) + '.01',5,0,CHAR(46)),102) AS FIRST_DATE

,DATEDIFF(MONTH,CONVERT(DATE,STUFF(CONVERT(VARCHAR(6),MIN([Month(YYYYMM)]),0) + '.01',5,0,CHAR(46)),102)

,CONVERT(DATE,STUFF(CONVERT(VARCHAR(6),MAX([Month(YYYYMM)]),0) + '.01',5,0,CHAR(46)),102)) + 1 AS MONTH_COUNT

FROM @EXISTING

)

,T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,NUMS(N) AS

(

SELECT

TOP (SELECT MONTH_COUNT FROM START_END) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N

FROM T T1,T T2,T T3

)

,DATE_LIST AS

(

SELECT

CONVERT(INT,SUBSTRING(CONVERT(VARCHAR(10),DATEADD(MONTH,NM.N,SE.FIRST_DATE),112),1,6),0) AS YYYYMM

FROM NUMS NM

CROSS APPLY START_END SE

)

/* Use the calendar as base, left join the sample data and cross apply another instance where missing */

,FINAL_SET AS

(

SELECT

DL.YYYYMM

,ROW_NUMBER() OVER

(

PARTITION BY DL.YYYYMM

ORDER BY E.[Month(YYYYMM)] DESC

,DL.YYYYMM ASC

) AS DL_RID

,E.Department

,E.TotalCount

,ISNULL(EX.LeftCount,0) AS LeftCount

FROM DATE_LIST DL

LEFT OUTER JOIN @EXISTING EX

ON DL.YYYYMM = EX.[Month(YYYYMM)]

OUTER APPLY @EXISTING E

WHERE DL.YYYYMM >= E.[Month(YYYYMM)]

)

SELECT

FS.YYYYMM

,FS.Department

,FS.TotalCount

,FS.LeftCount

FROM FINAL_SET FS

WHERE FS.DL_RID = 1;

Results

YYYYMM Department TotalCount LeftCount

----------- ---------- ----------- -----------

201401 xxxxxx 30 0

201402 xxxxxx 28 2

201403 xxxxxx 28 0

201404 xxxxxx 28 0

201405 xxxxxx 28 0

201406 xxxxxx 27 1

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 5

Phil Parkin (9/14/2015)
I would recommend that you create a calendar table[/url] and create a left join from your calendar table to the table which contains data.

Any mileage in looking for "gaps" in the data, do you think?

Phil Parkin SSC Guru Points: 247154 More actions · Answer 6

Kristen-173977 (9/14/2015)
Phil Parkin (9/14/2015)
I would recommend that you create a calendar table[/url] and create a left join from your calendar table to the table which contains data.
Any mileage in looking for "gaps" in the data, do you think?

Depends on the requirement

T-SQL Query to get the Month wise data

Cookies on SQLServerCentral