DDL and DML for the InputData table and DDL for the OutputData table at end post.
I want to insert data from InputData into OutputData with one tricky rule.
Description of InputData
The input data consists of groups (denoted by GroupId) with a series of Events (Active, Change and Reorder). The order of the events is denoted by the OrderNumber column. A given event for a given group can have multiple records (as there can be multiple dosages for an order). The number of records for each event is in the NumDosages column. So (from the test data) we have this data for group 10641:
InputId GroupID OrderNumber NumDosages event Dosage Schedule
-------- -------- ----------- ----------- ------- ------------------------------ -----------------
1 10641 1 2 Active - 0.5 mg, TAB, PO (2)ea QHS Bedtime
2 10641 1 2 Active - 0.5 mg, TAB, PO (1)ea BID Twice a day
3 10641 2 1 Change - 1 mg, TAB, PO (1)ea TID Three times a day
4 10641 3 1 Reorder - 1 mg, TAB, PO (1)ea TID Three times a day
5 10641 4 1 Reorder - 1 mg, TAB, PO (1)ea TID Three times a day
6 10641 5 2 Change - 0.5 mg, TAB, PO (1)ea QAM Morning
7 10641 5 2 Change - 1 mg, TAB, PO (1)ea QHS Bedtime
The first event (OrderNumber = 1) and last event (OrderNumber = 5) have two Dosages each (NumDosages = 2) so there are two rows for each event.
The second, third and fourth events (OrderNumber = 2, 3 and 4) have one Dosage each (NumDosages = 1) so there is one row for each of these events.
Rules for Insertion
- Each record in InputData will be inserted into OutputData with the column Disabled set to null.
- (Here's the tricky one). If the immediately previous event (within a given group) had more dosages then the given event, insert another copy of the previous event's rows with the column Disabled set to 'Y'
So for the given group my desired results would be that the following rows are inserted into OutputData:
InputId GroupID OrderNumber NumDosages event Dosage Schedule Disabled
-------- -------- ----------- ----------- ------- ------------------------------ ------------------ --------
1 10641 1 2 Active - 0.5 mg, TAB, PO (2)ea QHS Bedtime NULL
2 10641 1 2 Active - 0.5 mg, TAB, PO (1)ea BID Twice a day NULL
1 10641 1 2 Active - 0.5 mg, TAB, PO (2)ea QHS Bedtime Y
2 10641 1 2 Active - 0.5 mg, TAB, PO (1)ea BID Twice a day Y
3 10641 2 1 Change - 1 mg, TAB, PO (1)ea TID Three times a day NULL
4 10641 3 1 Reorder - 1 mg, TAB, PO (1)ea TID Three times a day NULL
5 10641 4 1 Reorder - 1 mg, TAB, PO (1)ea TID Three times a day NULL
6 10641 5 2 Change - 0.5 mg, TAB, PO (1)ea QAM Morning NULL
7 10641 5 2 Change - 1 mg, TAB, PO (1)ea QHS Bedtime NULL
The bolded rows were inserted because the NumDosages for the first event (OrderNumber = 1) is greater than the NumDosages for the second event (OrderNumber = 2).
My work so far
I am thinking it will be two inserts statements. The first one is easy:
Insert into OutputData (InputId,GroupID,OrderNumber,NumDosages,event,Dosage,Schedule)
select * from InputData
Then there is the tricky part ????
table ddl and dml for InputData
CREATE TABLE OutputData(
InputId int NOT NULL,
GroupID int NOT NULL,
OrderNumber int NOT NULL,
NumDosages int NOT NULL,
event nvarchar(7) NOT NULL,
Dosage nvarchar(35) NOT NULL,
Schedule nvarchar(20) NOT NULL,
Disabled nvarchar(1) NULL
)
CREATE TABLE InputData(
InputId int NOT NULL,
GroupID int NOT NULL,
OrderNumber int NOT NULL,
NumDosages int NOT NULL,
event nvarchar(7) NOT NULL,
Dosage nvarchar(35) NOT NULL,
Schedule nvarchar(20) NOT NULL
)
Insert into InputData (InputId,GroupID,OrderNumber,NumDosages,event,Dosage,Schedule)
SELECT 1,10641,1,2,'Active',' - 0.5 mg, TAB, PO (2)ea QHS','Bedtime' UNION ALL
SELECT 2,10641,1,2,'Active',' - 0.5 mg, TAB, PO (1)ea BID','Twice a day' UNION ALL
SELECT 3,10641,2,1,'Change',' - 1 mg, TAB, PO (1)ea TID','Three times a day' UNION ALL
SELECT 4,10641,3,1,'Reorder',' - 1 mg, TAB, PO (1)ea TID','Three times a day' UNION ALL
SELECT 5,10641,4,1,'Reorder',' - 1 mg, TAB, PO (1)ea TID','Three times a day' UNION ALL
SELECT 6,10641,5,2,'Change',' - 0.5 mg, TAB, PO (1)ea QAM','Morning' UNION ALL
SELECT 7,10641,5,2,'Change',' - 1 mg, TAB, PO (1)ea QHS','Bedtime' UNION ALL
SELECT 8,14089,1,1,'Active',' - 0.5 mg, TAB, PO (1)ea BID','Twice a day' UNION ALL
SELECT 9,14089,2,3,'Change',' - 0.5 mg, TAB, PO (1)ea QAM','Morning' UNION ALL
SELECT 10,14089,2,3,'Change',' - 0.5 mg, TAB, PO (1)ea Qnoon','Daily at Noon' UNION ALL
SELECT 11,14089,2,3,'Change',' - 0.5 mg, TAB, PO (1)ea QHS','Bedtime' UNION ALL
SELECT 12,14089,3,1,'Change',' - 0.5 mg, TAB, PO (1)ea BID','Twice a day' UNION ALL
SELECT 13,14089,4,1,'Reorder',' - 0.5 mg, TAB, PO (1)ea BID','Twice a day' UNION ALL
SELECT 14,14089,5,2,'Change',' - 0.25 mg, TAB, PO (1)ea Qnoon','Daily at Noon' UNION ALL
SELECT 15,14089,5,2,'Change',' - 0.5 mg, TAB, PO (1)ea BID','Twice a day' UNION ALL
SELECT 16,14089,6,2,'Change',' - 0.25 mg, TAB, PO (1)ea Qnoon','Daily at Noon' UNION ALL
SELECT 17,14089,6,2,'Change',' - 0.5 mg, TAB, PO (1)ea BID','Twice a day' UNION ALL
SELECT 18,14089,7,2,'Reorder',' - 0.25 mg, TAB, PO (1)ea Qnoon','Daily at Noon' UNION ALL
SELECT 19,14089,7,2,'Reorder',' - 0.5 mg, TAB, PO (1)ea BID','Twice a day' UNION ALL
SELECT 20,14089,8,2,'Reorder',' - 0.25 mg, TAB, PO (1)ea Qnoon','Daily at Noon' UNION ALL
SELECT 21,14089,8,2,'Reorder',' - 0.5 mg, TAB, PO (1)ea BID','Twice a day' UNION ALL
SELECT 22,14089,9,3,'Change',' - 0.25 mg, TAB, PO (1)ea QAM','Morning' UNION ALL
SELECT 23,14089,9,3,'Change',' - 0.25 mg, TAB, PO (1)ea Qnoon','Daily at Noon' UNION ALL
SELECT 24,14089,9,3,'Change',' - 0.5 mg, TAB, PO (1)ea BID','Twice a day' UNION ALL
SELECT 25,15134,1,2,'Active',' - 25 mg, TAB, PO (1)ea QAM','Morning' UNION ALL
SELECT 26,15134,1,2,'Active',' - 25 mg, TAB, PO (1)ea QHS','Bedtime' UNION ALL
SELECT 27,15134,2,1,'Change',' - 25 mg, TAB, PO (1)ea TID','Three times a day' UNION ALL
SELECT 28,15134,3,3,'Change',' - 25 mg, TAB, PO (2)ea QAM','Morning' UNION ALL
SELECT 29,15134,3,3,'Change',' - 25 mg, TAB, PO (2)ea Qnoon','Daily at Noon' UNION ALL
SELECT 30,15134,3,3,'Change',' - 25 mg, TAB, PO (1)ea QHS','Bedtime' UNION ALL
SELECT 31,18157,1,2,'Active',' - 0.5 mg, TAB, PO (1)ea QAM','Morning' UNION ALL
SELECT 32,18157,1,2,'Active',' - 1 mg, TAB, PO (1)ea QHS','Bedtime' UNION ALL
SELECT 33,18157,2,1,'Change',' - 1 mg, TAB, PO (1)ea BID','Twice a day' UNION ALL
SELECT 34,18157,3,2,'Change',' - 0.5 mg, TAB, PO (1)ea QAM','Morning' UNION ALL
SELECT 35,18157,3,2,'Change',' - 1 mg, TAB, PO (1)ea QHS','Bedtime' UNION ALL
SELECT 36,18157,4,1,'Change',' - 1 mg, TAB, PO (1)ea BID','Twice a day'
Expected Results from all test data (if I got it right)
InputId GroupID OrderNumber NumDosages event Dosage Schedule Disabled
-------- -------- ----------- ----------- ------- -------------------------------- -------------------- --------
1 10641 1 2 Active - 0.5 mg, TAB, PO (2)ea QHS Bedtime NULL
2 10641 1 2 Active - 0.5 mg, TAB, PO (1)ea BID Twice a day NULL
1 10641 1 2 Active - 0.5 mg, TAB, PO (2)ea QHS Bedtime Y
2 10641 1 2 Active - 0.5 mg, TAB, PO (1)ea BID Twice a day Y
3 10641 2 1 Change - 1 mg, TAB, PO (1)ea TID Three times a day NULL
4 10641 3 1 Reorder - 1 mg, TAB, PO (1)ea TID Three times a day NULL
5 10641 4 1 Reorder - 1 mg, TAB, PO (1)ea TID Three times a day NULL
6 10641 5 2 Change - 0.5 mg, TAB, PO (1)ea QAM Morning NULL
7 10641 5 2 Change - 1 mg, TAB, PO (1)ea QHS Bedtime NULL
8 14089 1 1 Active - 0.5 mg, TAB, PO (1)ea BID Twice a day NULL
9 14089 2 3 Change - 0.5 mg, TAB, PO (1)ea QAM Morning NULL
10 14089 2 3 Change - 0.5 mg, TAB, PO (1)ea Qnoon Daily at Noon NULL
11 14089 2 3 Change - 0.5 mg, TAB, PO (1)ea QHS Bedtime NULL
9 14089 2 3 Change - 0.5 mg, TAB, PO (1)ea QAM Morning Y
10 14089 2 3 Change - 0.5 mg, TAB, PO (1)ea Qnoon Daily at Noon Y
11 14089 2 3 Change - 0.5 mg, TAB, PO (1)ea QHS Bedtime Y
12 14089 3 1 Change - 0.5 mg, TAB, PO (1)ea BID Twice a day NULL
13 14089 4 1 Reorder - 0.5 mg, TAB, PO (1)ea BID Twice a day NULL
14 14089 5 2 Change - 0.25 mg, TAB, PO (1)ea Qnoon Daily at Noon NULL
15 14089 5 2 Change - 0.5 mg, TAB, PO (1)ea BID Twice a day NULL
16 14089 6 2 Change - 0.25 mg, TAB, PO (1)ea Qnoon Daily at Noon NULL
17 14089 6 2 Change - 0.5 mg, TAB, PO (1)ea BID Twice a day NULL
18 14089 7 2 Reorder - 0.25 mg, TAB, PO (1)ea Qnoon Daily at Noon NULL
19 14089 7 2 Reorder - 0.5 mg, TAB, PO (1)ea BID Twice a day NULL
20 14089 8 2 Reorder - 0.25 mg, TAB, PO (1)ea Qnoon Daily at Noon NULL
21 14089 8 2 Reorder - 0.5 mg, TAB, PO (1)ea BID Twice a day NULL
22 14089 9 3 Change - 0.25 mg, TAB, PO (1)ea QAM Morning NULL
23 14089 9 3 Change - 0.25 mg, TAB, PO (1)ea Qnoon Daily at Noon NULL
24 14089 9 3 Change - 0.5 mg, TAB, PO (1)ea BID Twice a day NULL
25 15134 1 2 Active - 25 mg, TAB, PO (1)ea QAM Morning NULL
26 15134 1 2 Active - 25 mg, TAB, PO (1)ea QHS Bedtime NULL
25 15134 1 2 Active - 25 mg, TAB, PO (1)ea QAM Morning Y
26 15134 1 2 Active - 25 mg, TAB, PO (1)ea QHS Bedtime Y
27 15134 2 1 Change - 25 mg, TAB, PO (1)ea TID Three times a day NULL
28 15134 3 3 Change - 25 mg, TAB, PO (2)ea QAM Morning NULL
29 15134 3 3 Change - 25 mg, TAB, PO (2)ea Qnoon Daily at Noon NULL
30 15134 3 3 Change - 25 mg, TAB, PO (1)ea QHS Bedtime NULL
31 18157 1 2 Active - 0.5 mg, TAB, PO (1)ea QAM Morning NULL
32 18157 1 2 Active - 1 mg, TAB, PO (1)ea QHS Bedtime NULL
31 18157 1 2 Active - 0.5 mg, TAB, PO (1)ea QAM Morning Y
32 18157 1 2 Active - 1 mg, TAB, PO (1)ea QHS Bedtime Y
33 18157 2 1 Change - 1 mg, TAB, PO (1)ea BID Twice a day NULL
34 18157 3 2 Change - 0.5 mg, TAB, PO (1)ea QAM Morning NULL
33 18157 3 2 Change - 1 mg, TAB, PO (1)ea QHS Bedtime NULL
34 18157 3 2 Change - 0.5 mg, TAB, PO (1)ea QAM Morning Y
35 18157 3 2 Change - 1 mg, TAB, PO (1)ea QHS Bedtime Y
36 18157 4 1 Change - 1 mg, TAB, PO (1)ea BID Twice a day NULL
Note: all records with Disabled = Y would be inserted via rule 2.
Insert Help (Can't come up with a better title)
Posted - 05/09/2012 : 13:39:13
