Number of Days in Month - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Number of Days in Month

Author

Topic

mgonda
Starting Member

29 Posts

Posted - 2012-03-08 : 13:50:25

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:23:43

Would something like this work for you?

DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
SET @StartDate = '20120201'; -- Feb 1
SET @EndDate = '20120301'; -- March 1 (1 day following the end of the month).

SELECT
	CustomerId,
	Code,
	DATEDIFF(dd,
		CASE WHEN ArrivalDate < '20120201' THEN '20120201' ELSE ArrivalDate END,
		CASE WHEN DepartureDate > '20120301' THEN '20120301' ELSE DepartureDate END
	) AS FebDays
FROM
	GIP_FOLIOS
WHERE
	ArrivalDate <= '20120301'
	AND DepartureDate >= '20120201'
	AND SOURCE_ID = 607
	AND CONV IN ('N7DAY')
	AND ROOMRATE <> 0
	AND RESERVATIONSTATUSCODE <> 'X'

mgonda
Starting Member

29 Posts

Posted - 2012-03-08 : 15:40:43

Thank you, your logic seems perfect. Someday I hope to be able to make the complex simple like that.

When I run it, I'm still getting a slight difference from the count I did, but I'll continue to play until I get it all right.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-08 : 16:22:18

quote:
Originally posted by mgonda

Thank you, your logic seems perfect. Someday I hope to be able to make the complex simple like that.

I think it's the simple, complex like that

[;-)]

Brett

EDIT: And NO CTE's or ROW OVER PARTITION...must be the solar flares

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mgonda
Starting Member

29 Posts

Posted - 2012-03-08 : 16:55:51

To begin with, I'd like to hang my head in shame, and apologize for posting twice. I posted here, and then thought that this might be less looked at than the general postings. I was wrong, and am grateful to see that this was actually the one that was answered first.

I'd also like to let you know that I'm just trying to get to know this new to me database that doesn't have any documentation, which is why I didn't see why there was a difference in the numbers until just a moment ago.

In case you were wondering, I just discovered why there were discrepancies in the numbers I counted, and what I pulled through your help. There are 2 more fields that I didn't think were different, but they are. Along with ArrivalDate and DepartureDate, there is CheckInDate and CheckOutDate. It looks like Arrival and Departure are what were originally booked, and CheckIn and CheckOut are what actually happened. And that is where the discrepancy happened, I was only querying what was booked, and not what actually happened like I was counting.

Thank again for your perfect solution.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-09 : 07:33:23

quote:

EDIT: And NO CTE's or ROW OVER PARTITION...must be the solar flares

Sorry to disappoint you Brett, but this [insert New Jersey regular expression here] SQL 2000 does not allow CTE's and ROW OVER PARTITION!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-09 : 07:35:08

quote:
Originally posted by mgonda

To begin with, I'd like to hang my head in shame, and apologize for posting twice. I posted here, and then thought that this might be less looked at than the general postings. I was wrong, and am grateful to see that this was actually the one that was answered first.

I'd also like to let you know that I'm just trying to get to know this new to me database that doesn't have any documentation, which is why I didn't see why there was a difference in the numbers until just a moment ago.

In case you were wondering, I just discovered why there were discrepancies in the numbers I counted, and what I pulled through your help. There are 2 more fields that I didn't think were different, but they are. Along with ArrivalDate and DepartureDate, there is CheckInDate and CheckOutDate. It looks like Arrival and Departure are what were originally booked, and CheckIn and CheckOut are what actually happened. And that is where the discrepancy happened, I was only querying what was booked, and not what actually happened like I was counting.

Thank again for your perfect solution.

Thank you for the update - Glad you worked it out.

The request to avoid cross-posting is mostly to avoid fragmentation of replies, and people answering questions that have already been answered in the other thread. But it happens, they are not going to ban you for that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources