T-SQL Query Help Required

All,

Hopefully this will make sense but if not, I can elaborate more.

I have a database which is the back-end of an events booking system.

There are 5 tables defined, 3 of which are used for event data and 2 of which are used for bookings. The tables are defined as follows...

CREATE TABLE Events
(
    EventID INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(50) NULL,
    IsActive BIT NOT NULL,
    Notes NTEXT NULL
)

CREATE TABLE EventDates
(
    EventDateID INT IDENTITY(1,1) NOT NULL,
    EventID INT NOT NULL,
    EventDate DATE NULL,
    Notes NTEXT NULL
)

CREATE TABLE EventDateTimes
(
    EventDateTimeID INT IDENTITY(1,1) NOT NULL,
    EventDateID INT NOT NULL,
    StartTime NVARCHAR(5) NULL,
    FinishTime NVARCHAR(5) NULL,
    Cost SMALLMONEY NULL
)

The reason for the above tables is to meet the following criteria.

• a single event can run over multiple days
• each of these event days can have multiple start times
• each of these event times can have different costs, depending on start/finish times

My booking tables are defined as follows...

CREATE TABLE Bookings
(
    BookingID INT IDENTITY(1,1) NOT NULL,
    CustomerID INT NOT NULL,
    EventID INT NOT NULL,
    BookingDate DATE NULL,
    AmountPaid SMALLMONEY NULL,
    Discount SMALLMONEY NULL
)

CREATE TABLE BookingDates
(
    BookingDateID INT IDENTITY(1,1) NOT NULL,
    BookingID INT NOT NULL,
    EventDateTimeID INT NOT NULL
)

Each booking has a reference to the overall event and keeps track of the total amount paid so far for the event (customers can pay for each day/time of the event individually or altogether) and also the discount which applies to the overall event (based on number of days booked in total).

What I'm getting really confused with is trying to determine the amount paid for bookings between certain dates. It may well be that my table layout is complete and utter rubbish and that there is a far better way to achieve what I'm attempting to do. Or I've just over complicated things way too much.

For example, I want to find the total amount paid for all bookings between 1st May 2011 and the 31st May 2011.

I think I may have LEFT, RIGHT and INNER JOIN'd myself to utter confusion.

Please could someone offer some advice. Tha开发者_运维问答nks in advance, Kev

I want to find the total amount paid for all bookings between 1st May 2011 and the 31st May 2011.

declare @FromDate date = '2011-05-01'
declare @ToDate date = '2011-05-31'

select sum(AmountPaid) as SumAmountPaid
from Bookings
where BookingDate between @FromDate and @ToDate

Or if you want to use EventDates.EventDate:

select sum(B.AmountPaid) as SumAmountPaid
from Bookings as B
  inner join BookingDates as BD
    on B.BookingID = BD.BookingID
  inner join EventDateTimes as EDT
    on BD.EventDateTimeID = EDT.EventDateTimeID
  inner join EventDates as ED
    on ED.EventDateID = ED.EventDateID
where ED.EventDate between @FromDate and @ToDate

a single event can run over multiple days each of these event days can have multiple start times each of these event times can have different costs, depending on start/finish times

You need an EVENTS prototype table whose columns describe the constant time-independent features of the event.

EVENTS
eventid
title   
etc

You need an EVENTSINSTANCES table to define individual occurrences of the event with the time-specific and time-dependent data.

EVENTINSTANCES
eventid
eventinstanceid
startDateTime
endDateTime
venue
fullEventDiscountPrice  

Your EVENTINSTANCES table should use datetime types for tracking the startTime and finishTime. In that way, you can have an event that begins at 7pm on Friday evening and ends at 4pm on Sunday afternoon. This table could contain the fee/entry price for the entire event, if it costs other than what it would cost to book the days individually, e.g. a discount.

From the EVENTINSTANCES table you can have a trigger or a procedure populate an EVENTDAYS table. If the event instance begins on 7pm Friday evening and ends at 4pm on Sunday, the EVENTDAYS table would contain an entry for the Friday, the Saturday, and the Sunday:

EVENTDAYS
eventdayid
eventinstanceid
eventDate  (datetime)
dailyFee  (money)  

In order to accommodate a difference in price between an event booked in its entirety and an event booked on a day-by-day basis, I would probably use two bookings tables:

 EVENTINSTANCEBOOKINGS (which points back to EVENTINSTANCES)

 and

 EVENTDAYBOOKINGS (which points back to EVENTDAYS)

You could UNION joins based on these two tables to get a composite view of the bookings; or you could put a trigger/procedure on EVENTINSTANCEBOOKINGS that populates EVENTDAYBOOKINGS analogous to how EVENTDAYS was populated from EVENTINSTANCES. Thus, for example, if someone were to book the Friday-Sunday event in its entirety, EVENTDAYBOOKINGS would get populated with three bookings records, each with a pro-rata money column (EventPrice / 3 [number of days in the event]).

This structure would greatly simplify the query that's giving you trouble -- the one that figures out total money for event bookings between two arbitrary dates. The granular entities EVENTDAYS and EVENTDAYBOOKING eliminate the date-range complexities that would arise when querying against EVENTINSTANCES and EVENTINSTANCEBOOKINGS. However, there's no free lunch -- while that query and its ilk are made simpler the inserts/updates have procedures/triggers attached to them.