SQL Query to join on the previous foreign key to return history of changes

Question

Hi All,

I am looking for a way to return a history of all changes made to records in a SQL Server database for a third party application. There are 3 tables:

BOOKING - Employees booked to projects

BK_ID	BK_EMPNAME	BK_TASKNAME	BK_START	BK_END	BK_TIMEHOURS
1	John Smith	Project X	10/10/2012	20/10/2012	20
2	Mary Bush	Project Y	09/11/2012	22/11/2012	40

BOOKINGHISTORY - History of all changes made to booking records

BKH_ID	BKH_BK_ID	BKH_EMPNAME	BKH_TASKNAME	BKH_START	BKH_END	BKH_TIMEHOURS	BKH_STATUS
1	1	John Smith	Project X	10/10/2012	18/10/2012	16	Inserted
2	1	John Smith	Project X	10/10/2012	22/10/2012	26	Updated
3	1	John Smith	Project X	10/10/2012	20/10/2012	20	Updated
4	2	Mary Bush	Project Y	07/11/2012	26/11/2012	50	Inserted
5	2	Mary Bush	Project Y	09/11/2012	22/11/2012	40	Updated
6	3	Mary Bush	Project X	14/12/2012	28/12/2012	40	Inserted
7	3	Mary Bush	Project X	22/12/2012	28/12/2012	30	Updated
8	3			01/01/1900	01/01/1900	0	Deleted

BOOKINGLOG - History of users who have made those changes

BKL_ID	BKL_BKH_ID	BKL_STATUS	BKL_CHANGEUSER	BKL_CHANGEDATE
106	1	Inserted	Bill Gates	18/08/2012
107	2	Updated	Paula Gills	19/08/2012
108	3	Updated	Paula Gills	20/08/2012
109	4	Inserted	Paula Gills	12/08/2012
110	5	Updated	Bill Gates	16/08/2012
111	6	Inserted	Bill Gates	14/08/2012
112	7	Updated	Harry Simons	15/08/2012
113	8	Deleted	Bill Gates	20/08/2012

Notice in the BOOKINGHISTORY table, when a deletion occurs the record details are cleared out leaving only the foreign key (BKH_BK_ID) and status (BKH_STATUS) so a join to the previous record will be required in some way. This is where I am falling over.

I am looking to create a query which returns a history of all the changes including the name of the user who made the change, with a plus next to the time (BKH_TIMEHOURS) when an update and minus next to time when a deletion occurs or hours are reduced:

BKH_EMPNAME	BKH_TASKNAME	BKH_START	BKH_END	BKH_TIMEHOURS	BKH_STATUS	BKL_CHANGEUSER
John Smith	Project X	10/10/2012	18/10/2012	16	Inserted	Bill Gates
John Smith	Project X	10/10/2012	22/10/2012	+10	Updated	Paula Gills
John Smith	Project X	10/10/2012	20/10/2012	-6	Updated	Paula Gills
Mary Bush	Project Y	07/11/2012	26/11/2012	50	Inserted	Paula Gills
Mary Bush	Project Y	09/11/2012	22/11/2012	-10	Updated	Bill Gates
Mary Bush	Project X	14/12/2012	28/12/2012	40	Inserted	Bill Gates
Mary Bush	Project X	22/12/2012	28/12/2012	-10	Updated	Harry Simons
		01/01/1900	01/01/1900	-30	Deleted	Bill Gates

The order of records is not sequential in any tables.

Any help or thoughts would be be greatly appreciated.

Thank you.

Here are the queries to create the tables and records.

CREATE TABLE [dbo].[BOOKING](
	[BK_ID] [int] NULL,
	[BK_EMPNAME] [varchar](255) NULL,
	[BK_TASKNAME] [varchar](255) NULL,
	[BK_START] [datetime] NULL,
	[BK_END] [datetime] NULL,
	[BK_TIMEHOURS] [float] NULL
) ON [PRIMARY]

GO

CREATE TABLE [BOOKINGHISTORY](
	[BKH_ID] [int] NULL,
	[BKH_BK_ID] [int] NULL,
	[BKH_EMPNAME] [varchar](255) NULL,
	[BKH_TASKNAME] [varchar](255) NULL,
	[BKH_START] [datetime] NULL,
	[BKH_END] [datetime] NULL,
	[BKH_TIMEHOURS] [float] NULL,
	[BKH_STATUS] [varchar](255) NULL
) ON [PRIMARY]

GO

CREATE TABLE [BOOKINGLOG](
	[BKL_ID] [int] NULL,
	[BKL_BKH_ID] [int] NULL,
	[BKL_STATUS] [varchar](255) NULL,
	[BKL_CHANGEUSER] [varchar](255) NULL,
	[BKL_CHANGEDATE] [datetime] NULL
) ON [PRIMARY]

GO

insert into [BOOKING] (BK_ID, BK_EMPNAME, BK_TASKNAME, BK_START, BK_END, BK_TIMEHOURS)
values (1, 'John Smith', 'Project X', '10-Oct-2012', '20-Oct-2012', 20);
insert into [BOOKING] (BK_ID, BK_EMPNAME, BK_TASKNAME, BK_START, BK_END, BK_TIMEHOURS)
values (2, 'Mary Bush', 'Project Y', '09-Nov-2012', '22-Nov-2012', 40);

insert into [BOOKINGHISTORY] (BKH_ID, BKH_BK_ID, BKH_EMPNAME, BKH_TASKNAME, BKH_START, BKH_END, BKH_TIMEHOURS, BKH_STATUS)
values (1, 1, 'John Smith', 'Project X', '10-Oct-2012', '18-Oct-2012', 16, 'Inserted');
insert into [BOOKINGHISTORY] (BKH_ID, BKH_BK_ID, BKH_EMPNAME, BKH_TASKNAME, BKH_START, BKH_END, BKH_TIMEHOURS, BKH_STATUS)
values (2, 1, 'John Smith', 'Project X', '10-Oct-2012', '22-Oct-2012', 26, 'Updated');
insert into [BOOKINGHISTORY] (BKH_ID, BKH_BK_ID, BKH_EMPNAME, BKH_TASKNAME, BKH_START, BKH_END, BKH_TIMEHOURS, BKH_STATUS)
values (3, 1, 'John Smith', 'Project X', '10-Oct-2012', '20-Oct-2012', 20, 'Updated');
insert into [BOOKINGHISTORY] (BKH_ID, BKH_BK_ID, BKH_EMPNAME, BKH_TASKNAME, BKH_START, BKH_END, BKH_TIMEHOURS, BKH_STATUS)
values (4, 2, 'Mary Bush', 'Project Y', '07-Nov-2012', '26-Nov-2012', 50, 'Inserted');
insert into [BOOKINGHISTORY] (BKH_ID, BKH_BK_ID, BKH_EMPNAME, BKH_TASKNAME, BKH_START, BKH_END, BKH_TIMEHOURS, BKH_STATUS)
values (5, 2, 'Mary Bush', 'Project Y', '09-Nov-2012', '22-Nov-2012', 40, 'Updated');
insert into [BOOKINGHISTORY] (BKH_ID, BKH_BK_ID, BKH_EMPNAME, BKH_TASKNAME, BKH_START, BKH_END, BKH_TIMEHOURS, BKH_STATUS)
values (6, 3, 'Mary Bush', 'Project X', '14-Dec-2012', '28-Dec-2012', 40, 'Inserted');
insert into [BOOKINGHISTORY] (BKH_ID, BKH_BK_ID, BKH_EMPNAME, BKH_TASKNAME, BKH_START, BKH_END, BKH_TIMEHOURS, BKH_STATUS)
values (7, 3, 'Mary Bush', 'Project X', '22-Dec-2012', '28-Dec-2012', 30, 'Updated');
insert into [BOOKINGHISTORY] (BKH_ID, BKH_BK_ID, BKH_EMPNAME, BKH_TASKNAME, BKH_START, BKH_END, BKH_TIMEHOURS, BKH_STATUS)
values (8, 3, '', '', '', '', '', 'Deleted');

insert into [BOOKINGLOG] (BKL_ID, BKL_BKH_ID, BKL_STATUS, BKL_CHANGEUSER, BKL_CHANGEDATE)
values (106, 1, 'Inserted', 'Bill Gates', '18-Aug-2012');
insert into [BOOKINGLOG] (BKL_ID, BKL_BKH_ID, BKL_STATUS, BKL_CHANGEUSER, BKL_CHANGEDATE)
values (107, 2, 'Updated', 'Paula Gills', '19-Aug-2012');
insert into [BOOKINGLOG] (BKL_ID, BKL_BKH_ID, BKL_STATUS, BKL_CHANGEUSER, BKL_CHANGEDATE)
values (108, 3, 'Updated', 'Paula Gills', '20-Aug-2012');
insert into [BOOKINGLOG] (BKL_ID, BKL_BKH_ID, BKL_STATUS, BKL_CHANGEUSER, BKL_CHANGEDATE)
values (109, 4, 'Inserted', 'Paula Gills', '12-Aug-2012');
insert into [BOOKINGLOG] (BKL_ID, BKL_BKH_ID, BKL_STATUS, BKL_CHANGEUSER, BKL_CHANGEDATE)
values (110, 5, 'Updated', 'Bill Gates', '16-Aug-2012');
insert into [BOOKINGLOG] (BKL_ID, BKL_BKH_ID, BKL_STATUS, BKL_CHANGEUSER, BKL_CHANGEDATE)
values (111, 6, 'Inserted', 'Bill Gates', '14-Aug-2012');
insert into [BOOKINGLOG] (BKL_ID, BKL_BKH_ID, BKL_STATUS, BKL_CHANGEUSER, BKL_CHANGEDATE)
values (112, 7, 'Updated', 'Harry Simons', '15-Aug-2012');
insert into [BOOKINGLOG] (BKL_ID, BKL_BKH_ID, BKL_STATUS, BKL_CHANGEUSER, BKL_CHANGEDATE)
values (113, 8, 'Deleted', 'Bill Gates', '20-Aug-2012');

GO

Answer 1

Thanks for posting table schema and sample data.

Could you tell us the logic you use to identify "previous" row, and also the version of SQL Server that you are using?

It will also be helpful if you explain more about the role being played by the column [BKH_BK_ID] in the table [BOOKINGHISTORY]. You mentioned foreign key, but where is this foreign key pointing to (there is no FK constraint in the schema you provided)?

---

AMB

Some guidelines for posting questions...

Answer 2

Hi Hunchback,

The BKH_BK_ID in the BOOKINGHISTORY table points to the BK_ID primary key in the BOOKING table.

Actually, one thing I missed from the above is the date of change (BKL_CHANGEDATE) in the BOOKINGLOG table which stores the name of the user who made the change and when. This change field can be used to work out the previous change to a record.

I will update the scripts above.

Answer 3

Hi,

Apologies for bumping this. Just wondering if any one had any thoughts on the original question

Thanks.

Answer 4

Could you walk us through the problem using one BK_ID (i.e. BK_ID = 1)?

Tell us what you expect to get for this BK_ID in specific and why.

---

AMB

Some guidelines for posting questions...

Answer 5

Please post real DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules (you did not). Temporal data should use ISO-8601 formats (you did not; was this copied from Oracle?). Code should be in Standard SQL as much as possible and not local dialect. Pictures and fancy typesetting is a waste. 

This is minimal polite behavior on SQL forums. 

Rows are not records, this is a fundamental concept. I have never seen a man with a 255 character name; in fact none of the columns of that size make any sense! Why do you think FLOAT makes sense for data? Etc. 

There is no key in any table and no way to ever have a key. Do you really have only one booking? How is a “booking_emp_name” totally different from a mere “emp_name”?  Why is booking_nbr totally different from the log and history booking numbers? Let me answer my own questions:  COBOL programmers used to write code like that to include the file name and use pointer chains to link records together. It is a non-relational nightmare. 

It is obvious that this was done by someone without any RDBMS or data modeling education. Her is a quick skeleton to help you replace this mess. 

CREATE TABLE Bookings
(booking_nbr INTEGER NOT NULL,
 task_name VARCHAR(25) NOT NULL,
 PRIMARY KEY (booking_nbr, task_name),
  etc);

CREATE TABLE Booking_History 
(booking_nbr INTEGER NOT NULL 
  REFERENCES Bookings(booking_nbr), 
 PRIMARY KEY (booking_nbr, booking_start_date), 
 booking_start_date DATE NOT NULL,
 booking_end_date DATE, -- null is current status
 CHECK (booking_start_date <= booking_end_date),
 foobar_points INTEGER NOT NULL,
etc);

There are more constraints we could add to prevent gaps in the dates, illegal status changes, etc. but this is a minimal normalized schema to get you started. 

All bookings should be in the same table. The current booking is a special case found with a VIEW that looks for a NULL booking_end_date. Your design flaw is called “attribute splitting” and it is a classic error made by file system programmers who want to physically move data around on tapes and punch cards. That means those history and log tables will not be there in a correct schema. And of course, nobody would keep log data in the schema being logged. It is not just dangerous, it is illegal. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 6

You wanna know what *I* call a mess Joe?  

1GB of InfoPath XML Files in SharePoint being used as a database to store information.. where nodes are chained together like punch cards.. and where to calculate values one has to iterate back through nodes.. which have no ordering information. The integers you need to use to iterate backwards through this 'chain' of nodes are free text fields... which often contain non-numeric data.

Oh and a lot of the files contain around 10MB of MIME encoded attachments..

Messy databases are bad enough.. but dealing with messy XML... 

I wish you were here with that Japanese stick you carry. Do you charge a fee to perform beatings on terrible data designers?

---

Josh Ash