[Solved] Make report with grouping by month
[Solved] Make report with grouping by month
Hello.
I have the table which holds rental period dates for some object. Here is example:
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:
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:
I have the table which holds rental period dates for some object. Here is example:
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:
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:
Last edited by robleyd on Sun Dec 03, 2023 1:31 am, edited 2 times in total.
Reason: Add green tick
Reason: Add green tick
OpenOffice 4.1.14 on Windows 7 and LibreOffice 6.4.7.2 on Linux Mint 20.3
Re: Make report with grouping by month
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.
Nevertheless, there is a work-around if you accept a calendar table with dates.
- Attachments
-
- rent db demo.odb
- (50.34 KiB) Downloaded 249 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Make report with grouping by month
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
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
- Attachments
-
- day rental report.png (22.72 KiB) Viewed 1976 times
-
- rent db demo-rev.1.odb
- (5.21 KiB) Downloaded 264 times
Libre Office 6.0.7 on Ubuntu 18.04
Re: Make report with grouping by month
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.
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.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Re: Make report with grouping by month
@ Mr. Villeroy
Thank you Sir. I appreciate your concern.
Regards
Nick
Thank you Sir. I appreciate your concern.
Regards
Nick
Libre Office 6.0.7 on Ubuntu 18.04
Re: Make report with grouping by month
No. Possible more than one month. Minumum rent period is one day, maximum not limited.
OpenOffice 4.1.14 on Windows 7 and LibreOffice 6.4.7.2 on Linux Mint 20.3
Re: Make report with grouping by month
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
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
Libre Office 6.0.7 on Ubuntu 18.04
Re: Make report with grouping by month
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.
- Attachments
-
- Rent2_chrisb.odb
- (4.82 KiB) Downloaded 265 times
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Re: Make report with grouping by month
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"
OpenOffice 4.1.14 on Windows 7 and LibreOffice 6.4.7.2 on Linux Mint 20.3
Re: Make report with grouping by month
Usually rent period lies in the range from several days to several weeks. In rare cases longer.
OpenOffice 4.1.14 on Windows 7 and LibreOffice 6.4.7.2 on Linux Mint 20.3
Re: Make report with grouping by month
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
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
- Attachments
-
- elementary example.png (21.67 KiB) Viewed 1360 times
Libre Office 6.0.7 on Ubuntu 18.04
Re: Make report with grouping by month
My solution works with any period of time.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice