[Solved] Populating filter table with date functions

[gkick]

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

[Villeroy]

Dump the quarters into a list box so the selected quarter is written into the filter table.

[F3K Total]

find attached an example ...
Query:

Code: Select all

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" )

[gkick]

Thank you gentlemen, much appreciated!

[gkick]

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

[F3K Total]

Nobody can see the SQL-statment please post the query in SQL-Mode.
You can paste as "Code" ... as i did ...

[gkick]

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

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

[F3K Total]

Heard,
better would be the database itself.
R

[Villeroy]

Listbox with linked field = Filter.INT1 and bound field = 0

Code: Select all

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

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.

[gkick]

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

[Villeroy]

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:

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

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

[gkick]

Thank you for this, picking up a lot from these examples.

[chrisb]

@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.

[gkick]

@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