[Solved] Recurring query problem
[Solved] Recurring query problem
How to use a calculated value in a secondary calculation, e,g
client, invoice date,term, duedate, overdue
the duedate is calculated via dateadd of invoice date and term
the overdue would be the calculation of currentdate-duedate, however queries do not like to use calculated values in a second calculation.
What would be a practical work around?
Thks
client, invoice date,term, duedate, overdue
the duedate is calculated via dateadd of invoice date and term
the overdue would be the calculation of currentdate-duedate, however queries do not like to use calculated values in a second calculation.
What would be a practical work around?
Thks
Last edited by gkick on Fri Jan 17, 2020 2:14 am, edited 1 time in total.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Recurring query problem
based upon the info provided this is not difficult.
you have not declared the unit size of "Term" so i have opted for months.
you have not declared the unit size of "Term" so i have opted for months.
Edit: 16 Jan 2020 17:13 added 'Datediff()' to calculate time (days) overdue. |
Code: Select all
select "Client", "InvoiceDate", "Term" "Term(months)",
"InvoiceDate" + "Term" month "DueDate",
case
when "InvoiceDate" + "Term" month < current_date then true
else false
end "Overdue"
,
case
when "InvoiceDate" + "Term" month < current_date then
datediff('DD', "InvoiceDate" + "Term" month, current_date)
end "DaysLate"
from "tMyTablName"
--to show only those clients who are overdue enable where clause
--where "InvoiceDate" + "Term" month < current_date
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: Recurring query problem
Hi Chris, thank you and sorry I need to clarify:
invoicedate is a datefield
term is type integer (number of days, 14, 30 or 90 etc)
duedate is calculated like DATEADD( 'day', "term", "adate" ) produces a 5 digit integer
next I want to calculate the number of days between CURDATE() and the duedate using datediff - which tells me the field duedate is unknown
Its more of a general thing, never managed to have a calculation based on another calculated field in the same query before
Thanks
invoicedate is a datefield
term is type integer (number of days, 14, 30 or 90 etc)
duedate is calculated like DATEADD( 'day', "term", "adate" ) produces a 5 digit integer
next I want to calculate the number of days between CURDATE() and the duedate using datediff - which tells me the field duedate is unknown
Its more of a general thing, never managed to have a calculation based on another calculated field in the same query before
Thanks
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Recurring query problem
i think that you did not notice the edit in my first post.
the code below is the same as above with "Term" month replaced by "term" day & table field names replaced by your field names.
the code below is the same as above with "Term" month replaced by "term" day & table field names replaced by your field names.
Code: Select all
select "client", "invoice date", "term" "term(days)",
"invoice date" + "term" day "DueDate",
case
when "invoice date" + "term" day < current_date then true
else false
end "Overdue",
case
when "invoice date" + "term" day < current_date then
datediff('DD', "invoice date" + "term" day, current_date)
end "DaysLate"
from "tMyTableName"
--to show only those clients who are overdue enable the where clause below
--where "invoice date" + "term" day < current_date
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: Recurring query problem
well, still end up with a syntax error message, possibly because of the "term" "term(days)" bit which I do not understand,
"term" is an existing field, "term(days)" I guess is an alias ?
"term" is an existing field, "term(days)" I guess is an alias ?
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Recurring query problem
yes "term(days)" is an alias.
did you activate the 'SQL' icon on the toolbar.
did you activate the 'SQL' icon on the toolbar.
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: Recurring query problem
yes running in sql direct mode, ahh maybe its because after the select its "term" day without brackets, will try and come back
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Recurring query problem
i just tested the code i posted with hsqldb 2.5.0 there are no errors.
the code "invoice date" + "term" day "DueDate" could be penned as DATEADD('dd', "term", "invoice date") AS "DueDate"
below is the table i created for the test. it contains 3 fields as indicated in your initial post plus a primary key.
1) paste the create block into menu:tools>SQL & hit execute.
2) repeat 1 with the insert block.
3) close window
4) hit table icon in base main window.
5) menu:View>Refresh Tables.
6) copy & paste the the code from my second post into the query window.
7) replace from "tMyTableName" with from "tOverdue".
8) activate SQL icon.
9) execute query.
if this works & it will then you will need to post the code you used which generated the syntax error.
the code "invoice date" + "term" day "DueDate" could be penned as DATEADD('dd', "term", "invoice date") AS "DueDate"
below is the table i created for the test. it contains 3 fields as indicated in your initial post plus a primary key.
1) paste the create block into menu:tools>SQL & hit execute.
2) repeat 1 with the insert block.
3) close window
4) hit table icon in base main window.
5) menu:View>Refresh Tables.
6) copy & paste the the code from my second post into the query window.
7) replace from "tMyTableName" with from "tOverdue".
8) activate SQL icon.
9) execute query.
if this works & it will then you will need to post the code you used which generated the syntax error.
Code: Select all
create table "tOverdue"(
id int generated by default as identity (start with 1) primary key,
"client" varchar(5) not null,
"invoice date" date not null,
"term" smallint not null
);
insert into "tOverdue" values
(default,'Jack','2019-11-19',14),
(default,'Jill','2019-11-05',30),
(default,'Jim','2019-11-01',90),
(default,'Jen','2019-11-03',90);
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: Recurring query problem
My humble apologies, was clicking the wrooong button, I owe you a pint or 2 of guiness, I guess that works with other datatypes as well. Thats great
Now I need to get this
into that
I opened a can of worms here
Anyhow thanks a lot and thank you for your patience
cherio
Now I need to get this
Code: Select all
SELECT "order_id","orderdate", "terms" "terms(days)",
"orderdate" + "terms" day "DueDate",
case
when "orderdate" + "terms" day < current_date then true
else false
end "Overdue",
case
when "orderdate" + "terms" day < current_date then
datediff('DD', "orderdate" + "terms" day, current_date)
end "DaysLate" FROM "orders"
Code: Select all
SELECT DISTINCT "orders"."order_id", "customer"."customer_name", "qrySubTotalBT"."SubTotal:", "qryTotalDiscountByOrderID"."Discount", "SubTotal:" + "Discount" "Total", "orders"."orderdate", "terms"."terms", DATEDIFF( 'dd', "orderdate", CURDATE( ) ), "orders"."datepaid", "orders"."received", "SubTotal:" + "Discount" - "received" "balance" FROM { oj "orders" "orders" LEFT OUTER JOIN "customer" "customer" ON "orders"."customer_id" = "customer"."customer_id" }, "qryTotalDiscountByOrderID" "qryTotalDiscountByOrderID", "qrySubTotalBT" "qrySubTotalBT", "terms" "terms" WHERE "qryTotalDiscountByOrderID"."order_id" = "qrySubTotalBT"."order_id" AND "qrySubTotalBT"."order_id" = "orders"."order_id" AND "orders"."terms" = "terms"."terms_id"
Anyhow thanks a lot and thank you for your patience
cherio
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: [Solved] Recurring query problem
we seem to have drifted away from the gist of the topic.
the points to remember are:
1) sql forbids the use of variables.
2) in relation to this topic we imitate a variable by repeating the formula(calculation) on which that variable would be based.
i have inserted the case when structures into your code.
in order to allow use of the parser i have replaced "orderdate" + "terms" day with dateadd('dd', "terms", "orderdate").
i hope it works but obviously am unable test it.
the points to remember are:
1) sql forbids the use of variables.
2) in relation to this topic we imitate a variable by repeating the formula(calculation) on which that variable would be based.
i have inserted the case when structures into your code.
in order to allow use of the parser i have replaced "orderdate" + "terms" day with dateadd('dd', "terms", "orderdate").
i hope it works but obviously am unable test it.
Code: Select all
SELECT DISTINCT
"orders"."order_id", "customer"."customer_name", "qrySubTotalBT"."SubTotal:",
"qryTotalDiscountByOrderID"."Discount",
"SubTotal:" + "Discount" "Total",
"orders"."orderdate", "terms"."terms",
DATEDIFF( 'dd', "orderdate", CURDATE( ) ),
"orders"."datepaid",
"orders"."received",
"SubTotal:" + "Discount" - "received" "balance",
dateadd('dd', "terms"."terms", "orders"."orderdate") "DueDate",
case
when dateadd('dd', "terms"."terms", "orders"."orderdate") < current_date then true
else false
end "Overdue",
case
when dateadd('dd', "terms"."terms", "orders"."orderdate") < current_date then
datediff('dd', dateadd('dd', "terms"."terms", "orders"."orderdate"), current_date)
end "DaysLate"
from "orders"
left join "customer"
ON "orders"."customer_id" = "customer"."customer_id"
join "qryTotalDiscountByOrderID"
on "qryTotalDiscountByOrderID"."order_id" = "orders"."order_id"
join "qrySubTotalBT"
on "qrySubTotalBT"."order_id" = "orders"."order_id"
join "terms"
on "orders"."terms" = "terms"."terms_id"
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