[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

SQL Conundrum

Dan Williams

3/16/2007 3:45:00 PM

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

Dan
4 Answers

Razvan Socol

3/18/2007 6:40:00 PM

0

Please post some sample data (as INSERT statements) and expected
results.

Razvan

Dan Williams

3/19/2007 11:46:00 AM

0

StatusId Status
--------------------------
1 Created
2 Covered
3 Cancelled
4 Not Covered


Users UserName
-------------------------------
1 Dan
2 Fred
3 Wilma

BookId Start Finish Hours
-----------------------------------------------------
1 09:00 11:00 2
2 09:00 12:00 3
3 12:00 14:00 2

BookingStatusID BookId StatusID UserId Date
--------------------------------------------------------------------------------------
1 1 1
1 2007/03/29 11:36
2 2 1
2 2007/03/29 11:40
3 3 1
1 2007/03/29 11:50
4 2 2
1 2007/03/29 12:00
5 1 2
2 2007/03/29 12:05
6 2 3
3 2007/03/29 12:06
7 2 2
3 2007/03/29 12:10


As you may be able to see, the BookingStatus table contains a history
of the bookings. Booking 2 has been covered by Dan at 12:00, but then
cancelled and recovered by Wilma at 12:10.

I'm trying to produce a report that will show the number of bookings
each user has covered during a specified time period, but i only want
it to count each distinct booking once. For example, in the above
data, Fred and Wilma should show as covering 1 booking and Dan as 0.
Wilma cancelled and recovered the booking Dan originally covered,
hence Dan doesn't get creditted for covering it, Wilma does.

hope this makes sense!

Thanks again

Dan

markc600

3/19/2007 1:09:00 PM

0

Maybe this?

select u.UserName,
count(BookingStatusID) as Num
from Users u
left outer join BookingStatus b on u.UserId=b.UserId and b.StatusID=2
and not exists (select * from BookingStatus b2
where b2.BookId=b.BookId
and b2.Date>b.Date)
group by u.UserName
order by u.UserName

--CELKO--

3/19/2007 2:03:00 PM

0

>> Many thanks for any suggestions <<

Here is a "cut & paste" on history tables. In your case, I owudl 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 < end_date),
PRIMARY KEY (upc, start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 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;