Insert Help (Can't come up with a better title)

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)

Insert Help (Can't come up with a better title)

Author

Topic

LaurieCox

158 Posts

Posted - 2012-05-09 : 13:39:13

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.

LaurieCox

158 Posts

Posted - 2012-05-09 : 13:54:08

Oh hell, I solved it!!!!!


select *, 'Y'
  from InputData i1 
 where exists (select 1
                 from InputData i2
                where i1.GroupID = i2.GroupID
                  and i1.OrderNumber = i2.OrderNumber - 1
                  and i1.NumDosages > i2.NumDosages)

Not nearly as tricky as I thought it was.

Critique of my solution or suggestions for a "better" solution welcome and appreciated

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources