SQL Conundrum

SQL Apprentice Question
I have the following SQL schema

Bookings
-------------
BookID
Hours


BookingStatus
---------------------
BookStatusID
BookingId
StatusID
UserId
Date


Statues
-----------
StatusID


Statuses included Created, Covered, Cancelled, Not Covered, To Be
Arranged, etc.


The BookingStatus table contains a history of the statuses a booking
goes through. Whenever a user updates the status of a booking, a new
record is inserted into the Booking Status with the corresponding
data.


I'm trying to create a report to show the number of bookings each user
has covered over a given time period. However, i only want to count
bookings that are still covered (i.e. have not been cancelled since
they were covered) and i don't want it to count bookings that have
been covered more than once. If it did, a user would be credited for
covering an individual booking more than once.


In other words, i need to fetch only the top 1 status of each booking
and count only those that are covered, if you see what i mean!


I could record the current status of the booking in my Bookings table,
but i'm worried about data integrity and keeping the booking status
consistant.


Anyone got any bright ideas about how i can either design my schema
better, or come up with an efficient SQL query to select my report? I
also need to sum the hours each user has covered, as well as a count
of bookings covered.


Many thanks for any suggestions


Celko Answers
>> Many thanks for any suggestions

Here is a "cut & paste" on history tables. In your case, I would also look at my article on Transition Constraints at www.dbazine.com for more ideas.

===============
The start and stop times are what you should have been catching in the first place and not the computed hours. Think raw data and single facts when designing a table. Let me use a history table for price changes. The fact to store is that a price had a duration:

CREATE TABLE PriceHistory (upc CHAR(13) NOT NULL
REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_date <> 0.0000),
etc.);


You actually needs more checks to assure that the start date is at
00:00 and the end dates is at 23:59:59.999 Hrs. You then use a
BETWEEN predicate to get the appropriate price.


SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);


It is also a good idea to have a VIEW with the current data:


CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;