May 30, 2017 at 4:25 am
Hi,
I've scenario to show "as on status" similar to monthly snapshot. Below is the table where I need to display as in expected result which will be consumed in reporting. I tried to join with date table, but unable to get the expected result.
Expected result will fill the missing month. Status is just one column givene.g, I'll have more columns similar to status and few other tables as well.
Need to join and create a monthly snapshot .
id,status,crdate
1,open,01/31/2017
1,IP,03/31/2017
1,cl,05/31/2017
Expected result.
id,status,crdate
1,open,01/31/2017
1,open,02/28/2017
1,IP,03/31/2017
1,IP,04/30/2017
1,cl,05/31/2017
1,cl,06/30/2017
Thank you.
May 30, 2017 at 4:48 am
CREATE TABLE #PleasePostConsumableDDLNextTime (
id int
, status char(4)
, crdate date
);
INSERT INTO #PleasePostConsumableDDLNextTime
VALUES
(1,'open','01/31/2017')
, (1,'IP','03/31/2017')
, (1,'cl','05/31/2017')
SELECT
p.id
, p.status
, v.MyDate AS crdate
FROM #PleasePostConsumableDDLNextTime p
CROSS APPLY (
-- add one day and one month and subtract one day to get last day of next month
-- this assumes the original date is the last date of the month it's in
VALUES
(crdate)
, (DATEADD(DAY,-1,DATEADD(MONTH,1,(DATEADD(DAY,1,crdate))))))
v(MyDate)
May 30, 2017 at 10:48 am
Thank you for the quick reply.
I tested the query. But the logic doesn't get the expected result when the interval between date varies. Inserted below data and the result doesn't give consecutive dates. Not sure if am missing anything.
INSERT INTO #PleasePostConsumableDDLNextTime
VALUES
(1,'open','01/31/2017')
, (1,'IP','03/31/2017')
, (1,'cl','06/31/2017')
result - May month is missing.
id status crdate
1 open 2017-01-31
1 open 2017-02-28
1 IP 2017-03-31
1 IP 2017-04-30
1 cl 2017-06-30
1 cl 2017-07-31
May 31, 2017 at 2:14 am
Yes, if you don't want gaps for missing months then you'll need to left join to a calendar table. You can either use a permanent table in your database or create one on the fly. If you build it so that each row includes the last date of the month and the last date of the next month, then you can eliminate the need for the date arithmetic in the query that I posted before.
John
May 31, 2017 at 10:31 am
CREATE TABLE #TempTable
(
ID INT NOT NULL,
Status VARCHAR(10) NOT NULL,
StartDate DATE NOT NULL
)
INSERT INTO #TempTable
(ID,Status,StartDate)
VALUES
(1,'open','01/31/2017'),(1,'IP','03/31/2017'),(1,'cl','05/31/2017')
SELECT TT.ID, TT.Status, DT.EndDate
FROM #TempTable AS TT
CROSS APPLY (VALUES (TT.StartDate), (EOMONTH(DATEADD(M,1,TT.StartDate)))) DT(EndDate)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply