Find the Changed order disguised as a Re-Order

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 the Changed order disguised as a Re-Order

Author

Topic

LaurieCox

158 Posts

Posted - 2014-08-13 : 14:26:20

Bear with me, because this is complicated. I am working with data from a prescription system and am trying to find bad data. I just discovered a prescription order that was marked as re-order but was inserted into the database as a change order. I want to find out if there are any other orders like this.

So a script consists of (each in a separate table):

A Medications Record (ClientId, Medication Name, Initial order date, first prescriber, etc).

An Instructions Record (dosage, strength, frequency, etc.)

Script Order (Start Date, End Date, current prescriber, event type), where event type is new, reorder or change order).

A script order can include multiple Instructions (i.e. the prescriber can order different dosages for a given drug in one order). I call these multi-dosage orders and there is a one-to-many relationship between a Script record and Instruction Records.

When a reorder happens a new Script Order record is created that points to existing Instruction records.

When a change order happens a new Script Order is created that points to new Instruction records.

So here is a good Med Order chain:


ClientId    MedId       MedicationName  MedOrder ScriptId    InstructionId EventType
----------- ----------- --------------- -------- ----------- ------------- ----------
53842       45367       Lamictal        1        200799      90505         New

53842       45367       Lamictal        2        202680      91637         Changed

53842       45367       Lamictal        3        207914      95644         Changed
53842       45367       Lamictal        3        207914      95643         Changed

53842       45367       Lamictal        4        211840      95644         Re-Ordered
53842       45367       Lamictal        4        211840      95643         Re-Ordered

53842       45367       Lamictal        5        214485      100691        Changed
53842       45367       Lamictal        5        214485      100692        Changed

53842       45367       Lamictal        6        220393      100691        Re-Ordered
53842       45367       Lamictal        6        220393      100692        Re-Ordered

53842       45367       Lamictal        7        226852      100692        Re-Ordered
53842       45367       Lamictal        7        226852      100691        Re-Ordered

So you can see when there is a re-order the re-order references the same Instruction records as the preceding order. When there is a change order the change order references new Instruction records.

Here is a partial order chain (the whole chain is in the test data) where a Script got marked as a reorder but the InstructionIds changed:


ClientId    MedId       MedicationName  MedOrder ScriptId    InstructionId EventType
----------- ----------- --------------- -------- ----------- ------------- ----------
64428       16289       Ritalin         18       213284      99791         Changed
64428       16289       Ritalin         18       213284      99789         Changed

64428       16289       Ritalin         19       217423      99791         Re-Ordered
64428       16289       Ritalin         19       217423      99789         Re-Ordered

64428       16289       Ritalin         20       217424      102828        Re-Ordered
64428       16289       Ritalin         20       217424      102827        Re-Ordered

If you look at the bolded InstructionIds from MedOrder 20 you will see that they are changed from the InstructionIds of MedOrder 19.

So how do I write script to catch other instances of this?

My expected output would be all rows that are marked as reorder but should have been marked as Changed. So from the test data below, I would expect this:


ClientId    MedId       MedicationName  MedOrder ScriptId    InstructionId EventType
----------- ----------- --------------- -------- ----------- ------------- ----------
64428       16289       Ritalin         10       200197      90076         Re-Ordered
64428       16289       Ritalin         14       203711      92381         Re-Ordered
64428       16289       Ritalin         20       217424      102828        Re-Ordered
64428       16289       Ritalin         20       217424      102827        Re-Ordered
64428       16289       Ritalin         22       221410      105638        Re-Ordered
64428       16289       Ritalin         22       221410      105639        Re-Ordered
64428       16289       Ritalin         27       234829      115629        Re-Ordered
64428       16289       Ritalin         27       234829      115630        Re-Ordered
64428       16289       Ritalin         32       248994      125302        Re-Ordered
64428       16289       Ritalin         32       248994      125303        Re-Ordered

Thanks in advance,

Laurie

Test data:
I have already written script to pull the data from the tables into an order chain, so my test data is the result of that script.


CREATE TABLE kt_TestData(
	ClientId int NOT NULL,
	MedId int NOT NULL,
	MedicationName varchar(15) NULL,
	MedOrder int NULL,
	ScriptId int NULL,
	InstructionId int NULL,
	EventType varchar(10) NULL
)
insert into kt_TestData
select 53842, 45367, 'Lamictal',1, 200799, 90505, 'New' union all
select 53842, 45367, 'Lamictal',2, 202680, 91637, 'Changed' union all
select 53842, 45367, 'Lamictal',3, 207914, 95644, 'Changed' union all
select 53842, 45367, 'Lamictal',3, 207914, 95643, 'Changed' union all
select 53842, 45367, 'Lamictal',4, 211840, 95644, 'Re-Ordered' union all
select 53842, 45367, 'Lamictal',4, 211840, 95643, 'Re-Ordered' union all
select 53842, 45367, 'Lamictal',5, 214485, 100691, 'Changed' union all
select 53842, 45367, 'Lamictal',5, 214485, 100692, 'Changed' union all
select 53842, 45367, 'Lamictal',6, 220393, 100691, 'Re-Ordered' union all
select 53842, 45367, 'Lamictal',6, 220393, 100692, 'Re-Ordered' union all
select 53842, 45367, 'Lamictal',7, 226852, 100692, 'Re-Ordered' union all
select 53842, 45367, 'Lamictal',7, 226852, 100691, 'Re-Ordered' union all
select 53842, 45367, 'Lamictal',8, 230707, 100692, 'Re-Ordered' union all
select 53842, 45367, 'Lamictal',8, 230707, 100691, 'Re-Ordered' union all
select 53842, 45367, 'Lamictal',9, 238309, 100691, 'Re-Ordered' union all
select 53842, 45367, 'Lamictal',9, 238309, 100692, 'Re-Ordered' union all
select 53842, 45367, 'Lamictal',10, 248845, 100691, 'Re-Ordered' union all
select 53842, 45367, 'Lamictal',10, 248845, 100692, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',2, 87057, 39195, 'New' union all
select 64428, 16289, 'Ritalin',3, 161947, 39195, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',4, 164818, 39195, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',5, 166415, 72892, 'Changed' union all
select 64428, 16289, 'Ritalin',6, 192414, 72892, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',7, 197086, 72892, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',8, 198762, 72892, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',9, 200196, 72892, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',10, 200197, 90076, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',11, 200201, 90078, 'Changed' union all
select 64428, 16289, 'Ritalin',12, 200202, 90079, 'Changed' union all
select 64428, 16289, 'Ritalin',13, 203710, 90079, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',14, 203711, 92381, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',15, 210264, 97362, 'Changed' union all
select 64428, 16289, 'Ritalin',16, 210265, 97363, 'Changed' union all
select 64428, 16289, 'Ritalin',17, 213283, 99790, 'Changed' union all
select 64428, 16289, 'Ritalin',17, 213283, 99788, 'Changed' union all
select 64428, 16289, 'Ritalin',18, 213284, 99791, 'Changed' union all
select 64428, 16289, 'Ritalin',18, 213284, 99789, 'Changed' union all
select 64428, 16289, 'Ritalin',19, 217423, 99791, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',19, 217423, 99789, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',20, 217424, 102828, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',20, 217424, 102827, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',21, 221409, 102828, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',21, 221409, 102827, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',22, 221410, 105638, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',22, 221410, 105639, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',23, 227298, 105638, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',23, 227298, 105639, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',24, 230722, 112729, 'Changed' union all
select 64428, 16289, 'Ritalin',24, 230722, 112730, 'Changed' union all
select 64428, 16289, 'Ritalin',25, 230723, 112732, 'Changed' union all
select 64428, 16289, 'Ritalin',25, 230723, 112731, 'Changed' union all
select 64428, 16289, 'Ritalin',26, 234828, 112732, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',26, 234828, 112731, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',27, 234829, 115629, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',27, 234829, 115630, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',28, 238615, 115630, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',28, 238615, 115629, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',29, 243024, 115630, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',29, 243024, 115629, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',30, 245931, 115629, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',30, 245931, 115630, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',31, 248993, 115629, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',31, 248993, 115630, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',32, 248994, 125302, 'Re-Ordered' union all
select 64428, 16289, 'Ritalin',32, 248994, 125303, 'Re-Ordered'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-13 : 16:48:58

[code]-- SwePeso
SELECT p.ClientID,
p.MedID,
n.MedOrder
FROM @Sample AS p
INNER JOIN @Sample AS n ON n.ClientID = p.ClientID
AND n.MedID = p.MedID
AND n.MedOrder = p.MedOrder + 1
GROUP BY p.ClientID,
p.MedID,
n.MedOrder
HAVING MAX(CASE WHEN 'Changed' IN (p.EventType, n.EventType) THEN 1 ELSE 0 END) = 0
AND SUM(CASE WHEN p.InstructionID = n.InstructionID THEN 1 ELSE 0 END) = 0
ORDER BY p.ClientID,
p.MedID,
n.MedOrder;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

LaurieCox

158 Posts

Posted - 2014-08-14 : 08:47:35

Hi SwePeso,

That looks to be perfect. I ran it against on production data and found 986 mislabeled orders out of 332237 (which I guess isn't too bad). I spot checked the results and they all were correctly bad.

Now I am going to have to go over your script and see how it works.

My problem with writing it myself was I didn't know how to match back with multiple rows per order. I had the join clause figured out (n.MedOrder = p.MedOrder + 1) but couldn't figure out how to match/not match on the InstructionId. I knew I couldn't do n.InsturctionId <> p.InstructionId (because of the multi dosage orders).

So that is what your sum clause is doing. It returns a 0 if none of the rows of a multi dosage order match any of the rows of the preceding order but some positive number if there is a match. And then the max clause just removes Changed orders from consideration.

So I have added another tidbit to my sql knowledge, the case statement within an aggregate.

Anyway thanks for your help,

Laurie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-14 : 09:21:24

You are welcome. Perhaps you will need additional checks but this one will take care of the obvious ones.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources