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

#### Alc

##### Registered User.
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
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.

##### CID VIP
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.
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

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

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``````

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");

Replies
7
Views
121
Replies
14
Views
98
Replies
15
Views
94
Replies
4
Views
109
Replies
4
Views
104