June 24, 2010 at 12:57 am
Hi All,
I have a query to be formed which I am facing difficulty with,
Here is the table:
Index_code | Code1 | Code2 | StartDate | EndDate
----------------------------------------------------------
AAA111 1 3 20021017 20030501
AAA111 1 3 20030501 NULL
AAA112 1 2 19990405 20010530
AAA112 1 2 20010530 NULL
Here is the expected result:
Index_code | Code1 | Code2 | StartDate | EndDate
----------------------------------------------------------
AAA111 1 3 20021017 20030430
AAA111 1 3 20030501 NULL
AAA112 1 2 19990405 20010529
AAA112 1 2 20010530 NULL
The requirement is must make sure the EndDate of line is StartDate - 1 of next line
for the same set of PK (in this case the PK is Index_code, Code1, Code2)..
Could anyone tell me how to do ? Totally run out of idea.............
June 24, 2010 at 1:15 am
This might help you
; WITH cte_TableName AS
(
SELECTROW_NUMBER() OVER( PARTITION BY index_code, code1, code2 ORDER BY startdate ) Row, *
FROMTableName
)
SELECTT1.index_code, T1.code1, T1.code2, T1.startdate, DATEADD( DAY, -1, T2.startdate ) enddate
FROMcte_TableName T1
LEFT JOIN cte_TableName T2
ON T1.index_code = T2.index_code
AND T1.code1 = T2.code1
AND T1.code2 = T2.code2
AND T1.Row = T2.Row - 1
Edited the INNER JOIN to LEFT JOIN for proper results
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 24, 2010 at 1:25 am
Try posting sample DDL and DML everytime for quick answers..check if below helps..
IF OBJECT_ID('TempDB..#Ixtable','U') IS NOT NULL
DROP TABLE #Ixtable
CREATE TABLE #Ixtable
(Index_code varchar(20),Code1 int,Code2 Int,Startdate Datetime,EndDate Datetime)
Insert into #Ixtable
select 'AAA111',1,3,'20021017','20030501'
union all
select 'AAA111',1,3,'20030501',null
union all
select 'AAA112',1,2,'19990405','20010530'
union all
select 'AAA112',1,2,'20010530',null
select * from #Ixtable
;with cte1 as(select ROW_NUMBER() OVER(ORDER BY Index_code) AS 'rn',index_code,code1,code2,startdate,enddate
from #Ixtable)
select c1.index_code,c1.code1,c1.code2,c1.startdate,c2.startdate-1 as 'Enddate'
from cte1 c1 left outer join cte1 c2 on c1.index_code = c2.index_code and
c2.rn= c1.rn+1
June 24, 2010 at 1:29 am
at1155 (6/24/2010)
PK is Index_code, Code1, Code2
I don't see this in your sample data. Are you sure?
-- Gianluca Sartori
June 24, 2010 at 1:32 am
Let me study the code first... Thanks dude
June 24, 2010 at 9:01 pm
Hi, the code is working fine ...however if i have data like these... this code gave me incorrect result
IF OBJECT_ID('TempDB..#Ixtable','U') IS NOT NULL
DROP TABLE #Ixtable
CREATE TABLE #Ixtable
(Index_code varchar(20),Code1 int,Code2 Int,Startdate Datetime,EndDate Datetime)
Insert into #Ixtable
select 'AAA111',1,3,'20021017','20030501'
union all
select 'AAA111',1,3,'20030501',null
union all
select 'AAA112',1,2,'19990405','20010530'
union all
select 'AAA112',1,2,'20010530','20010630'
union all
select 'AAA113',1,4,'20040506','20050617'
union all
select 'AAA114',1,4,'20080101','20090101'
union all
select 'AAA114',1,4,'20090101','20090831'
union all
select 'AAA114',1,4,'20090831','20091019'
union all
select 'AAA114',1,4,'20091019',null
select * from #Ixtable
;with cte1 as(select ROW_NUMBER() OVER(ORDER BY Index_code) AS 'rn',index_code,code1,code2,startdate,enddate
from #Ixtable)
select c1.index_code,c1.code1,c1.code2,c1.startdate,c2.startdate-1 as 'Enddate'
from cte1 c1 left outer join cte1 c2 on c1.index_code = c2.index_code and
c2.rn= c1.rn+1
Result return from the query
index_code code1 code2 startdate Enddate
-------------------- ----------- ----------- ----------------------- -----------------------
AAA111 1 3 2002-10-17 00:00:00.000 2003-04-30 00:00:00.000
AAA111 1 3 2003-05-01 00:00:00.000 NULL
AAA112 1 2 1999-04-05 00:00:00.000 2001-05-29 00:00:00.000
AAA112 1 2 2001-05-30 00:00:00.000 NULL
AAA113 1 4 2004-05-06 00:00:00.000 NULL
AAA114 1 4 2008-01-01 00:00:00.000 2008-12-31 00:00:00.000
AAA114 1 4 2009-01-01 00:00:00.000 2009-08-30 00:00:00.000
AAA114 1 4 2009-08-31 00:00:00.000 2009-10-18 00:00:00.000
AAA114 1 4 2009-10-19 00:00:00.000 NULL
Correct result should be like this
index_code code1 code2 startdate Enddate
-------------------- ----------- ----------- ----------------------- -----------------------
AAA111 1 3 2002-10-17 00:00:00.000 2003-04-30 00:00:00.000
AAA111 1 3 2003-05-01 00:00:00.000 NULL
AAA112 1 2 1999-04-05 00:00:00.000 2001-05-29 00:00:00.000
AAA112 1 2 2001-05-30 00:00:00.000 2001-06-30 00:00:00.000
AAA113 1 4 2004-05-06 00:00:00.000 2005-06-17 00:00:00.000
AAA114 1 4 2008-01-01 00:00:00.000 2008-12-31 00:00:00.000
AAA114 1 4 2009-01-01 00:00:00.000 2009-08-30 00:00:00.000
AAA114 1 4 2009-08-31 00:00:00.000 2009-10-18 00:00:00.000
AAA114 1 4 2009-10-19 00:00:00.000 NULL
I have tried to correct the code...but it doesn't work. Any help is appreciated 🙂
June 25, 2010 at 12:11 am
can you tell the exact business requirement for this, i think the result you stated now differs from the original post. we can help if you post the requirement
anyway ,try the below query:
;with cte1 as(select ROW_NUMBER() OVER(ORDER BY Index_code) AS 'rn',index_code,code1,code2,startdate,enddate
from #Ixtable)
select c1.index_code,c1.code1,c1.code2,c1.startdate,c2.startdate-1 as 'Enddate'
from cte1 c1 left outer join cte1 c2 on c1.index_code = c2.index_code
and c1.code1=c2.code1 and c1.code2 = c2.code2 and
c2.rn= c1.rn+1.
June 25, 2010 at 12:30 am
Hi, the requirements like this;
Must make sure the EndDate of line is StartDate - 1 of next line
for the same set of PK (in this case the PK is Index_code, Code1, Code2)..
for example:
Index_code Code1 Code2 Startdate EndDate
-------------------- ----------- ----------- ----------------------- -----------------------
AAA111 1 3 2002-10-17 00:00:00.000 2003-05-01 00:00:00.000
AAA111 1 3 2003-05-01 00:00:00.000 NULL
so the result is:
Index_code Code1 Code2 Startdate EndDate
-------------------- ----------- ----------- ----------------------- -----------------------
AAA111 1 3 2002-10-17 00:00:00.000 2003-04-30 00:00:00.000
AAA111 1 3 2003-05-01 00:00:00.000 NULL
However in certain case, example like below:
Index_code Code1 Code2 Startdate EndDate
-------------------- ----------- ----------- ----------------------- -----------------------
AAA111 1 3 2002-10-17 00:00:00.000 2003-05-01 00:00:00.000
AAA111 1 3 2003-05-01 00:00:00.000 2004-06-05 00:00:00:000
the EndDate in second line is not null so the value must be maintained. The result should be like this:
Index_code Code1 Code2 Startdate EndDate
-------------------- ----------- ----------- ----------------------- -----------------------
AAA111 1 3 2002-10-17 00:00:00.000 2003-04-30 00:00:00.000
AAA111 1 3 2003-05-01 00:00:00.000 2004-06-05 00:00:00:000
Do you get what I trying to say ?
June 25, 2010 at 1:51 am
Change the block as given below, I have just used a COALESCE() function to get the desired result
;with cte1 as(select ROW_NUMBER() OVER(ORDER BY Index_code) AS 'rn',index_code,code1,code2,startdate,enddate
from #Ixtable)
select c1.index_code,c1.code1,c1.code2,c1.startdate, -- c2.startdate-1 as 'Enddate'
coalesce( c2.startdate-1, c1.enddate ) as 'Enddate'
from cte1 c1 left outer join cte1 c2 on c1.index_code = c2.index_code and
c2.rn= c1.rn+1
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 25, 2010 at 2:00 am
I Missed two more joins (part of PK) in my earlier post. am updating the Kingstons final query to get the correct result.
;with cte1 as(select ROW_NUMBER() OVER(ORDER BY Index_code) AS 'rn',index_code,code1,code2,startdate,enddate
from #Ixtable)
select c1.index_code,c1.code1,c1.code2,c1.startdate, -- c2.startdate-1 as 'Enddate'
coalesce( c2.startdate-1, c1.enddate ) as 'Enddate'
from cte1 c1 left outer join cte1 c2 on c1.index_code = c2.index_code
and c1.code1=c2.code1 and c1.code2 = c2.code2 and
c2.rn= c1.rn+1
check if it helps...
June 25, 2010 at 2:13 am
NewBeeSQL (6/25/2010)
I Missed two more joins (part of PK) in my earlier post. am updating the Kingstons final query to get the correct result.
;with cte1 as(select ROW_NUMBER() OVER(ORDER BY Index_code) AS 'rn',index_code,code1,code2,startdate,enddate
from #Ixtable)
select c1.index_code,c1.code1,c1.code2,c1.startdate, -- c2.startdate-1 as 'Enddate'
coalesce( c2.startdate-1, c1.enddate ) as 'Enddate'
from cte1 c1 left outer join cte1 c2 on c1.index_code = c2.index_code
and c1.code1=c2.code1 and c1.code2 = c2.code2 and
c2.rn= c1.rn+1
check if it helps...
And lets hope some new Test condition doesn't crop up;-)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 25, 2010 at 2:35 am
The code is working properly 😛
Kingston and Newbee, thanks a lot... At least I have learned some new SQL skills
June 25, 2010 at 3:49 am
Glad we could help you out:-)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply