By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,469 Members | 1,037 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,469 IT Pros & Developers. It's quick & easy.

How to limit DateDiff to a calendar year when data ranges extend beyond that year?

100+
P: 259
I want to calculate an occupancy rate for nights stayed in a hotel. The occupancy rate needs to be for a calendar year.
The fields I am working with are
Arrival
Departure

I have parameters set up in the query as criteria on the Departure field.
FromDate
ToDate

Most of the arrival dates AND departure dates fall within the year...but the reservation occupying the hotel room on January 1 and on December 31 do not start/stop on those exact dates.

For example, someone stayed from December 25 through January 5...I only want 4 of those nights in that year's count (January 1, 2, 3, 4--checkout is on the 5th, they didn't stay that night). My query would count 11.

This is what I'm running in my query, which is beautiful except for the first and last reservation of the year.

Nights: DateDiff("d",[Arrival],[Departure])
parameters Between [FromDate] and [ToDate] which I have set as criteria on the Departure field (and that would be Jan 1 and Dec 31 of any year).

I know how to get what I want...but I don't know how to write it in the query or in code (OMG VBA---I'm just not there. LOL!)

If the Arrival is before (less than?) FromDate, then I need to calculate that entry Between FromDate and Departure (not between Arrival and Departure).

ALSO

If the Departure is after (greater than?) ToDate, then I need to calculate that entry Between Arrival and ToDate (not between Arrival and Departure).

All the other entries are good.

Who out there is smarter than me? :-)
3 Weeks Ago #1
Share this Question
Share on Google+
5 Replies


cactusdata
P: 79
This can be solved using some generic queries.

First, create this tiny query and save it as Ten:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Abs([id] Mod 10) AS N
  2. FROM MSysObjects;
Next, this query to generate days of months (within the entire range of Date) and save it as MonthsDateRange:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS 
  2.     [DateStart] DateTime, 
  3.     [DateEnd] DateTime;
  4. SELECT 
  5.     [Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000 AS Id, 
  6.     [DateStart] AS DateStart, 
  7.     [DateEnd] AS DateEnd, 
  8.     DateAdd("m",[Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000,[DateStart]) AS DateMonth
  9. FROM 
  10.     Ten AS Ten_0, 
  11.     Ten AS Ten_1, 
  12.     Ten AS Ten_2, 
  13.     Ten AS Ten_3, 
  14.     Ten AS Ten_4, 
  15.     Ten AS Ten_5, 
  16.     Ten AS Ten_6
  17. WHERE 
  18.     (((DateAdd("m",[Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000,[DateStart]))<=DateAdd("m",DateDiff("m",[DateStart],DateAdd("d",-1,[DateEnd])),[DateStart])) 
  19.     AND 
  20.     ((Ten_0.N)<=DateDiff("m",[DateStart],[DateEnd])\1) 
  21.     AND 
  22.     ((Ten_1.N)<=DateDiff("m",[DateStart],[DateEnd])\10) 
  23.     AND 
  24.     ((Ten_2.N)<=DateDiff("m",[DateStart],[DateEnd])\100) 
  25.     AND 
  26.     ((Ten_3.N)<=DateDiff("m",[DateStart],[DateEnd])\1000) 
  27.     AND 
  28.     ((Ten_4.N)<=DateDiff("m",[DateStart],[DateEnd])\10000) 
  29.     AND 
  30.     ((Ten_5.N)<=DateDiff("m",[DateStart],[DateEnd])\100000) 
  31.     AND 
  32.     ((Ten_6.N)<=DateDiff("m",[DateStart],[DateEnd])\1000000));
  33.  
Finally, this query, DaysMonthsDateRange, will return the day count for each (partial) month:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     MonthsDateRange.Id, 
  3.     MonthsDateRange.DateStart, 
  4.     MonthsDateRange.DateEnd, 
  5.     Year([DateMonth]) AS [Year], 
  6.     Month([DateMonth]) AS [Month], 
  7.     IIf(DateDiff("m",[DateStart],[DateMonth])=0,[DateStart],DateSerial(Year([DateMonth]),Month([DateMonth]),1)) AS DateFrom, 
  8.     IIf(DateDiff("m",[DateEnd],[DateMonth])=0,[DateEnd],DateSerial(Year([DateMonth]),Month([DateMonth])+1,1)) AS DateTo, 
  9.     DateDiff("d",[DateFrom],[DateTo]) AS Days
  10. FROM 
  11.     MonthsDateRange;
Result:

2 Weeks Ago #2

100+
P: 259
Holy cow! That was WAY MORE difficult than I anticipated. Thank you so much for your detailed response. I will study it.

What are the "4" days in your result on line 2? I see what the 7 days are. But I'm not following the 4.
2 Weeks Ago #3

100+
P: 259
So I saved all the queries as you mentioned but how do I get MY existing query talking to these? I'm pretty confused because I use the field "Arrival" and then I use "FromDate" as a parameter to limit the time the query looks at. I'm not sure if DateStart and DateFrom are supposed to be replaced with Arrival/Departure or if those are new fields just used to make your queries run.

I'm stuck! (Ugh. So sorry.)
Attached Images
File Type: jpg OccupancyRate.jpg (43.4 KB, 9 views)
2 Weeks Ago #4

cactusdata
P: 79
That can be done in a similar way.

First, create a tiny query to alias your field names those of mine in the next query (I'm lazy) and save it as DateRanges:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     ID, 
  3.     Condo, 
  4.     Arrival AS DateStart, 
  5.     Departure AS DateEnd
  6. FROM 
  7.     Occupancy;
Next, this fits a default query of mine, DaysInMonthsOfDateRanges, a Cartesian (multiplying) query, here modified to include field Condo:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT 
  2.     DateRanges.Id, 
  3.     DateRanges.Condo,
  4.     DateRanges.DateStart, 
  5.     DateRanges.DateEnd, 
  6.     10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10) AS Factor, 
  7.     Year(DateAdd("m",[Factor],[DateStart])) AS [Year], 
  8.     Month(DateAdd("m",[Factor],[DateStart])) AS [Month], 
  9.     IIf(DateDiff("m",[DateStart],[DateEnd]) = 0,DateDiff("d",[DateStart],[DateEnd]),IIf(DateDiff("m",DateAdd("m",[Factor],[DateStart]),[DateEnd])=0,Day([DateEnd])-1,Day(DateSerial(Year(DateAdd("m",[Factor],[DateStart])),Month(DateAdd("m",[Factor],[DateStart]))+1,0))-IIf([Factor] = 0,Day([DateStart])-1,0))) AS Days, 
  10.     DateDiff("d",[DateStart],[DateEnd]) AS DaysTotal
  11. FROM 
  12.     MSysObjects AS Uno, 
  13.     MSysObjects AS Deca, 
  14.     DateRanges
  15. WHERE 
  16.     (((10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)) <= DateDiff("m",[DateStart],[DateEnd])));
Output:



Finally, create an aggregating query to finish it up:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     Id, 
  3.     Condo, 
  4.     DateStart As Arrival, 
  5.     DateEnd As Departure, 
  6.     DaysTotal As Nights
  7. FROM 
  8.     DaysInMonthsOfDateRanges
  9. GROUP BY 
  10.     Id, 
  11.     Condo, 
  12.     DateStart, 
  13.     DateEnd, 
  14.     DaysTotal
  15. ORDER BY 
  16.     DateStart;
Output:

2 Weeks Ago #5

Rabbit
Expert Mod 10K+
P: 12,401
An alternative method that avoids the dummy queries is to join the two tables on the overlapping date ranges. Then you can just calculate datediff on the larger of the 2 start dates to the smaller of the 2 end dates.

On a related note, you said your criteria was on the departure date. But make sure that's what you actually want because that will miss any record that falls within the report date range but ends outside the report date range.
2 Weeks Ago #6

Post your reply

Sign in to post your reply or Sign up for a free account.