Set Based Solution - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Set Based Solution

Author

Topic

avmreddy17
Posting Yak Master

180 Posts

Posted - 2006-06-28 : 18:42:14

How can I get a all the Tickets Information related to one chain

If you see TicketID = 9 This has been replaced by TicketID 1571,
If you see TicketID = 12 This has been replaced three times.

If I pass 1574 to a SP , I need the whole chain ( i.e 1574 , 1573 , 1572 , 12 ) .
Is there a SET Based Solution for this

TicketID OldTicketID CancelReplaceStatus
----------- ----------- -------------------
2 NULL NULL
3 NULL NULL
4 NULL NULL
5 NULL NULL
6 NULL NULL
7 NULL NULL

9 NULL R
1571 9 NULL

10 NULL NULL
11 NULL NULL

12 NULL R
1572 12 R
1573 1572 R
1574 1573 NULL

Thx in Advance
Venu

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-06-28 : 19:04:13

Refer:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67059

Nathan Skerl

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-29 : 00:25:21

[code]
-- Declare the table for testing
declare @table table
(
TicketID int,
OldTicketID int,
CancelReplaceStatus char(1)
)
-- Create the sample testing data
insert into @table
select 2, NULL, NULL union all
select 3, NULL, NULL union all
select 4, NULL, NULL union all
select 5, NULL, NULL union all
select 6, NULL, NULL union all
select 7, NULL, NULL union all
select 9, NULL, 'R' union all
select 1571, 9, NULL union all
select 10, NULL, NULL union all
select 11, NULL, NULL union all
select 12, NULL, 'R' union all
select 1572, 12, 'R' union all
select 1573, 1572, 'R' union all
select 1574, 1573, NULL

-- Query
declare @TicketID table
(
seq int identity(1,1),
TicketID int
)

declare @ticket int
select @ticket = 1574

insert into @TicketID (TicketID) select @ticket where @ticket is not null

while (@ticket is not null)
begin
select @ticket = OldTicketID
from @table t
where t.TicketID = @ticket

insert into @TicketID (TicketID) select @ticket where @ticket is not null
end
select * from @TicketID order by seq

/* RESULT
seq TicketID
----------- -----------
1 1574
2 1573
3 1572
4 12

(4 row(s) affected)
*/[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources