Page 1 of 1
[Solved] Make report with grouping by month
Posted: Mon Nov 27, 2023 2:19 pm
by 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 10039 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 10039 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:
Re: Make report with grouping by month
Posted: Mon Nov 27, 2023 6:58 pm
by 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.
Re: Make report with grouping by month
Posted: Mon Nov 27, 2023 7:53 pm
by 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
Re: Make report with grouping by month
Posted: Tue Nov 28, 2023 11:32 am
by 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.
Re: Make report with grouping by month
Posted: Tue Nov 28, 2023 11:52 am
by Nick N,
@ Mr. Villeroy
Thank you Sir. I appreciate your concern.
Regards
Nick
Re: Make report with grouping by month
Posted: Tue Nov 28, 2023 7:58 pm
by artem78
chrisb wrote: ↑Tue Nov 28, 2023 11:32 am
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.
Re: Make report with grouping by month
Posted: Tue Nov 28, 2023 8:08 pm
by 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
Re: Make report with grouping by month
Posted: Wed Nov 29, 2023 1:19 am
by chrisb
artem78 wrote: ↑Tue Nov 28, 2023 7:58 pm
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.
Re: Make report with grouping by month
Posted: Fri Dec 01, 2023 12:59 pm
by artem78
Villeroy wrote: ↑Mon Nov 27, 2023 6:58 pm
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!
Re: Make report with grouping by month
Posted: Fri Dec 01, 2023 1:05 pm
by artem78
chrisb wrote: ↑Wed Nov 29, 2023 1:19 am
artem78 wrote: ↑Tue Nov 28, 2023 7:58 pm
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.
Re: Make report with grouping by month
Posted: Fri Dec 01, 2023 1:48 pm
by 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
Re: Make report with grouping by month
Posted: Sun Dec 03, 2023 12:31 am
by Villeroy
artem78 wrote: ↑Fri Dec 01, 2023 1:05 pm
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.