Find record based on data from following row

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)

Find record based on data from following row

Author

Topic

LaurieCox

158 Posts

Posted - 2012-03-28 : 14:22:14

So I have this table (dml for sample data at end of post):


CREATE TABLE #RxHistory (
       rx_dosage_id int,
       member_id int,
       Drug_Name varchar(50),
       orderhistory_id int,
       ordercontent_id int,
       event varchar(10),
       NDC_Code varchar(11),
       NumDosages int)

If I order the data by member_id, Drug_Name, orderhistory_id, ordercontent_id


SELECT *
  FROM #RxHistory
 order by member_id,Drug_Name,orderhistory_id,ordercontent_id

I want to find all rows that meet the following conditions:


(I will write the condition in pseudo sql)
where (event = 'Discontinu' 
  and  NumDosages = 1 
  -- here's the tricky part:
  and  the event on the following row (based on the sort above) <> 'Active')
   or (event = 'Discontinu'
  and  NumDosages > 1
  -- more tricky stuff:
  and  the event on the following row (based on the sort above) not in ('Active','Discontinu')

So if you look at the sample data sorted accordingly you will see that only the rows in red meet the criteria:


rx_dosage_id member_id   Drug_Name   orderhistory_id ordercontent_id event      NDC_Code    NumDosages
------------ ----------- ----------- --------------- --------------- ---------- ----------- -----------
14044408     1059524     Haldol      7142769         13911534        Active     00045024560 1
14458708     1059524     Haldol      7366029         14330770        Change     00045024560 1
15374230     1059524     Haldol      7859455         15258370        Active     00045024560 1
14458708     1059524     Haldol      7859455         15258371        Discontinu 00045024560 1
15781433     1059524     Haldol      8077280         15670943        Reorder    00045024560 1
16121328     1059524     Haldol      8256696         16014500        Reorder    00045024560 1
3660192      1059528     Prozac      1690074         3507662         Active     00093719856 1
3849370      1059528     Prozac      1782997         3692445         Reorder    00093719856 1
4005000      1059528     Prozac      1858979         3844187         Change     00093719856 1
4290361      1059528     Prozac      1997468         4122340         Reorder    00093719856 1
4290361      1059528     Prozac      2053424         4232982         Discontinu 00093719856 1
3625208      1059530     Abilify     1672886         3473363         Active     12280001415 1
3758813      1059530     Abilify     1738706         3603958         Active     59148001013 1
3625208      1059530     Abilify     1783700         3693778         Discontinu 12280001415 1
3850748      1059530     Abilify     1783700         3693784         Reorder    59148001013 1
3964912      1059530     Abilify     1839665         3805178         Reorder    59148001013 1
4115746      1059530     Abilify     2009596         4146569         Discontinu 59148001013 1
9716528      1059534     Chantix     4834297         9546212         Active     00069046856 1
9716529      1059534     Chantix     4834297         9546213         Active     00069046856 1
9716530      1059534     Chantix     4834297         9546214         Active     00069046956 1
9716528      1059534     Chantix     5580243         10960689        Discontinu 00069046856 1
8085314      1059540     Geodon      3968063         7905515         Active     00049397041 2
8085315      1059540     Geodon      3968063         7905515         Active     00049397041 2
8455544      1059540     Geodon      4164735         8278463         Active     00049397041 2
8455545      1059540     Geodon      4164735         8278463         Active     00049397041 2
8085314      1059540     Geodon      4323743         8578948         Discontinu 00049397041 2
8085315      1059540     Geodon      4323743         8578948         Discontinu 00049397041 2
8754187      1059540     Geodon      4323747         8578955         Change     00049397041 2
8754188      1059540     Geodon      4323747         8578955         Change     00049397041 2
8754187      1059540     Geodon      4419493         8760003         Discontinu 00049397041 2
8754188      1059540     Geodon      4419493         8760003         Discontinu 00049397041 2
7144661      1059557     Doxepin     3468462         6956705         Active     00049535066 1
7144661      1059557     Doxepin     4153504         8257601         Discontinu 00049535066 1
8434879      1059557     Doxepin     4153504         8257604         Active     00049536066 1
8434879      1059557     Doxepin     4462011         8840779         Discontinu 00049536066 1

So I need to solve the "tricky stuff" from my pseudo sql above.

Thanks,

Laurie

Sample data:


INSERT INTO #RxHistory
SELECT 14044408,1059524,'Haldol',7142769,13911534,'Active','00045024560',1 UNION ALL
SELECT 14458708,1059524,'Haldol',7366029,14330770,'Change','00045024560',1 UNION ALL
SELECT 15374230,1059524,'Haldol',7859455,15258370,'Active','00045024560',1 UNION ALL
SELECT 14458708,1059524,'Haldol',7859455,15258371,'Discontinu','00045024560',1 UNION ALL
SELECT 15781433,1059524,'Haldol',8077280,15670943,'Reorder','00045024560',1 UNION ALL
SELECT 16121328,1059524,'Haldol',8256696,16014500,'Reorder','00045024560',1 UNION ALL
SELECT 3660192,1059528,'Prozac',1690074,3507662,'Active','00093719856',1 UNION ALL
SELECT 3849370,1059528,'Prozac',1782997,3692445,'Reorder','00093719856',1 UNION ALL
SELECT 4005000,1059528,'Prozac',1858979,3844187,'Change','00093719856',1 UNION ALL
SELECT 4290361,1059528,'Prozac',1997468,4122340,'Reorder','00093719856',1 UNION ALL
SELECT 4290361,1059528,'Prozac',2053424,4232982,'Discontinu','00093719856',1 UNION ALL
SELECT 3625208,1059530,'Abilify',1672886,3473363,'Active','12280001415',1 UNION ALL
SELECT 3758813,1059530,'Abilify',1738706,3603958,'Active','59148001013',1 UNION ALL
SELECT 3625208,1059530,'Abilify',1783700,3693778,'Discontinu','12280001415',1 UNION ALL
SELECT 3850748,1059530,'Abilify',1783700,3693784,'Reorder','59148001013',1 UNION ALL
SELECT 3964912,1059530,'Abilify',1839665,3805178,'Reorder','59148001013',1 UNION ALL
SELECT 4115746,1059530,'Abilify',2009596,4146569,'Discontinu','59148001013',1 UNION ALL
SELECT 9716528,1059534,'Chantix',4834297,9546212,'Active','00069046856',1 UNION ALL
SELECT 9716529,1059534,'Chantix',4834297,9546213,'Active','00069046856',1 UNION ALL
SELECT 9716530,1059534,'Chantix',4834297,9546214,'Active','00069046956',1 UNION ALL
SELECT 9716528,1059534,'Chantix',5580243,10960689,'Discontinu','00069046856',1 UNION ALL
SELECT 8085314,1059540,'Geodon',3968063,7905515,'Active','00049397041',2 UNION ALL
SELECT 8085315,1059540,'Geodon',3968063,7905515,'Active','00049397041',2 UNION ALL
SELECT 8455544,1059540,'Geodon',4164735,8278463,'Active','00049397041',2 UNION ALL
SELECT 8455545,1059540,'Geodon',4164735,8278463,'Active','00049397041',2 UNION ALL
SELECT 8085314,1059540,'Geodon',4323743,8578948,'Discontinu','00049397041',2 UNION ALL
SELECT 8085315,1059540,'Geodon',4323743,8578948,'Discontinu','00049397041',2 UNION ALL
SELECT 8754187,1059540,'Geodon',4323747,8578955,'Change','00049397041',2 UNION ALL
SELECT 8754188,1059540,'Geodon',4323747,8578955,'Change','00049397041',2 UNION ALL
SELECT 8754187,1059540,'Geodon',4419493,8760003,'Discontinu','00049397041',2 UNION ALL
SELECT 8754188,1059540,'Geodon',4419493,8760003,'Discontinu','00049397041',2 UNION ALL
SELECT 7144661,1059557,'Doxepin' ,3468462,6956705,'Active','00049535066',1 UNION ALL
SELECT 7144661,1059557,'Doxepin',4153504,8257601,'Discontinu','00049535066',1 UNION ALL
SELECT 8434879,1059557,'Doxepin' ,4153504,8257604,'Active','00049536066',1 UNION ALL
SELECT 8434879,1059557,'Doxepin' ,4462011,8840779,'Discontinu','00049536066',1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-28 : 14:49:52

[code]
;With RxTemp
AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY member_id,Drug_Name ORDER BY orderhistory_id,ordercontent_id) AS Seq
FROM #RxHistory
)

SELECT t1.*
FROM RxTemp t1
INNER JOIN RxTemp t2
ON t1.member_id = t2.member_id
AND t1.Drug_Name = t2.Drug_Name
AND t1.Seq+1 = t2.Seq
WHERE t1.event = 'Discontinu'
and ((t1.NumDosages > 1 AND t2.event not in ('Active','Discontinu'))
or (t1.NumDosages = 1 AND t2.event <> 'Active'))
[/code]

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

LaurieCox

158 Posts

Posted - 2012-03-28 : 15:06:36

Hi visakh16,

Thank you. Of course now I am going to have to go and learn about common table expressions so I can understand how the thing works and apply it to other problems as well.

Anyway you gave me something more to study (always a good thing).

Thanks again,

Laurie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-28 : 15:08:05

no problem
you're welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources