[Solved] Populating filter table with date functions

Creating tables and queries

[Solved] Populating filter table with date functions

Postby gkick » Wed Mar 18, 2020 6:36 am

Hi there,

In order to create a filter table for multiple report generation filtering by year, month, quarter, week by way of list boxes I need some conversion since the normal query construct would be something like YEAR(ldate), MONTH(ldate) , Employee etc.

Can not figure out how to get this - SELECT QUARTER( "ldate" ) AS "Month" FROM "tbllog" GROUP BY QUARTER( "ldate" ) into a filter table.

The original source queries for reports all have varied parameters and work well but the trouble is in my split db HSQL2.5) once I run the report the parameter box minimizes the current form and the parameter prompt sits on top of the db container, the report runs ok and once closed returns control to the container the form still being minimized. This behavior seems to be the same on LO 6.2 and 6.3 or perhaps its just my machine. Funny enough this does not happen with embedded dbs.

Now for the filter table I run into problems with date functions, so would this require a two way datatype conversion and then add the rest of the original query for report generation?

Anyone done something similar?
Look forward to any pointers, thanks
Last edited by Hagar Delest on Sat Mar 21, 2020 4:21 pm, edited 1 time in total.
Reason: tagged solved.
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 207
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Populating filter table with date functions

Postby Villeroy » Wed Mar 18, 2020 1:55 pm

Dump the quarters into a list box so the selected quarter is written into the filter table.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27870
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Populating filter table with date functions

Postby F3K Total » Wed Mar 18, 2020 4:11 pm

find attached an example ...
Query:
Code: Select all   Expand viewCollapse view
SELECT
    MONTHNAME( "DATE" ) "MONTH",
    SUM( "NUMBER" ) "SUM"
FROM
    "DATA"
WHERE
    QUARTER( "DATE" ) = ( SELECT "F_QUARTER" FROM "FILTER" ) OR ( SELECT "F_QUARTER" FROM "FILTER" ) IS NULL
GROUP BY
    MONTH( "DATE" )
ORDER BY
    MONTH( "DATE" )
Attachments
FilterQuarters.odb
(40.12 KiB) Downloaded 7 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 961
Joined: Fri Dec 16, 2011 8:20 pm

Re: Populating filter table with date functions

Postby gkick » Thu Mar 19, 2020 3:25 am

Thank you gentlemen, much appreciated!
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 207
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Populating filter table with date functions

Postby gkick » Thu Mar 19, 2020 6:53 am

Oops, was happy to early...

The datebits in the listboxes for Year,Quarter,Month,Week are all integers, at one stage the report query complains about ambiguity and then about syntax error. Do I need to convert the value of the filter table using CAST ?

Thanks
Attachments
Oops.PNG
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 207
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Populating filter table with date functions

Postby F3K Total » Thu Mar 19, 2020 4:53 pm

Nobody can see the SQL-statment please post the query in SQL-Mode.
You can paste as "Code" ... as i did ...
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 961
Joined: Fri Dec 16, 2011 8:20 pm

Re: Populating filter table with date functions

Postby gkick » Thu Mar 19, 2020 5:56 pm

Report.PNG
FilterSelection.PNG
Thank you F3K Total,

Guess I have opened a can of worms here.
The database contains a number of reports like the attached one in all of which the criterias are hardcoded. Apart from getting the alternatives filters to work I guess I need to make them cascading. So from the attached report for example the if 2020 is selected only q1 and q1 should be available as there is no data for 3 and 4

The original working code without the filter condition as I can not switch to SQL anymore due to error

Code: Select all   Expand viewCollapse view
SELECT QUARTER( "ldate" ) "Quarter", SUM( DATEDIFF( 'minute', "signin", "signout" ) / 60.00 ) "Hours", SUM( DATEDIFF( 'minute', "signin", "signout" ) / 60.00 - CASE WHEN DATEDIFF( 'minute', "signin", "signout" ) / 60.00 < "STH" THEN 0 ELSE DATEDIFF( 'minute', "signin", "signout" ) / 60.00 - "STH" END ) "ActHrs", SUM( CASE WHEN DATEDIFF( 'minute', "signin", "signout" ) / 60.00 < "STH" THEN 0 ELSE DATEDIFF( 'minute', "signin", "signout" ) / 60.00 - "STH" END ) "NSH", SUM( CASE WHEN DATEDIFF( 'minute', "signin", "signout" ) / 60.00 < "STH" THEN "StandardRate" * DATEDIFF( 'minute', "signin", "signout" ) / 60.00 ELSE "StandardRate" * "STH" END ) "Pay", SUM( CASE WHEN DATEDIFF( 'minute', "signin", "signout" ) / 60.00 < "STH" THEN 0 ELSE DATEDIFF( 'minute', "signin", "signout" ) / 60.00 - "STH" END * "OverTimeRate" ) "OT", SUM( CASE WHEN DATEDIFF( 'minute', "signin", "signout" ) / 60.00 < "STH" THEN "StandardRate" * DATEDIFF( 'minute', "signin", "signout" ) / 60.00 ELSE "StandardRate" * "STH" END + CASE WHEN DATEDIFF( 'minute', "signin", "signout" ) / 60.00 < "STH" THEN 0 ELSE DATEDIFF( 'minute', "signin", "signout" ) / 60.00 - "STH" END * "OverTimeRate" ) "Remuneration" FROM "tbllog", "tblPax", "tbldepartment", "tblschedule" WHERE "tbllog"."uid" = "tblPax"."signin" AND "tblPax"."department" = "tbldepartment"."did" AND "tblPax"."schedule" = "tblschedule"."sid" AND YEAR( "ldate" ) = :Parameter_EnterYearFourDigits GROUP BY QUARTER( "ldate" ) ORDER BY "Quarter" ASC


And here are the filter listboxes

Hope it all makes sense, Greetings from Chile

GK
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 207
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Populating filter table with date functions

Postby F3K Total » Thu Mar 19, 2020 7:49 pm

Heard,
better would be the database itself.
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 961
Joined: Fri Dec 16, 2011 8:20 pm

Re: Populating filter table with date functions

Postby Villeroy » Thu Mar 19, 2020 8:47 pm

Listbox with linked field = Filter.INT1 and bound field = 0
Code: Select all   Expand viewCollapse view
SELECT DISTINCT Year("Table"."Date") AS "Y"
FROM "Table"
ORDER BY "Y" DESC

The list box shows all possible years in descending order
2020
2019
2018
etc
writes the selected year into Filter.INT1 WHERE Filter.ID=1 or something
and this should select the report data:
Code: Select all   Expand viewCollapse view
SELECT "Filter"."INT1" AS "Y", QUARTER("Table"."Date") AS "Q", all the other stuff
FROM "Filter", "Table", several joined tables
WHERE Year("DateField") = "Filter"."INT1" AND "Filter"."ID"=1
GROUP BY "Filter"."INT1", QUARTER("Table"."Date")

this selects all required data matching the entered year number in Filter.INT1 at Filter.ID=1 for each year (only one) and each quarter (1 to 4).

See query "qQuarterOfYear" in the attached database demo. I did not create any form, just entered the year number into row #6 of the filter table.
Attachments
FilterData_t101391.odb
qQuarterOfYear
(101.85 KiB) Downloaded 4 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27870
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Populating filter table with date functions

Postby gkick » Fri Mar 20, 2020 1:08 am

Sorry for the delay, some internet probs here.

Here is the link

https://www.dropbox.com/s/haqi5g3df64h6 ... s.rar?dl=0


Log in as SA no pw and wait, may take a bit for the admin form to open...

close the reminder form and set the zoom to 90 depending on screen resolution
The admin form has the listboxes for the filter table

All sort of unfinished crude work in progress, trying to replicate an application I did some 20 odd years ago in access2002/vba
Thanks for having a look.
Sooo much more to learn

Test data is available for 2019, only little for 2020 Jan Feb
other logins are max pw moriz, fred pw bloggs
, gkick pw winzi

week 52 2019 has some useful data

cheers
GK

@Villeroy, thanks for the example, will study it tonight
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 207
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Populating filter table with date functions

Postby Villeroy » Fri Mar 20, 2020 2:22 pm

I added a filter record with id=1 and entered 2019 into the year field (just to separate this filter case from any other filter)
I copied your above parameter query and removed the parameter condition
added the "Filter" table to the FROM clause.
added "Filter"."id"=1 AND YEAR( "ldate" ) = "Filter"."Year" to the WHERE clause replacing the parameter
added "Filter"."Year" to the selected columns and to the GROUPED BY clause.
Created a quick report with 3 or 4 clicks.
It looks like this:
Bildschirmfoto von 2020-03-20 13-11-41.png

gkick wrote:So from the attached report for example the if 2020 is selected only q1 and q1 should be available as there is no data for 3 and 4

and when I write 2020 as year number in the filter table, it only shows one row.
----------
The resulting query:
Code: Select all   Expand viewCollapse view
SELECT "Filter"."Year", QUARTER( "ldate" ) "Quarter", SUM( DATEDIFF( 'minute', "signin", "signout" ) / 60.00 ) "Hours", SUM( DATEDIFF( 'minute', "signin", "signout" ) / 60.00 - CASE WHEN DATEDIFF( 'minute', "signin", "signout" ) / 60.00 < "STH" THEN 0 ELSE DATEDIFF( 'minute', "signin", "signout" ) / 60.00 - "STH" END ) "ActHrs", SUM( CASE WHEN DATEDIFF( 'minute', "signin", "signout" ) / 60.00 < "STH" THEN 0 ELSE DATEDIFF( 'minute', "signin", "signout" ) / 60.00 - "STH" END ) "NSH", SUM( CASE WHEN DATEDIFF( 'minute', "signin", "signout" ) / 60.00 < "STH" THEN "StandardRate" * DATEDIFF( 'minute', "signin", "signout" ) / 60.00 ELSE "StandardRate" * "STH" END ) "Pay", SUM( CASE WHEN DATEDIFF( 'minute', "signin", "signout" ) / 60.00 < "STH" THEN 0 ELSE DATEDIFF( 'minute', "signin", "signout" ) / 60.00 - "STH" END * "OverTimeRate" ) "OT", SUM( CASE WHEN DATEDIFF( 'minute', "signin", "signout" ) / 60.00 < "STH" THEN "StandardRate" * DATEDIFF( 'minute', "signin", "signout" ) / 60.00 ELSE "StandardRate" * "STH" END + CASE WHEN DATEDIFF( 'minute', "signin", "signout" ) / 60.00 < "STH" THEN 0 ELSE DATEDIFF( 'minute', "signin", "signout" ) / 60.00 - "STH" END * "OverTimeRate" ) "Remuneration"
FROM "tbllog", "tblPax", "tbldepartment", "tblschedule", "Filter"
WHERE "tbllog"."uid" = "tblPax"."signin" AND "tblPax"."department" = "tbldepartment"."did" AND "tblPax"."schedule" = "tblschedule"."sid" AND "Filter"."id" = 1 AND YEAR( "ldate" ) = "Filter"."Year"
GROUP BY "Filter"."Year", QUARTER( "ldate" )
ORDER BY "Quarter" ASC
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27870
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Solved]Re: Populating filter table with date functions

Postby gkick » Fri Mar 20, 2020 11:57 pm

Thank you for this, picking up a lot from these examples.
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 207
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: [Solved] Populating filter table with date functions

Postby chrisb » Sat Mar 21, 2020 7:29 pm

@gkick,
i downloaded your db (hsqldb 2.5.0) & executed the code you posted on Thu Mar 19, 2020 5:56 pm.
it was noticeable that the fields "Hours", "ActHrs", "NSH" & "OT" were all displayed as integers.
i right clicked the field headers in turn & formatted as decimal with two decimal places.
all four fields showed as xxx.00.
you used the 'DATEDIFF' function like so: SUM( DATEDIFF( 'minute', "signin", "signout" ) / 60.00 ) AS "Hours"
this works ok in hsqldb 1.8.0.10 but not with hsqldb 2.5.0 which indicates that the 'DATEDIFF' function has been updated in some version of hsqldb 2.x (hardly surprising).

using hsqldb 2.5.0:
100 / 1.5 = 100
100 / 1.33 = 100
100 / 0.5 = ERROR DIVISION BY ZERO
so it's obvious that when dividing an integer by a decimal anything behind the decimal point is ignored.

i have always used 1.00 * integer / some value. e.g. in your case: 1.00 * datediff('minute', "signin", "signout") / 60 which works well.

i am sorry to be the bearer of this bad news but it's better to know sooner rather than later.
open office 4.1.7 & LibreOffice 6.3.2.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 209
Joined: Mon Jun 07, 2010 4:16 pm

Re: [Solved] Populating filter table with date functions

Postby gkick » Sat Mar 21, 2020 9:57 pm

@chris,

Thank you for letting me know. After upgrading the driver from 2.3.2 to 2.5 I noticed something was different but could not pin it down. Now if I switch the driver back to 2.3.2 I can no longer log in. Oh well a revamp is in order. Also thanks for the work around!

cheers

GK
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 207
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest