Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cut Dates in Range To Specific Month

Author  Topic 

mgonda
Starting Member

29 Posts
Posted - 2012-03-08 : 13:55:31
I work at a hotel, and have visitors booking rooms under codes. I am trying to count how many room nights are being booked in a specific month under a specific code.

CustomerID, Code, ArrivalDate, DepartureDate
1 , HT23, 1/30/2012 , 2/3/2012
2 , HT23, 1/31/2012 , 2/2/2012
3 , HT23, 2/1/2012 , 2/4/2012
4 , HT23, 2/28/2012 , 3/2/2012
5 , HT23, 2/29/2012 , 3/5/2012

I have a long, convoluted query that doesn't work because I don't know how to chop off the January or March days to find only the days associated with February. The DepartureDate itself doesn't actually count, because they're not staying the night that day.

So I should get:
CustomerID, FebNights
1, 2
2, 1
3, 3
4, 2
5, 1

But I essentially end up with:
CustomerID, FebNights
1, 4
2, 2
3, 3
4, 3
5, 5

What I want, is the 9 days from this, but I'm getting 17


SELECT SpecDay, COUNT(SpecDay) * TripLength AS Total Days
FROM (
SELECT CustomerID, ArrivalDate, DepartureDate, SpecDay =
CASE
WHEN '2012-02-01 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-01 00:00:00'
WHEN '2012-02-02 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-02 00:00:00'
WHEN '2012-02-03 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-03 00:00:00'
WHEN '2012-02-04 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-04 00:00:00'
WHEN '2012-02-05 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-05 00:00:00'
WHEN '2012-02-06 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-06 00:00:00'
WHEN '2012-02-07 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-07 00:00:00'
WHEN '2012-02-08 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-08 00:00:00'
WHEN '2012-02-09 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-09 00:00:00'
WHEN '2012-02-10 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-10 00:00:00'
WHEN '2012-02-11 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-11 00:00:00'
WHEN '2012-02-12 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-12 00:00:00'
WHEN '2012-02-13 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-13 00:00:00'
WHEN '2012-02-14 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-14 00:00:00'
WHEN '2012-02-15 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-15 00:00:00'
WHEN '2012-02-16 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-16 00:00:00'
WHEN '2012-02-17 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-17 00:00:00'
WHEN '2012-02-18 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-18 00:00:00'
WHEN '2012-02-19 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-19 00:00:00'
WHEN '2012-02-20 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-20 00:00:00'
WHEN '2012-02-21 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-21 00:00:00'
WHEN '2012-02-22 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-22 00:00:00'
WHEN '2012-02-23 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-23 00:00:00'
WHEN '2012-02-24 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-24 00:00:00'
WHEN '2012-02-25 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-25 00:00:00'
WHEN '2012-02-26 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-26 00:00:00'
WHEN '2012-02-27 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-27 00:00:00'
WHEN '2012-02-28 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-28 00:00:00'
WHEN '2012-02-29 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN '2012-02-29 00:00:00' END,
TripLength =
CASE
WHEN '2012-02-01 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-02 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-03 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-04 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-05 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-06 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-07 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-08 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-09 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-10 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-11 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-12 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-13 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-14 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-15 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-16 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-17 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-18 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-19 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-20 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-21 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-22 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-23 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-24 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-25 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-26 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-27 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-28 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate)
WHEN '2012-02-29 00:00:00' BETWEEN ArrivalDate AND DepartureDate - 1 THEN DATEDIFF(DAY,ArrivalDate,DepartureDate) END
FROM GIP_FOLIOS
WHERE SOURCE_ID = 607
AND CONV IN ('N7DAY')--'VOM203','VOM403','VOM503','VOM703','NVOM203','NVOM403','NVOM503','NVOM703','N7DAY','RP312','NM412')
AND ROOMRATE <> 0
AND RESERVATIONSTATUSCODE <> 'X'
GROUP BY CustomerID, ArrivalDate, DepartureDate) AS L

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2012-03-08 : 15:26:07
People who run this forum frown upon the same question being posted to more than one forum. Assuming this is the same question as what you posted in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=172250 take a look at that thread. I posted something that may work for you.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site