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

nellsterAsked:
Who is Participating?
 
David ToddSenior DBACommented:
Hi,

Looking back, the paste didn't work ...

use BookingDatabase
go

declare @EventStart datetime
declare @EventEnd datetime

-- Event is Monday to Friday, first week in October
select @EventStart = '2007-10-01', @EventEnd = '2007-01-05'

-- Gives the individual number of nights
-- for a booking during the event
select datediff(
      dd
      -- take the later of CheckIn or EventStart
      -- doesn't count nights prior to event
      , case
            when b.CheckInDate < @EventStart then @EventStart
            else b.CheckInDate
      end
      -- take the earlier of CheckOut or EventEnd
      -- doesn't count nights after event
      , case
            when b.CheckOutDate < @EventEnd then @EventEnd
            else b.CheckOutDate
      end
      ) as Nights
      , b.RoomTypeID
from dbo.Booking b
-- only want stays during the event
where b.CheckInDate <= @EventEnd and b.CheckOutDate >= @EventStart


select
      rt.RoomType
      , sum( eventBooking.Nights ) TotalNights
from dbo.RoomType rt
inner join
      (
      -- for a booking during the event
      select datediff(
            dd
            -- take the later of CheckIn or EventStart
            -- doesn't count nights prior to event
            , case
                  when b.CheckInDate < @EventStart then @EventStart
                  else b.CheckInDate
            end
            -- take the earlier of CheckOut or EventEnd
            -- doesn't count nights after event
            , case
                  when b.CheckOutDate < @EventEnd then @EventEnd
                  else b.CheckOutDate
            end
            ) as Nights
            , b.RoomTypeID
      from dbo.Booking b
      -- only want stays during the event
      where b.CheckInDate <= @EventEnd and b.CheckOutDate >= @EventStart
      ) eventBookings
      on eventBookings.RoomTypeID = rt.RoomTypeID

Regards
  David
0
 
4rcl1t3Commented:
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.
0
 
SQL_SERVER_DBACommented:
SELECT datediff(dd, CheckInDate, CheckOutDate) AS 'date difference in days', RoomTypeID FROM table
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
David ToddSenior DBACommented:
Hi,

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

Cheers
  David
0
 
David ToddSenior DBACommented:
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
0
 
LowfatspreadCommented:
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?
0
 
nellsterAuthor Commented:
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
0
 
nellsterAuthor Commented:
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
0
 
4rcl1t3Commented:
It's the alias for the table Booking it is defined in the from clause, here: from dbo.Booking b
0
 
David ToddSenior DBACommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.