Calculate day count across months without double counting (1 Viewer)

Alc

Registered User.
Local time
Today, 04:03
Joined
Mar 23, 2007
Messages
2,407
I have a table with bookings in it.
Each booking has a Date Out, a Date In and the name of the person involved.
I want to produce a report of the number of days each person has a vehicle booked per month. Where I'm running into a problem where a booking crosses from one month to the next.

For example,
John Smith has bookings:
from April 18th to April 25th
from April 25th to May 2nd
from May 2nd to May 9th

The following query
Code:
SELECT tblvehiclelog.Driver, tblvehiclelog.[Date Out], tblvehiclelog.[Date In], DateDiff("d",[date out],[date in]) AS DaysBooked
FROM tblvehiclelog 
GROUP BY tblvehiclelog.Driver, tblvehiclelog.[Date Out], tblvehiclelog.[Date In], DateDiff("d",[date out],[date in])
HAVING  tblvehiclelog.[Date Out]>DateAdd("yyyy",-1,Date()) AND tblvehiclelog.[Date In]<=Now()
[Code]
Gives me three records for John, each of seven days.
However, summarizing by month on 'Date In' gives me:
April - 7 days
May - 14 days

summarizing by month on 'Date Out' gives me:
April - 14 days
May - 7 days

what I want is
April - 12 days
May - 9 days

For the life of me, I can't think of a way to write this as a query?

Any suggestions gratefully received, as always.
 

Minty

AWF VIP
Local time
Today, 08:03
Joined
Jul 26, 2013
Messages
10,355
I have a routine for doing this (for Holiday taken , but the same result required), but it's a stored procedure on our SQL server.
Let me see if I can put into "Access Speak", if nobody comes back beforehand.
 

isladogs

MVP / VIP
Local time
Today, 08:03
Joined
Jan 14, 2017
Messages
18,186
Suggest you use the fact that datetime values are stored in Access as double datatype numbers.
Or if you don't need the time, the dates are long integers e.g today is 13/04/2018 or 43203

So if you use CLng(DateIn) and CLng(DateOut), the difference between them is the total number of days.

If you like the idea, it can be modified to get the total number of days in each month by comparing with CLng value for the last day in the month and the first day of the following month.

Hope that all makes sense.
 

Alc

Registered User.
Local time
Today, 04:03
Joined
Mar 23, 2007
Messages
2,407
I have a routine for doing this (for Holiday taken , but the same result required), but it's a stored procedure on our SQL server.
Let me see if I can put into "Access Speak", if nobody comes back beforehand.

Thanks a lot. It didn't come up in the testing that was done but, as one should expect, the first person to use it live has loudly complained about it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:03
Joined
May 7, 2009
Messages
19,169
you can try this query:

SELECT tblVehicleLog.Driver, Format([Date In],"mmm yyyy") AS MonthYear, Sum(DateDiff("d",[Date In],[Date Out])) AS DaysBooked
FROM tblVehicleLog
GROUP BY tblVehicleLog.Driver, Format([Date In],"mmm yyyy"), Format([Date In],"yyyymm")
ORDER BY tblVehicleLog.Driver, Format([Date In],"yyyymm");
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:03
Joined
Feb 19, 2002
Messages
42,981
The best way to do this is to use a table of dates that has an entry for each date in the range you are working with. That will allow you to select rows from this table that fall between the start date and end date of a range and then sum the selected rows by month, week, year or whatever floats your boat.

You can build the date table on the fly based on the range of dates you want to analyze or make a perpetual table where every year you add another 365/366 rows for the new year.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:03
Joined
May 7, 2009
Messages
19,169
copy and paste this in a module:

Code:
Option Compare Database
Option Explicit

Public Function fnDateDiff(thisDriver As String, thisMonth As String) As Integer
    Dim counter As Integer
    Dim currMonth As String
    Dim currDate As Date
    Dim i As Integer
    With CurrentDb.OpenRecordset("select [date in], [date out] from tblVehicleLog " & _
                        "where driver=" & Chr(34) & thisDriver & Chr(34) & " " & _
                        "order by [date in] asc;", dbOpenSnapshot)
                        
        If Not (.BOF And .EOF) Then
            .MoveFirst
            While Not .EOF
                For i = 1 To DateDiff("d", ![date in], ![date out])
                    If Month(DateAdd("d", i, ![date in])) = thisMonth Then
                        If DateAdd("d", i, ![date in]) > currDate Then
                            counter = counter + 1
                            currDate = DateAdd("d", i, ![date in])
                        End If
                    End If
                Next
                .MoveNext
            Wend
        End If
        .Close
    End With
    fnDateDiff = counter
End Function

then your query will be:

SELECT tblVehicleLog.Driver, Format([date in],"mmm yyyy") AS MonthYear, fnDateDiff([Driver],Month([Date In])) AS DaysBooked
FROM tblVehicleLog
GROUP BY tblVehicleLog.Driver, Format([date in],"mmm yyyy"), fnDateDiff([Driver],Month([Date In])), Format([date in],"yyyymm")
ORDER BY tblVehicleLog.Driver, Format([date in],"yyyymm");
 

Users who are viewing this thread

Top Bottom