[Solved] CASE WHEN or sql loop?

Creating tables and queries
Post Reply
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

[Solved] CASE WHEN or sql loop?

Post by gkick »

Hi again,
Not sure how to handle this one. The attachment is a simplified mockup of a previous payroll scenario.

table Contacts has some contacts, the schedule table contains categories like A - used for Donald and Betty, B and C for others. Corresponding to the letter designation are pay rates at specific intervals which may represent increases or decreases over time.

Currently Donald Duck and Betty are on schedule A with the first pay rate effective as of 1/1/2020 and an increased rate effective as of 25/3
My guess would be that should be possible with a somewhat complex CASE WHEN structure,
in pseudo code - when a new work record is created I need to match up the log date with the effective pay rate and use the value in the subsequent calculation. (However there is no link between the tables)

The code below is just the raw query so far as my first lot of criteria s dint work .

Code: Select all

SELECT "tblLog"."ldate", "first_name" || ' ' || "lastname" "Name", "tblSchedule"."schedule", "tblSchedule"."effective", "tblSchedule"."standardrate", "tblLog"."worked", "standardrate" * "worked" "Pay" FROM "tblContacts", "tblSchedule", "tblLog" WHERE "tblContacts"."sid" = "tblSchedule"."sid" AND "tblLog"."cid" = "tblContacts"."contact_id"
If CASE WHEN is not viable ,what other options would achieve the desired outcome, read somewhere looping in sql should be avoided.
Thank you.

https://www.dropbox.com/s/3n85zm0i9s72r ... e.rar?dl=0
Last edited by Hagar Delest on Tue Mar 31, 2020 11:16 am, edited 1 time in total.
Reason: tagged solved
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: CASE WHEN or sql loop?

Post by F3K Total »

Hi,
complicated, but i found nothing better:

Code: Select all

SELECT 
    Z."ldate", 
    Z."worked", 
    X."first_name" || ' ' || X."lastname" "Name", 
    Y."schedule",  
    (SELECT  MAX("effective") "lasteffective" FROM "tblSchedule" WHERE "schedule" = Y."schedule" AND "effective" <= Z."ldate" GROUP BY "schedule")"lasteffective",
    (SELECT  "standardrate" FROM "tblSchedule" WHERE "schedule" = Y."schedule" AND "effective" = (SELECT  MAX("effective") "lasteffective" FROM "tblSchedule" WHERE "schedule" = Y."schedule" AND "effective" <= Z."ldate" GROUP BY "schedule") )"currentrate",
        (SELECT  "standardrate" FROM "tblSchedule" WHERE "schedule" = Y."schedule" AND "effective" = (SELECT  MAX("effective") "lasteffective" FROM "tblSchedule" WHERE "schedule" = Y."schedule" AND "effective" <= Z."ldate" GROUP BY "schedule") ) * Z."worked" "Pay"
FROM 
    "tblContacts" X, 
    "tblSchedule" Y, 
    "tblLog" Z
WHERE 
    X."sid" = Y."sid" 
AND 
    Z."cid" = X."contact_id"
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
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: CASE WHEN or sql loop?

Post by chrisb »

@gkick,
i have not downloaded the above attachment.
this response is based around the attachment you uploaded in a previous obviously related topic: viewtopic.php?f=61&t=101391
i did not reply to that thread because it was marked [Solved] before i got to download your database, however i did produce a solution of sorts & that solution forms the basis of the attachment below which uses embedded hsqldb 1.8.0.10.
just want to say that what you were aiming for there was achievable but not realistic & i settled for a more practical outcome.

it's often difficult to configure a users db in order to achieve a result & very often quicker & easier to build a db from scratch.
i placed most of the fields from your table "tblschedule" into a new table "tPayRateLog".
i do not use the table "tblschedule".
i only access the table "tblPax" to extract the field "signin".
i do use the table "tbllog" which contains the sign in/out data. i do not use & cannot see the purpose of the field "tbllog"."ldate" which contains errors.
"tPayRateLog"."OTRate" (overtime rate) is a decimal value so overtime pay = "tPayRateLog"."StdRate" * "tPayRateLog"."OTRate". this is in preference to the literal values which you used.

what we have:
four list box queries.
a query called 'qFilteredRemunerationWithPayRate'. the calculations within this query are based on your original code.
a view created from this query is the data source for the report.
the logged hours (difference between signin & signout) are rounded down to the nearest fifteen minutes for purpose of clarity.

two forms:
one to select values & open the report (uses macros). this form is related to your previous topic as mentioned above.
the other 'fPayRate' addresses this topic & relates to pay rates.
the top pair of forms are used to create/update pay rates.
the bottom pair are used to assign/update an employees pay rate.
overall i think that it's fairly easy to use & importantly preserves history.

this is not a fully functioning db, the intention here is to demonstrate a method which hopefully may help in securing a solution.
there are many issues which need to be addressed e.g. the actual start/finish times & that pay rates may vary by day or hour.
this is a 'Base' forum & 'Base' does not play well with some current back end database functions (arrays for example) & is therefore restrictive.

i tested by using the query 'qFilteredRemunerationWithPayRate':
1) open query for edit.
2) copy the query & close the window.
3) paste into a query design window.
4) delete everything above the first 'from' clause.
5) insert 'select *' as the first row.
6) delete the second row from bottom (group by clause).
7) bottom row should = 'order by p."signin", t."signin"'.
8) activate the 'SQL' icon. save as 'Query1'.
now we have a query which shows each & every row.
remember that the data is filtered according to the values selected in the form 'fOpen Report' so open the form, select a signin (try Mark who had a change of rate), close the form.
execute the query & we can see the signin, logged hrs, pay rate etc.
i went further & registered the db then imported 'Query1' into 'Calc' & used the 'SUM' function on various groups of cells.

EXAMPLES OF CHANGE IN PAY RATE:
using the form 'fPayRate'.
Mark is on pay rate B. all employee on pay rate B will have received a salary increase of 5% on 1 Jan 2020.
hit pay rate B & employee Mark & note that the first row values are applied between StartDate & EndDate while the current pay rate (second row) is applied from StartDate & EndDate is null.

Max was on pay rate A until 31 Dec 2019 when he was switched to pay rate E. hit pay rate A, E & employee Max to see how it should be done.
 Edit: 29 Mar 2020. found error in code used "PayRateLogID" when i should have used "PayRateID"
replaced guilty attachment with ammended version. 
Attachments
PayRates_2.odb
(66.59 KiB) Downloaded 224 times
Last edited by chrisb on Mon Mar 30, 2020 12:06 am, edited 1 time in total.
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
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: CASE WHEN or sql loop?

Post by gkick »

@F3K Total
@chrisb

Thank you very, very much gentlemen for your time, effort and interest providing these solutions. And yes I agree, its often more practical to scrap a brown field project and start afresh rather then bandaging and in the process fix one bit and break other stuff.

kind regards
GK
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Post Reply