Table, test data and expected results at end of post.
I have chains of data denoted by GroupId. Each chain starts with an Active Event.
Some Events are associated with a TitrationId and an associated Step value. These can be Active or Change Events. If it is an Active Event then each record for a given TitrationId is in a separate group.
Example:
Groupid OrderNumber titrationid Step event
----------- ----------- ----------- ------ ----------
128643 1 166722 0 Active
128643 2 NULL NULL Change
128643 3 NULL NULL Discontinu
167188 1 166722 1 Active
167188 2 NULL NULL Reorder
167188 3 NULL NULL Discontinu
In this example there are two groups which both start with the same Active Titration Event (166722). I want to set the column called GroupTitrationId to the GroupId of the Active Event with Step = 0 for all members of both groups.
Example Results:
Groupid OrderNumber titrationid Step event GroupTitirationId
----------- ----------- ----------- ------ ---------- -----------------
128643 1 166722 0 Active 128643
128643 2 NULL NULL Change 128643
128643 3 NULL NULL Discontinu 128643
167188 1 166722 1 Active 128643
167188 2 NULL NULL Reorder 128643
167188 3 NULL NULL Discontinu 128643
Thanks,
Laurie
Table structure and data:
CREATE TABLE TestData(
GroupId int NULL,
OrderNumber int NULL,
titrationid int NULL,
step int NULL,
event varchar(10) NULL,
GroupTitrationId int NULL
)
insert into TestData (Groupid,OrderNumber,titrationid,Step,event)
select 522,1,NULL,NULL,'Active' union all
select 522,2,NULL,NULL,'Reorder' union all
select 522,3,NULL,NULL,'Change' union all
select 522,4,NULL,NULL,'Change' union all
select 522,5,NULL,NULL,'Discontinu' union all
select 8804,1,58557,0,'Active' union all
select 8804,2,59731,0,'Change' union all
select 8804,2,59731,1,'Change' union all
select 8804,3,59731,0,'Discontinu' union all
select 27105,1,53376,1,'Active' union all
select 47315,1,55810,0,'Active' union all
select 47315,2,NULL,NULL,'Change' union all
select 47315,2,NULL,NULL,'Change' union all
select 47315,3,NULL,NULL,'Reorder' union all
select 47315,3,NULL,NULL,'Reorder' union all
select 91257,1,58557,1,'Active' union all
select 128643,1,166722,0,'Active' union all
select 128643,2,NULL,NULL,'Change' union all
select 128643,3,NULL,NULL,'Discontinu' union all
select 147674,1,55810,1,'Active' union all
select 167188,1,166722,1,'Active' union all
select 167188,2,NULL,NULL,'Reorder' union all
select 167188,3,NULL,NULL,'Discontinu' union all
select 176831,1,53376,0,'Active' union all
select 176831,2,53376,0,'Discontinu' union all
select 189780,1,55810,2,'Active'
Expected Results:
select * from TestData
order by GroupTitrationId, OrderNumber,step
GroupId OrderNumber titrationid step event GroupTitrationId
----------- ----------- ----------- ----------- ---------- ----------------
522 1 NULL NULL Active 522
522 2 NULL NULL Reorder 522
522 3 NULL NULL Change 522
522 4 NULL NULL Change 522
522 5 NULL NULL Discontinu 522
8804 1 58557 0 Active 8804
91257 1 58557 1 Active 8804
8804 2 59731 0 Change 8804
8804 2 59731 1 Change 8804
8804 3 59731 0 Discontinu 8804
47315 1 55810 0 Active 47315
147674 1 55810 1 Active 47315
189780 1 55810 2 Active 47315
47315 2 NULL NULL Change 47315
47315 2 NULL NULL Change 47315
47315 3 NULL NULL Reorder 47315
47315 3 NULL NULL Reorder 47315
176831 1 53376 0 Active 176831
27105 1 53376 1 Active 176831
176831 2 53376 0 Discontinu 176831
Special cases:
GroupId: 522 does not have a Titration Event therefore the GroupTitrationId should be set 522
GroupId:8804 has a Change Titration Event this should have no bearing on the results. It is only Active Titration Events we care about.
Edited to remove my attempt at solving the problem as it was no where near correct or useful
Update Help
Posted - 06/12/2012 : 10:15:21
