[Solved] SQL Query Datediff Displaying Multiple Fields In Month

[mkeeper]

I have a sample database (HSQLDB) to keep prepayments expenses. Normally the prepaid expense is for a year. I have a query to calculate between start date and end date of the expenses and would like to know the remaining expenses amount in month (calculated value) by displaying in field for twelve months (12 fields).

However, several expenses are less than a year. I am looking for a query that calculated results would only display monthly amount in the certain period field instead of all twelve fields. Here is my query:-

SELECT "expdescp", "startdate", "enddate", "amount",
DATEDIFF('mm', "startdate", "enddate") +1 AS "MM_Month_inclusive",
"amount" / (DATEDIFF('mm', "startdate", "enddate") +1) AS "Per Month",
DATEDIFF('month', CURRENT_DATE, "enddate") AS "Remaining mm",
"amount" / (DATEDIFF('mm', "startdate", "enddate") +1) AS "t1",
"amount" / (DATEDIFF('mm', "startdate", "enddate") +1) AS "t2",
"amount" / (DATEDIFF('mm', "startdate", "enddate") +1) AS "t3",
"amount" / (DATEDIFF('mm', "startdate", "enddate") +1) AS "t4",
"amount" / (DATEDIFF('mm', "startdate", "enddate") +1) AS "t5",
"amount" / (DATEDIFF('mm', "startdate", "enddate") +1) AS "t6",
"amount" / (DATEDIFF('mm', "startdate", "enddate") +1) AS "t7",
"amount" / (DATEDIFF('mm', "startdate", "enddate") +1) AS "t8",
"amount" / (DATEDIFF('mm', "startdate", "enddate") +1) AS "t9",
"amount" / (DATEDIFF('mm', "startdate", "enddate") +1) AS "t10",
"amount" / (DATEDIFF('mm', "startdate", "enddate") +1) AS "t11",
"amount" / (DATEDIFF('mm', "startdate", "enddate") +1) AS "t12"
FROM "Prepaid"

I am new to SQL code and found CASEWHEN only have one result or is there some other way. Attached the database file. Thanks in advance.

[Villeroy]

Pull the query from the data source window into a spreadsheet and use the OFFSET function.
[Tutorial] Using registered datasources in Calc

T2: =SUM(OFFSET($H2;0;0;1;$G2))
calculates the sum of the preceeding 12 columns resized to the remaining months in G2

Some simple but clumsy SQL:

Code: Select all

SELECT "qry_datediff_remaining_periods".*, 
"t1"* casewhen("Remaining mm">0,1,0) +
"t2"* casewhen("Remaining mm">1,1,0) +
"t3"* casewhen("Remaining mm">2,1,0) +
"t4"* casewhen("Remaining mm">3,1,0) +
"t5"* casewhen("Remaining mm">4,1,0) +
"t6"* casewhen("Remaining mm">5,1,0) +
"t7"* casewhen("Remaining mm">6,1,0) +
"t8"* casewhen("Remaining mm">7,1,0) +
"t9"* casewhen("Remaining mm">8,1,0) +
"t10"* casewhen("Remaining mm">9,1,0) +
"t11"* casewhen("Remaining mm">10,1,0) +
"t12"* casewhen("Remaining mm">11,1,0) AS "Sum Payments" 
FROM "qry_datediff_remaining_periods"

[Villeroy]

Another more "SQLish" approach where I added a table "Months" with the numbers and names of the 12 months. I use such a table to look up German month names. In this case we only need the sequence of numbers 1-12. See "Query2". For better understanding, test the inner SELECT statement which generates a simple 2-column record set which is grouped and summed by the outer SELECT.

[chrisb]

Edit:  the original attachment was made using libreoffice, replaced with openoffice compatible attachment (report was not editable).

hello mkeeper,

it's important to understand how DATEDIFF calculates months in embedded HSQL:

datediff('mm', '2022-01-10', '2022-12-01') = 11 (the number of fully completed months is in fact 10)
and
datediff('mm', '2022-01-10', '2022-12-10') = 11 (the number of fully completed months is indeed 11)

for your purposes you need to calculate the number of whole months completed.

note: the function returns (YEAR(end_date) - YEAR(start_date)) * 12 + MONTH(end_date) - MONTH(start_date), the value of day is ignored.

when dayofmonth(start_date) is <= dayofmonth(end_date) the result is accurate, the number of fully completed months is returned.
BUT
when dayofmonth(start_date) is greater than dayofmonth(end_date) then 1 must be subtracted from the result.

using a database it's easy to summarise your data.
i added the table "tPrepaid2" to your db.
i replaced the fields "startdate" & "enddate" with "FirstDueDate" & "LastDueDate" respectively.
i added a dummy record where "expdescp" = 'DUMMY' so that i could test the query "qPrepaid2" with different dates.
i added a report which uses the query "qPrepaid2" as its data-source.

Prepayment_OpenOffice.odb

(42.57 KiB) Downloaded 191 times

[mkeeper]

Hello Villeroy,

I tried the OFFSET function in the spreadsheet and it is worked

The clumsy SQL is similar to Prepayment_Origin2.odb. The source data has been changed from Jan 01, 2023 till Dec 31, 2023 to Dec 31, 2022 till Dec 30, 2023. It became thirteen months period instead of twelve months period. This was brought up by chrisb on Datediif calculates months in embedded HSQL.

The main reason I intend to switch Prepayment Schedule from spreadsheet to Base is to get consistency. Auditor would ask you some prepaid expense invoice dated in January 2023 regardless of date, you started to charge out:-

a) in February 2023, every month, and ending in January 2024.

b) another prepaid expenses dated in January 2023, you started to charge out in January 2023 and ending in December 2023.

and also to calculate or summaries each prepaid expenses line (about 50 rows of expenses) by month or vertically calculation. I will leave this from this thread and let Report Builder does it.

Hello chrisb,

Your solution is great. Thank you! I need to add one more field “Invoice date” before the “FirstDueDate” and “LastDueDate” to enter the date correctly such as invoice dated Jan 24, 2023, “FirstDueDate” will be Feb 23, 2023. Now there is a consistency policy to expense out.

Once again, Thank you so much.