complex logic inside of cursor

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 2005 Forums

Transact-SQL (2005)

complex logic inside of cursor

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-08-26 : 12:33:56

I have three tables:
Registration:
CREATE TABLE [dbo].[Registration](
[Registration_Key] [int] IDENTITY(1,1) NOT NULL,
[Unique_ID] [char](16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DOB] [datetime] NOT NULL,
[Gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Registration] PRIMARY KEY CLUSTERED
(
[Registration_Key] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Insert into registration values ( 'ABC1001', '19760123', 'F' )
Insert into registration values ( 'CDE1002', '19690221', 'M' )
Insert into registration values ( 'GF678', '19800204', 'F' )

Registration_key unique_id DOB Gender
1 ABC1001 1/23/1976 12:00:00 AM F
2 CDE1002 2/21/1969 12:00:00 AM M
3 GF678 2/4/1980 12:00:00 AM F

Episodes:
CREATE TABLE [dbo].[Episodes](
[Episode_Key] [int] IDENTITY(1,1) NOT NULL,
[Registration_Key] [int] NOT NULL,
[MCP_Key] [int] NOT NULL,
[Enrollment_Date] [smalldatetime] NOT NULL,
[Discharge_Date] [smalldatetime] NULL,
CONSTRAINT [PK_Episodes] PRIMARY KEY CLUSTERED
(
[Episode_Key] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Episodes] WITH CHECK ADD CONSTRAINT [FK_Episodes_Registration] FOREIGN KEY([Registration_Key])
REFERENCES [dbo].[Registration] ([Registration_Key])
GO
ALTER TABLE [dbo].[Episodes] CHECK CONSTRAINT [FK_Episodes_Registration]

Episode_key Registration_key MCP_key Assessment_date Discharge_key
1 1 1001 1/14/2007 1/14/2007
7 1 1002 11/21/2006 12/23/2006
5 2 1002 11/2/2006 12/25/2006
6 3 1444 3/4/2007 NULL
8 4 1001 3/1/2007 NULL
9 3 1423 3/5/2007 NULL

And

Assessment Table:
CREATE TABLE [dbo].[Assessment](
[Assessment_Key] [int] IDENTITY(1,1) NOT NULL,
[Episode_KEY] [int] NOT NULL,
[Registration_Key] [int] NOT NULL,
[DISABILITY] [tinyint] NULL,
[Year] [int] NULL,
CONSTRAINT [PK_Assessment] PRIMARY KEY CLUSTERED
(
[Assessment_Key] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Assessment] WITH CHECK ADD CONSTRAINT [FK_Assessment_Episodes] FOREIGN KEY([Episode_KEY])
REFERENCES [dbo].[Episodes] ([Episode_Key])
GO
ALTER TABLE [dbo].[Assessment] CHECK CONSTRAINT [FK_Assessment_Episodes]
GO
ALTER TABLE [dbo].[Assessment] WITH CHECK ADD CONSTRAINT [FK_Assessment_Registration] FOREIGN KEY([Registration_Key])
REFERENCES [dbo].[Registration] ([Registration_Key])
GO
ALTER TABLE [dbo].[Assessment] CHECK CONSTRAINT [FK_Assessment_Registration]

Assessmnet_key Episodes Registraton_key Disability year
1 1 1 3 2007
2 5 2 6 2007
3 5 2 6 2007
4 7 1 4 2007
5 1 1 6 2007
6 6 3 7 2006

Stage table that the original data is coming from

CREATE TABLE [dbo].[stage](
[unique_id] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Dob] [datetime] NULL,
[Gender] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mcp_key] [int] NULL,
[assessment_date] [datetime] NULL,
[reason_code] [int] NULL,
[disability] [int] NULL,
[year] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Insert into stage values ( 'CC1001', '19660405', 'M', 1001, '20070301', 0,4, 2007 )
Insert into stage values ( 'ABC1001', '19800723', 'F', 1001, '20070415', 2,4, 2007 )
Insert into stage values ( 'GF678', '19800204', 'M', 1423, '20070305', 7,7, 2007 )

I have to load the stage data to three different tables based on the complex logic. I need to process the row by row using a cursor to follow the business rule.
We will insert the record registration à Episodes à Assessments
Here is the main logic
If the unique_id DOES NOT exist in the registration table.
1)Insert registration records to registration table ( unique record)
2)Retrive registration_key ( identity) and insert Episode table with corresponding data
3) Retrive Episode_key ( identity_key) and insert remaining information into assessment table

If the unique_id DOES exist in the registration table,

1) Get Registration_key and update registration information with new data
2) Scan Episodes table to see there are existing eposide using registration_key and MCP_key where discharge_date is NULL (there are existing episodes : same registration_key , mcP_key where discharge_date is null compare to the STAGE data )
IF the above answer is NO, then DO the same thing as above 2)3)
Retrive registration_key ( identity) and insert Episode table with corresponding data
Retrive Episode_key ( identity_key) and insert remaining information into assessment table

IF the above answer is YES ( there are existing episodes : same registration_key , mcP_key where discharge_date is null compare to the STAGE data)
THEN
Look at the Reason_code, if the reason_code in 2,3,5,6 then update Assessment_date to Discharge_date .. and insert remaining the assessment information.
If the reason code is NOT in 2,3,5,6 , just retrieve the episode_key and insert remaing information to Assessment table.

As an examples :
Here is the stage data
Unique_id DOB GENDER MCP_KEY Assessment_Date Reason_code Disability year
cc1001 4/5/1966 M 1001 3/1/2007 0 4 2007
ABC1001 1/23/1980 F 1001 4/15/2007 2 4 2007
GF678 2/4/1980 M 1423 3/5/2007 7 7 2007

We are going to start with the first row:
Cc1001 does not exist in registration table , so we
1)Insert registration records to registration table ( unique record)
2)Retrive registration_key ( identity) and insert Episode table with corresponding data
3) Retrive Episode_key ( identity_key) and insert remaining information into assessment table

Next row : ABC1001 is Exist in registration table ,
1)so we update the new data with existing data ( in this case the new DOB) in registration table
2) we scan the Episode table to see if there is registration_key and mcp_key where discharge_date is NULL. Yes there is one record in Episodes table ( registration_key = 1 and mcp_key = 1001 where discharge date is null),
So THEN we dont create a new episodes , we just need to update the discharge date and insert assessment records. To be able to update the discharge date ,
Look at the Reason_code, if the reason_code in 2,3,5,6 then update Assessment_date to Discharge_date .. and insert remaining the assessment information.
IF the reason code is NOT in 2,3,5,6 , just retrieve the episode_key and insert remaing information to Assessment table.
The reason_code is 2 in this case, so we set discharge_date as the assessment_date
And add the remaining Assessment data into the assessment table

Next row: GF678
It exists in the registration table so we update the registration information
And scan at the episodes table to see if there is an existing episodes records based on the registration_key and MCP_key .. there is registration_key = 3 but different MCP_key ( 1444) where discharge_date is null..
SO we add new Episodes with registration_key from the registration table and ADD remaining Assessment data to the Assessment table.

So the final output looks like this:

Registration_key unique_id DOB Gnder
1 ABC1001 07/23/1976 F
2 CDE1002 2/21/1969 M
3 GF678 2/4/1980 F
4 cc1001 4/5/1966 M

Episodes table
Episode_key Registration_key MCP_key Assessment_date Discharge_key
1 1 1001 1/14/2007 1/14/2007
7 1 1002 11/21/2006 12/23/2006
5 2 1002 11/2/2006 12/25/2006
6 3 1444 3/4/2007 NULL
8 4 1001 3/1/2007 NULL
9 3 1423 3/5/2007 NULL

Assessment:
Assessmnet_key Episodes Registraton_key Disability year
1 1 1 3 2007
2 5 2 6 2007
3 5 2 6 2007
4 7 1 4 2007
5 1 1 6 2007
6 6 3 7 2006
7 8 4 4 2007
8 1 1 4 2007
9 9 3 7 2007

I hope i explain it well .. I am not sure how to write this complex logic inside of cursor..
can you show me how?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources