I am trying to calculate occupancy in a hotel for every month in a one year period. That means scanning thru thousands of booking records.
I know how to do this in pure Xojo code, and I already did it. (maybe not the most efficient way, iterating thru the records, but it works fine)
I am wondering now, if it is possible to do it in pure SQLite, to make it even faster. But I am lost to how the query should be... (probably using a recursive query ?)
The first issue is that the scheme of the "bookings" table (I'm simplifying here) is BookingId integer, DateIn date, DateOut date, so I don't have A ROW for every date but instead I have a field for the dateIN and a field for the dateOut.
I know how to calculate the difference (in days) between two dates, but the problem here is that (obviously) the time span (dateIN-dateOut) of every booking may exceed one month even two or three... so I need to sum the right amount of nights for every month in the whole booking period of every record.
Finally (as I did it in xojo) what I would like is to have (in the best scenario) a sort of dictionary YearMonth:NightsTaken..
This is way more than I can handle with SQL, but is it even possible ?