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

[DanicaDear]

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? :-)

[cactusdata]

This can be solved using some generic queries.

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

SELECT DISTINCT Abs([id] Mod 10) AS N
FROM MSysObjects;

Next, this query to generate days of months (within the entire range of Date) and save it as MonthsDateRange:

PARAMETERS 
    [DateStart] DateTime, 
    [DateEnd] DateTime;
SELECT 
    [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, 
    [DateStart] AS DateStart, 
    [DateEnd] AS DateEnd, 
    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
FROM 
    Ten AS Ten_0, 
    Ten AS Ten_1, 
    Ten AS Ten_2, 
    Ten AS Ten_3, 
    Ten AS Ten_4, 
    Ten AS Ten_5, 
    Ten AS Ten_6
WHERE 
    (((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])) 
    AND 
    ((Ten_0.N)<=DateDiff("m",[DateStart],[DateEnd])\1) 
    AND 
    ((Ten_1.N)<=DateDiff("m",[DateStart],[DateEnd])\10) 
    AND 
    ((Ten_2.N)<=DateDiff("m",[DateStart],[DateEnd])\100) 
    AND 
    ((Ten_3.N)<=DateDiff("m",[DateStart],[DateEnd])\1000) 
    AND 
    ((Ten_4.N)<=DateDiff("m",[DateStart],[DateEnd])\10000) 
    AND 
    ((Ten_5.N)<=DateDiff("m",[DateStart],[DateEnd])\100000) 
    AND 
    ((Ten_6.N)<=DateDiff("m",[DateStart],[DateEnd])\1000000));
 

Finally, this query, DaysMonthsDateRange, will return the day count for each (partial) month:

SELECT 
    MonthsDateRange.Id, 
    MonthsDateRange.DateStart, 
    MonthsDateRange.DateEnd, 
    Year([DateMonth]) AS [Year], 
    Month([DateMonth]) AS [Month], 
    IIf(DateDiff("m",[DateStart],[DateMonth])=0,[DateStart],DateSerial(Year([DateMonth]),Month([DateMonth]),1)) AS DateFrom, 
    IIf(DateDiff("m",[DateEnd],[DateMonth])=0,[DateEnd],DateSerial(Year([DateMonth]),Month([DateMonth])+1,1)) AS DateTo, 
    DateDiff("d",[DateFrom],[DateTo]) AS Days
FROM 
    MonthsDateRange;

Result:

[DanicaDear]

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.

[DanicaDear]

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.)

[cactusdata]

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:

SELECT 
    ID, 
    Condo, 
    Arrival AS DateStart, 
    Departure AS DateEnd
FROM 
    Occupancy;

Next, this fits a default query of mine, DaysInMonthsOfDateRanges, a Cartesian (multiplying) query, here modified to include field Condo:

SELECT DISTINCT 
    DateRanges.Id, 
    DateRanges.Condo,
    DateRanges.DateStart, 
    DateRanges.DateEnd, 
    10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10) AS Factor, 
    Year(DateAdd("m",[Factor],[DateStart])) AS [Year], 
    Month(DateAdd("m",[Factor],[DateStart])) AS [Month], 
    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, 
    DateDiff("d",[DateStart],[DateEnd]) AS DaysTotal
FROM 
    MSysObjects AS Uno, 
    MSysObjects AS Deca, 
    DateRanges
WHERE 
    (((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:

SELECT 
    Id, 
    Condo, 
    DateStart As Arrival, 
    DateEnd As Departure, 
    DaysTotal As Nights
FROM 
    DaysInMonthsOfDateRanges
GROUP BY 
    Id, 
    Condo, 
    DateStart, 
    DateEnd, 
    DaysTotal
ORDER BY 
    DateStart;

Output:

[Rabbit]

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.

[NeoPa]

Hi Danica (My dear).

This may help some (Time Interval Overlapping (MS Access 2003)).

Do let us know if you've managed to get a solution for this :-)

[DanicaDear]

NeoPa! I'm so glad to hear from you again!! I hope you are doing well. I had given up on this but will study it again perhaps. Thank you for the additional reference.

Sometimes I get in over my head. It doesn't take much, really. haha.
I'll post back if/when I get this worked out. I think the solutions here have been excellent...I just gave up to soon.

[NeoPa]

The solutions have been posted by two very clever people. There's no question about that.

Just so you know, I'm still happy to receive a call from you if ever you find you're stuck with something. It's been a very long time since we actually spoke. Send me a message if you'd like to.