September 13, 2015 at 10:59 pm
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.
September 14, 2015 at 12:00 am
September 14, 2015 at 12:00 am
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;
September 14, 2015 at 12:09 am
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:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 14, 2015 at 12:28 am
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
September 14, 2015 at 1:21 am
September 14, 2015 at 2:12 am
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 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply