[Solved] SQL Query Datediff Displaying Multiple Fields In Month
Posted: Sat Mar 04, 2023 8:21 am
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.
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.