Change history merging - 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 2005 Forums

Transact-SQL (2005)

Change history merging

Author

Topic

CorporateTool
Starting Member

1 Post

Posted - 2009-11-16 : 15:50:24

Hello everyone,

I have a problem that is really cooking my brain. Here's the scenario:

I have a table of demographic information keyed with a unique numeric PK. Columns are ID (PK), first name, last name, location, division, and hire date.

The table holds the current location and division values for an employee. Only one record exists in this table per employee.

Now lets say I have a change history table with a FK of the first table's PK (ID). It contains Change ID (PK), ID (FK), field ID (1 for division, 2 for location), value before, value after, change effective date.

As you probably guessed, this second table holds a historical record of all the changes that have occurred for a single employee. There can be many records per employee, but there don't have to be any records. Also when a location is changed, division doesn't necessarily have to be changed and vice-versa.

My task is to try to take all of the fields from the first table except location and division and join in the complete history of division and location changes for that individual. As if this wasn't bad enough, I need to show effective and termination dates for each date span and both the location and division value for that span.

Unfortunately I no control over the software that enforces this goofy change-tracking method and I'm stuck with what it's providing. Normally I would record all the details of a record when something changes, but this just isn't an option in this case. Any ideas?

Here are some queries to build the tables and populate them with example data:

CREATE TABLE [tblEmployees](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Last_Name] [varchar](50) NULL,
[First_Name] [varchar](50) NULL,
[Location] [varchar](50) NULL,
[Division] [varchar](50) NULL,
[Hire_Date] [datetime] NULL,
CONSTRAINT [PK_tblTemp] PRIMARY KEY CLUSTERED
(
[ID] ASC
))

INSERT INTO [tblEmployees]
([Last_Name],[First_Name],[Location],[Division],[Hire_Date])
VALUES
('Employee','Test','Denver','110','2005-10-01')

INSERT INTO [tblEmployees]
([Last_Name],[First_Name],[Location],[Division],[Hire_Date])
VALUES
('Employee2','Test','Chicago','220','2004-01-01')

CREATE TABLE [tblChanges](
[Change_ID] [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NOT NULL,
[Field_ID] [int] NOT NULL,
[Value_Before] [varchar](50) NOT NULL,
[Value_After] [varchar](50) NOT NULL,
[Change_Effective] [datetime] NOT NULL,
CONSTRAINT [PK_tblChanges] PRIMARY KEY CLUSTERED
(
[Change_ID] ASC
))

ALTER TABLE [dbo].[tblChanges] WITH CHECK ADD CONSTRAINT [FK_tblChanges_tblTemp1] FOREIGN KEY([ID])
REFERENCES [dbo].[tblEmployees] ([ID])

ALTER TABLE [dbo].[tblChanges] CHECK CONSTRAINT [FK_tblChanges_tblTemp1]

INSERT INTO [tblChanges]
([ID],[Field_ID],[Value_Before],[Value_After],[Change_Effective])
VALUES
(1,1,'Boise','San Diego','2006-01-01')

INSERT INTO [tblChanges]
([ID],[Field_ID],[Value_Before],[Value_After],[Change_Effective])
VALUES
(1,2,'90','110','2006-01-01')

INSERT INTO [tblChanges]
([ID],[Field_ID],[Value_Before],[Value_After],[Change_Effective])
VALUES
(1,1,'San Diego','Denver','2006-09-01')

INSERT INTO [tblChanges]
([ID],[Field_ID],[Value_Before],[Value_After],[Change_Effective])
VALUES
(2,1,'Boise','Denver','2004-03-28')

INSERT INTO [tblChanges]
([ID],[Field_ID],[Value_Before],[Value_After],[Change_Effective])
VALUES
(2,2,'90','220','2006-02-01')

INSERT INTO [tblChanges]
([ID],[Field_ID],[Value_Before],[Value_After],[Change_Effective])
VALUES
(2,1,'Denver','Chicago','2006-09-01')

Many thanks,

-Jeff

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-11-16 : 17:16:35

I'm not clear on what your question actually is...

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-11-17 : 01:37:48

Select
E.ID,
E.last_name,
E.First_name,
E.Hire_date,
CASE C.Field_ID WHEN 1 THEN 'Division' When 2 Then 'Location' End AS Field,
C.Value_before,
C.Value_After,
C.Change_effective
From
tblEmployees E,
tblChanges C
Where
E.ID=C.ID

UNION ALL

Select
E.ID,
E.last_name,
E.First_name,
E.Hire_date,
'Division' AS Field,
E.Division AS Value_before,
E.Division AS Value_After,
E.Hire_date AS Change_effective
From
tblEmployees E
LEFT JOIN
tblChanges C
ON E.ID=C.ID
Where
C.ID IS NULL

UNION ALL

Select
E.ID,
E.last_name,
E.First_name,
E.Hire_date,
'Location' AS Field,
E.Location AS Value_before,
E.Location AS Value_After,
E.Hire_date AS Change_effective
From
tblEmployees E
LEFT JOIN
tblChanges C
ON E.ID=C.ID
Where
C.ID IS NULL
Order by
1,5,8

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources