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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update Help

Author  Topic 

LaurieCox

158 Posts

Posted - 2012-06-12 : 10:15:21
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 16:11:35
something ;like below based on sample data and output you posted




SELECT *,
CASE WHEN (t.step =0 AND t.event = 'Active') OR (t.titrationid IS NULL AND t.step IS NULL )THEN t.GroupId
WHEN t1.GroupId IS NOT NULL THEN t1.GroupId
ELSE t.GroupId
END AS GroupTitrationId
FROM table t
OUTER APPLY (SELECT GroupId
FROM table
WHERE titrationid = t.titrationid
AND step = 0
AND event = 'Active'
)t1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

LaurieCox

158 Posts

Posted - 2012-06-13 : 09:31:52
Hi visakh16

That looks good. I really have to start thinking about the apply clause (both cross and outer) when I am trying to solve problems. This is the third or fourth time it has figured in the solution to a problem I posted here.

Thank you so much for you help,

Laurie
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-13 : 10:36:50
quote:
Originally posted by AlgaeSea

Hi visakh16

That looks good. I really have to start thinking about the apply clause (both cross and outer) when I am trying to solve problems. This is the third or fourth time it has figured in the solution to a problem I posted here.

Thank you so much for you help,

Laurie


welcome

see some common scenarios where you can apply it

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -