Foreign key assign (part 2 ... More Complicated)

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)

Foreign key assign (part 2 ... More Complicated)

Author

Topic

LaurieCox

158 Posts

Posted - 2012-04-26 : 14:11:17

Backgroud

This is related to the Foreign key assignment with set based insert thread but with an added twist.

I have two more tables that I need to populate with data and the foreign key assignment for one of them is even more complicated as it has two foreign keys that need to be 'discovered' and one of them has a twist.

I am going to describe the problem using my (hopefully) temporary solution of adding new columns to the base tables, but I would like the actual solution to not have to add these columns (see comments in the ddl's below).

Note: A possible solution to the basic problem (of deriving the foreign keys without extra columns) was posted on the other thread while I was composing this post. I have not looked at it yet to see if it does solve that problem but it does not change the main thrust of this question (described in detail below).

I will use the same input table as I did in the original post (I have included the ddl and dml at the end of this post for convenience). Note: GroupId/OrderNumber defines a unique record in the InputData table.

The Problem
I have these four tables:


CREATE TABLE Medications(
	MedicationId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	StartDate datetime,
	EndDate datetime,
-- fields not in the actual table added for linking purposes	
	GroupId int
) 
-- Insert all records from InputData that have EventType = N (New).
insert into Medications (StartDate,EndDate,GroupId)
select StartDate
     , EndDate
     , GroupID
  from InputData
 where EventType = 'N'

CREATE TABLE Instructions(
	InstructionId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	MedicationId int foreign key references Medications(MedicationId) not null,
	StartDate datetime,
	EndDate datetime,
-- fields not in the actual table added for linking purposes	
	GroupId int,
	OrderNumber int
) 
-- Insert all records from InputData that have EventType = N or C (New, Change)
-- Link to Medications table by GroupId
insert into Instructions (MedicationId,StartDate,EndDate,GroupId,OrderNumber)
select m.MedicationId
     , i.StartDate
     , i.EndDate
     , i.GroupID
     , i.OrderNumber
  from InputData i join Medications m on i.GroupID = m.GroupId
 where i.EventType in ('N', 'C')

CREATE TABLE Scripts(
	ScriptId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	EventType char(1),
	StartDate datetime,
	EndDate datetime,
-- fields not in the actual table added for linking purposes	
	GroupId int,
	OrderNumber int
) 

--Insert all records in InputData into Scripts
insert into Scripts (EventType,StartDate,EndDate,GroupId,OrderNumber)
select EventType
     , StartDate
     , EndDate
     , GroupID
     , OrderNumber
  from InputData 

CREATE TABLE ScriptDrugs(
	ScriptDrugId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	ScriptId int foreign key references Scripts(ScriptId) not null,
	InstructionId int foreign key references Instructions(InstructionId) not null,
	StartDate datetime,
	EndDate datetime,
-- fields not in the actual table added for linking purposes	
	GroupId int,
	OrderNumber int
) 
--Insert all records into ScriptDrugs
-- Link to Scripts where GroupId and OrderNumber match
-- Link to Instructions Here's the tricky part and where I need help
-- see explanation below
insert into ScriptDrugs (ScriptId,InstructionId,StartDate,EndDate,GroupId,OrderNumber)
select s.ScriptId
       n.InstructionId
     , i.StartDate
     , i.EndDate
     , i.GroupID
     , i.OrderNumber
  from InputData i join Scripts s on i.GroupID = s.GroupId
                                 and i.OrderNumber = s.OrderNumber
                   join Instructions n on ??????????????????????????????

The Medications table is not needed for this particular problem but is included as it is needed to get data into the Instructions table from the test data.

So for the tricky part: The link between rows in the ScriptDrugs and Instructions tables.

So the Instructions table only has records for N(ew) and C(hanged) records from the InputData table.

The InputData table also has R(eorder) records. The New and Changed records will link back to the New Changed records in Instructions based on GroupId/OrderNumber.

So the tricky part is that the the Reorder records link to most recent New or Changed record based on OrderNumber within a given GroupId.

Examples might help. After running the scripts above to insert data into the Instructions and Scripts tables I get these records for GroupId 5834:


Instruction Table:
InstructionId MedicationId StartDate               EndDate                 GroupId     OrderNumber
------------- ------------ ----------------------- ----------------------- ----------- -----------
5             2            2010-01-19 00:00:00.000 2010-01-19 16:28:02.000 5834        1
7             2            2010-01-26 00:00:00.000 2010-02-25 00:00:00.000 5834        4
6             2            2010-03-02 00:00:00.000 2010-03-09 00:00:00.000 5834        5

Scripts Table:
ScriptId    EventType StartDate               EndDate                 GroupId     OrderNumber
----------- --------- ----------------------- ----------------------- ----------- -----------
5           N         2010-01-19 00:00:00.000 2010-01-19 16:28:02.000 5834        1
10          R         2010-01-19 16:28:02.000 2010-01-19 16:57:06.000 5834        2
4           R         2010-01-19 16:57:06.000 2010-01-26 00:00:00.000 5834        3
11          C         2010-01-26 00:00:00.000 2010-02-25 00:00:00.000 5834        4
6           C         2010-03-02 00:00:00.000 2010-03-09 00:00:00.000 5834        5

The insert into the ScriptDrugs table from the InputData table should generate these records:


ScriptDrugId ScriptId  InstructionId  GroupId  OrderNumber  (explanation for InstructionId value)
------------ --------  -------------  -------  -----------
<auto gen>   5         5              5834     1             new order points to inself
<auto gen>   10        5              5834     2             reeorder points to previous new order
<auto gen>   4         5              5834     3             reeorder points to previous new order
<auto gen>   11        7              5834     4             change order points to inself
<auto gen>   6         6              5834     5             change order points to inself

Again "previous new/change order" is determined by the OrderNumber column. So InstructionId of a Reorder is the record from the Instructions table that has the Max(OrderNumber) for the given GroupId that is < the OrderNumber of the record being inserted.

Results for GroupId 178248


Instruction Table:
InstructionId MedicationId StartDate               EndDate                 GroupId     OrderNumber
------------- ------------ ----------------------- ----------------------- ----------- -----------
3             1            2008-05-05 00:00:00.000 2008-07-10 00:00:00.000 178248      1
4             1            2008-07-10 00:00:00.000 2008-10-02 00:00:00.000 178248      2
1             1            2008-10-02 00:00:00.000 2008-12-23 14:00:47.000 178248      3
2             1            2009-03-16 00:00:00.000 2009-06-14 00:00:00.000 178248      5

Script Table:
ScriptId    EventType StartDate               EndDate                 GroupId     OrderNumber
----------- --------- ----------------------- ----------------------- ----------- -----------
3           N         2008-05-05 00:00:00.000 2008-07-10 00:00:00.000 178248      1
9           C         2008-07-10 00:00:00.000 2008-10-02 00:00:00.000 178248      2
1           C         2008-10-02 00:00:00.000 2008-12-23 14:00:47.000 178248      3
7           R         2008-12-23 14:00:47.000 2009-03-16 00:00:00.000 178248      4
2           C         2009-03-16 00:00:00.000 2009-06-14 00:00:00.000 178248      5
8           R         2009-06-17 14:19:08.000 2009-09-15 00:00:00.000 178248      6

ScriptDrug table expected results:
ScriptDrugId ScriptId  InstructionId  GroupId  OrderNumber  (explanation)
------------ --------  -------------  -------  -----------
<auto gen>   3         3              178248   1             new order points to inself
<auto gen>   9         4              178248   2             change order points to inself
<auto gen>   1         1              178248   3             change order points to inself
<auto gen>   7         1              178248   4             reeorder points to most recent change order
<auto gen>   2         2              178248   5             change order points to inself
<auto gen>   8         2              178248   6             reeorder points to most recent change order

So how do I write the join to the Instructions table to pull the correct foregn key value for the ScriptDrugs table?

Please let me know if I need to clarify this any.

Thanks,

Laurie

ddl/ddm for InputData:


CREATE TABLE InputData(
	[GroupID] [int] NULL,
	[OrderNumber] [int] NOT NULL,
	[EventType] [varchar](1) NULL,
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL
) 
select 178248, 3, 'C', '2008-10-02 00:00:00.000', '2008-12-23 14:00:47.000' union all
select 178248, 5, 'C', '2009-03-16 00:00:00.000', '2009-06-14 00:00:00.000' union all
select 178248, 1, 'N', '2008-05-05 00:00:00.000', '2008-07-10 00:00:00.000' union all
select 5834, 3, 'R', '2010-01-19 16:57:06.000', '2010-01-26 00:00:00.000' union all
select 5834, 1, 'N', '2010-01-19 00:00:00.000', '2010-01-19 16:28:02.000' union all
select 5834, 5, 'C', '2010-03-02 00:00:00.000', '2010-03-09 00:00:00.000' union all
select 178248, 4, 'R', '2008-12-23 14:00:47.000', '2009-03-16 00:00:00.000' union all
select 178248, 6, 'R', '2009-06-17 14:19:08.000', '2009-09-15 00:00:00.000' union all
select 178248, 2, 'C', '2008-07-10 00:00:00.000', '2008-10-02 00:00:00.000' union all
select 5834, 2, 'R', '2010-01-19 16:28:02.000', '2010-01-19 16:57:06.000' union all
select 5834, 4, 'C', '2010-01-26 00:00:00.000', '2010-02-25 00:00:00.000'

Edit: to get rid of the dangling comma's in the Create Table statements.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-26 : 15:21:28

After reading this detailed explanation I feel its necessary to include the GroupID/OrderNumber combination to your tables, the reason being you cant rely upon the EventType,StartDate,EndDate combinational value to identify a unique order event as there can be a chance of multiple orders with same/multiple group having same event,start and end date values. so unless you add them to tables above, you wont be having a way to join onto and get only actually related entities. Joining on only EventType,StartDate,EndDate combination can cause non equivalent records (orders) to be linked due to fact of having same values for all fields and will yield wrong results

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

LaurieCox

158 Posts

Posted - 2012-04-26 : 15:34:50

Hi visakh16,

I just posted a response to your answer in the other thread (before I saw your post here) coming to the same conclusion about the extra fields. So I will just have to make that work.

But what about the question posed in this thread? How do I write the join statement to the Instructions table for inserting records into the ScriptDrugs table so that the correct foreign key is used based on the rules I described in the op? Assume that the extra fields are added into the base tables.

Again thanks for your help,

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources