December 22, 2016 at 9:13 pm
Hi Experts consider the below scripts,
CREATE TABLE #TEMP(Source NVARCHAR(100),Target nvarchar(100),StartDate int,EndDate int)
INSERT INTO #TEMP(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Pune',20150110,20150220)
INSERT INTO #TEMP(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Pune',20150221,20150320)
INSERT INTO #TEMP(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Pune',20150321,NULL) --20140410
INSERT INTO #TEMP(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Pune',20150411,20160120)
INSERT INTO #TEMP(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Pune',20160120,NULL)
INSERT INTO #TEMP(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Chennai',20140110,20150320)
INSERT INTO #TEMP(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Chennai',20150321,20150520)
INSERT INTO #TEMP(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Chennai',20150521,NULL) --20150710
INSERT INTO #TEMP(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Chennai',20150711,20160220)
INSERT INTO #TEMP(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Chennai',20160220,NULL)
INSERT INTO #TEMP(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Bangalore',20140110,20150320)
INSERT INTO #TEMP(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Bangalore',20150321,20150520)
INSERT INTO #TEMP(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Bangalore',20150521,NULL)
CREATE TABLE #ExpectedResult(Source NVARCHAR(100),Target nvarchar(100),StartDate int,EndDate int)
INSERT INTO #ExpectedResult(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Pune',20150110,20150220)
INSERT INTO #ExpectedResult(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Pune',20150221,20150320)
INSERT INTO #ExpectedResult(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Pune',20150321,20140410)
INSERT INTO #ExpectedResult(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Pune',20150411,20160120)
INSERT INTO #ExpectedResult(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Pune',20160120,NULL)
INSERT INTO #ExpectedResult(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Chennai',20140110,20150320)
INSERT INTO #ExpectedResult(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Chennai',20150321,20150520)
INSERT INTO #ExpectedResult(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Chennai',20150521,20150710)
INSERT INTO #ExpectedResult(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Chennai',20150711,20160220)
INSERT INTO #ExpectedResult(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Chennai',20160220,NULL)
INSERT INTO #ExpectedResult(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Bangalore',20140110,20150320)
INSERT INTO #ExpectedResult(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Bangalore',20150321,20150520)
INSERT INTO #ExpectedResult(Source ,Target ,StartDate ,EndDate ) VALUES('Mumbai','Bangalore',20150521,NULL)
I had specify Temp as my source data and ExpepectedResult as my ExpectedResult. I tried Leap and Lag, but Leap and Lag goes onto entire table, i dont know how to command it to stop for Mumbai-Pune,Mumbai-Chennai and Mumbai-Bangalore. Also the last entry only should be NULL.
December 23, 2016 at 2:14 am
You haven't actually asked a question here. What are you trying to achieve (yes I know you've given expected results, but how do you get these)?
Without knowing what you're trying to do, then we have no way of know how to achieve it.
I'm not even sure where some of your data comes from. For example, your Expected results has a Mumbai row with an end date of 20140410. That date isn't in your TEMP data, so where did it come from (Another table, default result, a present from Santa :-P)? Are you using a different table as well or more fields you haven't shared with us?
Please elaborate on your goal and provide some more detail.
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 23, 2016 at 3:00 am
StartDate is entered through application.
EndDate is something that is entered as Next(StartDate) - 1 day.
Like i said i am trying to used Leap and Lag function but what i am not able to do is apply that logic only for a bunch for Target-Source combination and last value of EndDate should be NULL
December 23, 2016 at 3:22 am
This gives the logic you asked for, however, not the expected results, for example i do not get the following value:
Source Target StartDate EndDate
------- ------- ---------- ---------
Mumbai Pune 20150321 20140410
As stated above, 2014 not in your Sample data.
I have had to convert your INT type StartDates to DATE value to do Date Maths. If they are coming from a Date Dimension, then change the below query to use your Date Dimension's Date Field and then return the key in the bottom select (You will not need the WITH then, as you can do it in one parse). If not, you will need to convert back to your INTEGER format.
/*
Start date isn't a Date, so i need to convert it.
Unless you're using a Dates Dimension you haven't mentioned?
*/
WITH CTE ([Source], [Target], [StartDate]) AS
(
SELECT T.[Source],
T.[Target],
CAST(LEFT(CAST(T.StartDate AS VARCHAR(10)), 4) + '-' + LEFT(RIGHT(CAST(T.StartDate AS VARCHAR(10)), 4),2) + '-' + RIGHT(CAST(T.StartDate AS VARCHAR(10)),2) AS DATE)
FROM #TEMP T
)
SELECT C.[Source],
C.[Target],
C.StartDate,
LEAD(DATEADD(DAY, -1, C.StartDate)) OVER (PARTITION BY C.[Source],
C.[Target]
ORDER BY C.StartDate ASC) AS EndDate
FROM CTE C
ORDER BY C.[Source] ASC,
C.[Target] DESC;
Edit: Alignment fixing.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 23, 2016 at 5:11 am
SELECT
[Source],
[Target],
StartDate,
EndDate = ISNULL(EndDate,LEAD(StartDate,1) OVER(PARTITION BY Source, Target ORDER BY StartDate,EndDate) - 1)
FROM #TEMP
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply