Data between two dates crossing over months

[Andrea1223]

Hi.

I have booking dates (UK format) and need to produce a report for number of days per month for these bookings.   Eg:  01/01/2020 - 04/01/2020 = 4 days  23/1/2020 - 29/1/2020 = 7 days; total for January = 11 days.

The problem is that if that second booking was for 12 days (23/1/2020 - 3/2/2022), I need it to show 7 days in January and 3 days in February.

I have attached a sample of the data and resulting report.  The most obvious occurrence of the problem is July which shows 35 days booked but it needs to show the 28 days booked in July and 7 In August.

Any advice would be much appreciated.

[#2 · Accepted solution]

@Andrea1223 

i import your query to a table.

i also made some tables (ending with Z), and some "related" queries (numbered according to the sequence i made them).

use the form to show the report.

[#3]

Thanks @arnel_gpp. It took me a little while to get my head around your workings (I don't use anything that I don't fully understand) but I've got it now and the solution works. Thank you.