SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LaurieCox
Posting Yak Master

USA
149 Posts

Posted - 06/12/2012 :  10:15:21  Show Profile  Reply with Quote
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

Edited by - LaurieCox on 06/12/2012 11:58:58

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/12/2012 :  16:11:35  Show Profile  Reply with Quote
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
Posting Yak Master

USA
149 Posts

Posted - 06/13/2012 :  09:31:52  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/13/2012 :  10:36:50  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000