[Solved] Make report with grouping by month

[artem78]

Hello.

I have the table which holds rental period dates for some object. Here is example:

Снимок экрана в 2023-11-24 18-41-56.png (6.04 KiB) Viewed 1992 times

I need to make report, which should represents how many days object was occupied each month. For my sample data result should be like this:

Снимок экрана в 2023-11-24 18-44-34.png (4.29 KiB) Viewed 1992 times

Note that occupied period might starts and ends in different months.

Earlier I solved similar task in Sqlite3 using SQL query with WITH RECURSIVE sentence. Unfortunately hsqldb 1.8 used in LibreOffice not supports it (only 2.x version). I tried to upgrade database engine using HyperSQLOOo extension, but with no luck.

Demo db:

rent db demo.odb

(3.58 KiB) Downloaded 192 times

[Villeroy]

My way to convert HSQL1 to HSQL2: viewtopic.php?f=21&t=86071

Nevertheless, there is a work-around if you accept a calendar table with dates.

[Nick N,]

Hi,

If it is of interest to you, please find enclosed a partial solution. I.E. you might by yourself select DATE format to suit your needs.

Regards

[chrisb]

hello artem78,

i am assuming that there is never more than one month between "begin" and "end".
i added the table "tMonths" because we need to know the maximum number of days per month.
i have also catered for leap years.

Rent_chrisb.odb

(5.2 KiB) Downloaded 260 times

[Nick N,]

@ Mr. Villeroy

Thank you Sir. I appreciate your concern.

Regards

Nick

[artem78]

i am assuming that there is never more than one month between "begin" and "end".

No. Possible more than one month. Minumum rent period is one day, maximum not limited.

[Nick N,]

Prastitié Artem,

The reason why i did not do a MONTH query is just because of above concern. I.E. should you wish to know a result in financial terms, I would like you to consider a period divided by DAYS. Else the matter could become potentially COMPLICATED.

Dobrie Dien

Anakin

[chrisb]

maximum not limited

what does that mean 1 year, 5 years, 10 years?

it's not nice to move the goal posts, you should have made this clear in the initial post & example.

i made a small mistake in my first attachment regarding leap years which will result in missing records.
this attachment fixes that error & handles gaps of up to 12 months
check out the query result for Nov 2015 to Mar 2016 which relates to the record in the table "Rents" where ID = 7.

[artem78]

Nevertheless, there is a work-around if you accept a calendar table with dates.

Yes, this is completely acceptable. I tried your solution and with small modification seems it works as what I need.

Code: Select all

SELECT
	"M", "Y",
	SUM( "Count" ) AS "Count"
FROM
	(
	SELECT
		"ID", "Y", "M",
		COUNT( "D" ) AS "Count"
	FROM (
		SELECT
			"R"."ID", "CAL"."D",
			MONTH( "CAL"."D" ) AS "M",
			YEAR( "CAL"."D" ) AS "Y"
		FROM "CAL", "Rents" AS "R"
		WHERE "CAL"."D" BETWEEN "R"."begin" AND "R"."end"
		)
	GROUP BY "ID", "Y", "M"
	)
GROUP BY "Y", "M"

Thanks, Villeroy!

[artem78]

maximum not limited

what does that mean 1 year, 5 years, 10 years?

Usually rent period lies in the range from several days to several weeks. In rare cases longer.

[Nick N,]

Hello,

I beg your pardon, but I did try to keep information as elementary as possible.

Glad to learn that you achieved your task.

Regards

Nick

[Villeroy]

Usually rent period lies in the range from several days to several weeks. In rare cases longer.

My solution works with any period of time.