Count days separated by month

Copper Contributor

Hi

Say I have table with 2 dates (field1 & field2)
20/12/2020 & 10/01/2021

How would I show the datediff by month in a query
e.g.
Dec 20 = 12
Jan 21 = 10

Or if the dates were 28/08/2021 & 02/10/2021
Aug 21 = 4
Sep 21 = 30
Oct 21 = 2

Thank you

5 Replies
Sorry but I can't make any sense of the values in your examples.

@karenlorr_uk 

 

Sometimes it can help to explain the real world problem you need to solve, rather than trying to "simplify" by abstracting out just a few details. 

Like Colin, I can't see any logic in the examples provided as raw numbers.


What is the goal? Why are you counting months, or is it days? 

Thanks for providing enough context to help us offer suggestions.

Sorry if I didn’t give enough information – my bad.

I am creating a database for a friend who has a small hotel – I’m not getting paid, but have been promised a good dinner.


She has a number of rooms in her hotel.


The rooms rent out for different amounts – depending on demand.


She would like to know what the bookings were per month.


But this, obviously, has a problem as she may book out a room for (for example) 6 weeks, 2 weeks, 4 days, etc, etc. so on some occasion the bookings would go from one month to another.


So rather than using the start date (e.g. 25/12/2020 for X days) it would be better if she could know what the bookings where per month.


I hope this makes sense.


Using my original post’s details let say some booked a room from 28/08/2021 & 02/10/2021

She would want to know that the room was booked for:

Aug 21 = for 4 days

Sep 21 = for 30 days

Oct 21 = for 2 days


I have tried to create a query to do this but, let just say, I’m not have much luck.


Thanks

@karenlorr_uk 

 

Thank you. Now that does make sense.

You need to have each booking recorded one of three ways. 

Each booking could have a start date and and end date. I.e. a booking for three days starting on December 1st would have an end date of December 3. 

Each booking could be recorded on each of the days in the time span, i.e. a booking starting on December 1st would have entries for that booking on 1/12, 2/12 and 3/12

 

It sounds like your approach is to record the Start date and a second field with the length of stay.  That's why you are having a problem with the calculation across month boundaries where you need to know "bookings" per month. A booking could be in two different months, or even three if it is for more than 30 (or 31) days. 

 

I think you also need to pin down what you and your client mean by "bookings". Do you mean the number of times a room is booked for one day or longer? Or, do you mean the number of nights for which a room is booked? Slightly different meanings. Let's say 10 people each "book" the same room for two nights each. That's 10 "bookings" by the first definition, or 20 "bookings" by the second definition. Before we get to a specific suggestion, we do need to pin down that definition.

 

For me, the second approach mentioned above, one record per room per day it is booked, would probably be the easiest way to manage calculations like this, but it could be done with the other approach, as long as we know what we are counting exactly.

 

@karenlorr_uk 

You can use the query DaysMonthsDateRange found in \demos\Date.accdb at 

https://github.com/GustavBrock/VBA.Date 


DaysMonthsDateRange

Id        DateStart      DateEnd    Year Month DateFrom      DateTo     Days

028-08-202102-10-20212021828-08-202101-09-20214
128-08-202102-10-20212021901-09-202101-10-202130
228-08-202102-10-202120211001-10-202102-10-20211

 

However, an end date is normally not included in date interval calculations so, to have a count of 2 for September, you would need to add one day to the date of your Field2 and pass that to DateEnd.

 

 

 

DateEnd = DateAdd("d", 1, [Field2])