Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Determine number of nights between dates for rooming list?

Posted on 2007-10-09
12
Medium Priority
?
350 Views
Last Modified: 2008-09-20
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

0
Comment
Question by:nellster
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 1

Expert Comment

by:4rcl1t3
ID: 20043287
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
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20043352
SELECT datediff(dd, CheckInDate, CheckOutDate) AS 'date difference in days', RoomTypeID FROM table
0
 
LVL 35

Expert Comment

by:David Todd
ID: 20045210
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 35

Expert Comment

by:David Todd
ID: 20045280
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 20046395
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
 

Author Comment

by:nellster
ID: 20046878
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
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 20051591
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
 

Author Comment

by:nellster
ID: 20065432
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
 
LVL 1

Expert Comment

by:4rcl1t3
ID: 20065515
It's the alias for the table Booking it is defined in the from clause, here: from dbo.Booking b
0
 
LVL 35

Expert Comment

by:David Todd
ID: 20074467
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question