November 7, 2011 at 6:59 pm
I have got a data like:
IDLevelSeq
1001,1,1
1002,1,2
1003,1,3
1004,1,4
1005,1,5
2001,2,1
2002,2,2
2003,2,3
2004,2,4
3001,3,1
3002,3,2
I want to implement 2 more derived columns based on the data above which contains forward and backward links
i.e. Take for example ID = 1001, this is contained in Level 1 and has Seq = 1. There is no previous Seq for this Level so backward Link will be NULL. The forward Link will be next sequence for this Level which is 1002 and so on...
I want the final result to look like:
IDLevelNumBWDFWD
1001,1,1,NULL,1002
1002,1,2,1001,1003
1003,1,3,1002,1004
1004,1,4,1003,1005
1005,1,5,1004,NULL
2001,2,1,NULL,2002
2002,2,2,2001,2003
2003,2,3,2002,2004
2004,2,4,2003,NULL
3001,3,1,NULL,3002
3002,3,2,3001,NULL
How can this be implemented using a SQL Function?
November 7, 2011 at 8:02 pm
I managed to write a sample code and it's working as expected:
DECLARE @t TABLE
(ReadNum int,
GroupLevel int,
SeqNum int
)
Insert Into @t Values (1001,1,1)
Insert Into @t Values (1002,1,2)
Insert Into @t Values (1003,1,3)
Insert Into @t Values (1004,1,4)
Insert Into @t Values (1005,1,5)
Insert Into @t Values (2001,2,1)
Insert Into @t Values (2002,2,2)
Insert Into @t Values (2003,2,3)
Insert Into @t Values (2004,2,4)
Insert Into @t Values (3001,3,1)
Insert Into @t Values (3002,3,2)
;With cte_main
AS
(Select
ReadNum,
GroupLevel,
SeqNum
From @t
)
Select
main.ReadNum,
main.GroupLevel,
main.ReadNum,
BWD.ReadNum AS BWD_ReadNum,
FWD.ReadNum AS FWD_ReadNum
From
cte_main main
Left Join
cte_main BWD
ON BWD.GroupLevel = main.GroupLevel
AND BWD.SeqNum = (main.SeqNum - 1)
Left Join
cte_main FWD
ON FWD.GroupLevel = main.GroupLevel
AND FWD.SeqNum = (main.SeqNum + 1)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply