data cleansing - SQL Server Forums

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

SQL Server Development (2000)

data cleansing

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-12 : 08:53:44

John Smith writes "I have two tables, "activity" and "session_log". The activity table contains
activity ids and the begin and end dates of each activity. The session_log
table records each activity meeting, including the meeting date
(session_DATE). Logically speaking, each session_DATE must fall between the
applicable ACT_BEGIN and ACT_END dates.

CREATE TABLE [dbo].[Activity] (
[ACT_ID] [int] NOT NULL ,
[ACT_BEGIN] [datetime] NOT NULL ,
[ACT_END] [datetime] NOT NULL ,
) ON [PRIMARY]

CREATE TABLE [dbo].[session_log] (
[session_ID] [int] NOT NULL ,
[ACT_ID] [int] NOT NULL ,
[session_DATE] [datetime] NOT NULL ,
) ON [PRIMARY]

INSERT INTO [Activity]([ACT_ID], [ACT_BEGIN], [ACT_END])
VALUES(1, '6/1/00', '6/30/01')
INSERT INTO [Activity]([ACT_ID], [ACT_BEGIN], [ACT_END])
VALUES(2, '5/1/01', '6/30/02')
INSERT INTO [Activity]([ACT_ID], [ACT_BEGIN], [ACT_END])
VALUES(3, '11/1/01', '11/25/01')
INSERT INTO [Activity]([ACT_ID], [ACT_BEGIN], [ACT_END])
VALUES(4, '7/1/00', '11/25/00')

INSERT INTO [session_log]([session_ID], [ACT_ID], [session_DATE])
VALUES(1, 1, '11/25/01')
INSERT INTO [session_log]([session_ID], [ACT_ID], [session_DATE])
VALUES(2, 1, '6/3/00')
INSERT INTO [session_log]([session_ID], [ACT_ID], [session_DATE])
VALUES(3, 2, '6/30/02')
INSERT INTO [session_log]([session_ID], [ACT_ID], [session_DATE])
VALUES(4, 2, '11/25/00')
INSERT INTO [session_log]([session_ID], [ACT_ID], [session_DATE])
VALUES(5, 3, '11/25/01')

The code in the past did not check for out-of-bounds session_DATE values,
however. A session_DATE value of '11/25/01' for an activity with an
ACT_BEGIN value of '6/1/00' and an ACT_END value of '6/30/01', for example,
was allowed. So the session_log table contains a mix of valid and invalid
session_date values for all activities.

What I would like to do is to change the invalid session_date values to
valid ones (not necessarily ACTUAL values, but least values that fall
between the applicable ACT_BEGIN and ACT_END dates). Not sure how to
implement this in code, but I am beginning to sketch out the process:

1. create a temporary table with all possible valid session_DATE values for
each activity, kind of like a calendar; so, for example, activity 3 would
have all possible dates between and including '11/1/01' and '11/25/01'

2. either create another temporary table or modify the first temporary table
so that already used session_DATE values are marked out (only one activity
session is allowed for each day of the activity period); so, for example,
'11/25/01' is unavailable for assignment when fixing activity 3's
session_log data entry errors since '11/25/01' has already been used...

3. go through the temporary table and session_log table row by row and as an out-of-bounds session_DATE
value is found in the session_log table, change it to a valid one using the
valid session_DATE value from the temporary table. After using a value from
the temporary table it can no longer be used, so we move on to the next row
in the temporary table...

Any ideas?"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources