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 Active128643 2 NULL NULL Change128643 3 NULL NULL Discontinu167188 1 166722 1 Active167188 2 NULL NULL Reorder167188 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 128643128643 2 NULL NULL Change 128643128643 3 NULL NULL Discontinu 128643167188 1 166722 1 Active 128643167188 2 NULL NULL Reorder 128643167188 3 NULL NULL Discontinu 128643
Thanks,LaurieTable 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 allselect 522,2,NULL,NULL,'Reorder' union allselect 522,3,NULL,NULL,'Change' union allselect 522,4,NULL,NULL,'Change' union allselect 522,5,NULL,NULL,'Discontinu' union allselect 8804,1,58557,0,'Active' union allselect 8804,2,59731,0,'Change' union allselect 8804,2,59731,1,'Change' union allselect 8804,3,59731,0,'Discontinu' union allselect 27105,1,53376,1,'Active' union allselect 47315,1,55810,0,'Active' union allselect 47315,2,NULL,NULL,'Change' union allselect 47315,2,NULL,NULL,'Change' union allselect 47315,3,NULL,NULL,'Reorder' union allselect 47315,3,NULL,NULL,'Reorder' union allselect 91257,1,58557,1,'Active' union allselect 128643,1,166722,0,'Active' union allselect 128643,2,NULL,NULL,'Change' union allselect 128643,3,NULL,NULL,'Discontinu' union allselect 147674,1,55810,1,'Active' union allselect 167188,1,166722,1,'Active' union allselect 167188,2,NULL,NULL,'Reorder' union allselect 167188,3,NULL,NULL,'Discontinu' union allselect 176831,1,53376,0,'Active' union allselect 176831,2,53376,0,'Discontinu' union allselect 189780,1,55810,2,'Active'
Expected Results:select * from TestDataorder by GroupTitrationId, OrderNumber,stepGroupId OrderNumber titrationid step event GroupTitrationId----------- ----------- ----------- ----------- ---------- ----------------522 1 NULL NULL Active 522522 2 NULL NULL Reorder 522522 3 NULL NULL Change 522522 4 NULL NULL Change 522522 5 NULL NULL Discontinu 5228804 1 58557 0 Active 880491257 1 58557 1 Active 88048804 2 59731 0 Change 88048804 2 59731 1 Change 88048804 3 59731 0 Discontinu 880447315 1 55810 0 Active 47315147674 1 55810 1 Active 47315189780 1 55810 2 Active 4731547315 2 NULL NULL Change 4731547315 2 NULL NULL Change 4731547315 3 NULL NULL Reorder 4731547315 3 NULL NULL Reorder 47315176831 1 53376 0 Active 17683127105 1 53376 1 Active 176831176831 2 53376 0 Discontinu 176831
Special cases:GroupId: 522 does not have a Titration Event therefore the GroupTitrationId should be set 522GroupId: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