Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nirnir2
Starting Member
20 Posts |
 Posted - 2015-03-16 : 04:21:02
|
I have the following table .
DECLARE @TBL_TEST TABLE (RECID INT,dateStart datetime , dateEnd datetime, prevRecid int , nextRecid int, xkey char(20) )
INSERT INTO @TBL_TEST VALUES (11,'20140301','20150228',null,null,'key123')
INSERT INTO @TBL_TEST VALUES (22,'20130301','20140228',null,null,'key123')
INSERT INTO @TBL_TEST VALUES (33,'20120301','20130228',null,null,'key123')
INSERT INTO @TBL_TEST VALUES (44,'20140301','20150228',null,null,'key124')
INSERT INTO @TBL_TEST VALUES (55,'20130301','20140228',null,null,'key124')
INSERT INTO @TBL_TEST VALUES (66,'20120301','20130228',null,null,'key124')
INSERT INTO @TBL_TEST VALUES (77,'20120301','20130228',null,null,'key125')
I need to update the fields nextRecid , prevRecid .
nextRecid with the recid of record which have same xkey and its dateStart is one day after dateEnd of original record .
prevRecid with the recid of record which have same xkey and its dateEnd is one day before dateStart of original record .
after the update the data should looks like
RECID DateStart DateEnd PrevRecid NextRecid Xkey
11 2014-03-01 2015-02-28 22 NULL key123
22 2013-03-01 2014-02-28 33 11 key123
33 2012-03-01 2013-02-28 NULL 22 key123
44 2014-03-01 2015-02-28 55 NULL key124
55 2013-03-01 2014-02-28 66 44 key124
66 2012-03-01 2013-02-28 NULL 55 key124
77 2012-03-01 2013-02-28 NULL NULL key125 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-16 : 08:54:28
|
| This is obviously a homework question. That means you need to make an effort to solve it yourself. Post your work as you go along and call out for help if you get stuck. |
 |
|
|
nirnir2
Starting Member
20 Posts |
Posted - 2015-03-16 : 10:39:12
|
with cte as
(select f.recid,s.recid as nextRecid from @TBL_TEST f inner join @TBL_TEST s on f.xkey = s.xkey and DATEDIFF(day,s.dateStart,f.dateEnd ) BETWEEN -3 AND 3)
update @TBL_TEST set nextRecid=cte.nextRecid from cte inner join @TBL_TEST t on cte.RECID =t.RECID
with cte as
(select f.recid,s.recid as prevRecid from @TBL_TEST f inner join @TBL_TEST s on f.xkey = s.xkey and DATEDIFF(day,f.dateStart,s.dateEnd ) BETWEEN -3 AND 3)
update @TBL_TEST set prevRecid=cte.prevRecid from cte inner join @TBL_TEST t on cte.RECID =t.RECID
select * from @TBL_TEST |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-03-16 : 13:39:25
|
| As far as I can see, the queries you developed generate the output you desire! Good work!! |
|
|
|
|
|
|
|
|
calculate linked records