asked on

Determine number of nights between dates for rooming list?

I have a registration web site running ASP.NET 2.0 with SQL Server 2005 Express DB. Some of the details that are collected during the registration process are accommodation details.

The fields in the Users table of the DB associated with this are "CheckInDate"(type DateTime), "CheckOutDate"(type DateTime) and "RoomTypeID"(Integer). I have had a request to produce a roominglist that will plot how many of the room-types are booked for each date/night of the event.

I am a bit stumped on the best way to approach constructing the procedure to obtain this report.

Any help gratefully recieved!

Nellster

4rcl1t3

SELECT datediff(dd, CheckInDate, CheckOutDate) AS 'date difference in days' FROM table

see http://msdn2.microsoft.com/en-us/library/ms189794.aspx for more info.

SQL_SERVER_DBA

SELECT datediff(dd, CheckInDate, CheckOutDate) AS 'date difference in days', RoomTypeID FROM table

David Todd

Hi,

I have a few questions:
How many nights for the event?
How many RoomTypes?
What are you creating the report in?

Cheers
David

David Todd

Hi,

Here's some code for starters.

Its not particularly easy, but I wanted to show the no of roomtype by individual nights ...

Can you post the structure of your booking table, and some sample data?

Cheers
David

Lowfatspread

explain you data base in more detail please...

what tables do you have how do they relate?

we need to know about the other tables not just the room reservation one..

how are you reserving rooms
e.g a row per night, row per booking, can you have multiple occupancy per day

e.g. morning meeting, afternoon meeting , evening function?

nellster

ASKER

Thanks for the replies guys, sorry I should have exlained a bit better.

The two tables that are related for this query are "Users" & "RoomType". The Users table has fields for generic registration data such as "FirstName", "Surname" etc.... but also collects the "CheckInDate" and "CheckOutDate" and "RoomTypeID" value which is related to the "RoomType" table which simply has "RoomTypeID" and "RoomTypeName" columns.

The User will simply enter in their CheckIn and CheckOut dates and the reservations are worked out by the Registration Manager running the event. The CheckIn and CheckOut dates on the webform are contained in dropdownlists and pre populated for the duration of the event, (eg.. CheckIn dropdown would contain the valuse "10/09/2007, 11/09/2007, 12/09/2007".... CheckOut dropdown would contain "11/09/2007, 12/09/2007, 12/09/2007, 13/09/2007")

From the details recorded I would need to work out how many rooms are needed for each room type for each night... kinda like below..

            10/09/2007 - 11/09/2007 - 12/09/2007 - 13/09/2007

Basic Room        22 4 12 15
Suite             22        6        35        21
King Suite        0        4 2        6

Hope that makes it a bit clearer?

Nellster

ASKER CERTIFIED SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nellster

ASKER

Thanks for the code pasting David, apologies for not getting back sooner.

Throwing me into a bit of confusion but I think I'm getting there. Syntax wise, what does the 'b' part of "b.CheckInDate" signify?

Thanks,

Nellster

4rcl1t3

It's the alias for the table Booking it is defined in the from clause, here: from dbo.Booking b

David Todd

Hi,

Aliases: Your code started to get hard to read with the full dbo.verylongtablename.somecolumn way of specifying columns. In code of much lenght or complexity it is more usual to use aliases, and it is a good practice to use either the table name or table alias in front of every column.

I've possibly overdone the size of the aliases, and made them too short.

As you have done, its a good practice to specify the owner or schema of each object (the dbo part), including procedures and views and functions.

Regards
David