agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedDifficulties with LAG-function when calculating overtime
2+ messages / 2 participants
[nested] [flat]
* Difficulties with LAG-function when calculating overtime
@ 2018-11-19 13:27 Andreas Joseph Krogh <andreas@visena.com>
2018-11-19 16:08 ` Re: Difficulties with LAG-function when calculating overtime David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Andreas Joseph Krogh @ 2018-11-19 13:27 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Hi all, I'm having difficulties dealing with values from "previous-rows" (LAG).
I have these variables:
balance: A user's balance of worked hours for month, may be negative if worked
too little.
overtime_rate: percent-value used to calculate "bonus hours". Ex: If a user
has a balance of 10 hours (meaning 10 hours overtime) and 50% then he gets 5
"extra hours" which is added to the accumulated total.
compensatory_time: A user may have hours logged as compensatory time when
having some hours off work.
payout: A number of hours the user gets "payed" (withdraw) when getting
compensated for overtime for the current month.
I have this (simplified example) schema:
drop table if exists logged_hours;
create table logged_hours(
date DATE NOT NULL,
balance NUMERIC(100,2) NOT NULL,
overtime_rate INT NOT NULL,
compensatory_time NUMERIC(100,2) NOT NULL,
payout NUMERIC(100,2) NOT NULL
);
INSERT INTO logged_hours(date, balance, overtime_rate, compensatory_time,
payout)
VALUES
('2018-01-01', 17.5, 50, 0, 26.25)
, ('2018-02-01', 2.5, 50, 5 , 0)
, ('2018-03-01', 14 , 50, 4 , 3.75)
, ('2018-04-01', -10 , 50, 10 , 0)
;
And I'm trying to craft a query to calculate, for each month:
- balance (the "input-value")
- extra overtime
This is GREATEST(balance minus compensatory-time minus "accumulated total
from previous period if negative", 0)
- Accumulated balance
balance + extra_overtime - compensatory_time - payout + "accumulated
balance from previous period"
Here we see that extra_overtime and accumulated_balance are "inter-related"
The goal is to come up with a query which gives this data (from the above
INSERT):
date balance basis_for_extra_overtime extra_overtime (50%) total_time payout
compensatory_time accumulated_balance_after_payout 2018-01-01 17.50 17.5 (17.5
* 0.5) 8.75 (17.5 + 8.75) 26.25 26.25 0.00 (26.26 – 26.25) 0 2018-02-01 2.50 0 0
2.5 0.00 5.00 (2.5 – 5) –2.5 2018-03-01 14.00 (14 – 4 – 2.5) 7.5 (7.5 * 0.5)
3.75 (14 + 3.75) 17.75 3.75 4.00 (17.75 – 3.75 – 4 – 2.5) 7.5 2018-04-01 –10.00
0 0 –10 0.00 10.00 (–10 – 10 + 7.5) –12.5
As we see, the "previous period"'s value for
"accumulated_balance_after_payout" is used in the calculation of the fields
"basis_for_extra_overtime" (for period 2018-03, because the accumulated balance
is negative) and "accumulated_balance_after_payout".
I have this basis-query:
SELECT lh.date , lh.balance -- Basis for extra overtime: balance -
compensatory_time - "previous month's" accumulated_balance_after_payout if
negative , GREATEST(lh.balance - lh.compensatory_time -- TODO: minus "previous
month's" accumulated_balance_after_payout if negative , 0) AS
basis_for_extra_overtime , (GREATEST(lh.balance - lh.compensatory_time -- TODO:
minus "previous month's" accumulated_balance_after_payout if negative , 0) *
lh.overtime_rate/100) as extra_overtime -- balance + extra_overtime , lh.balance
-- extra_overtime + (GREATEST(lh.balance - lh.compensatory_time -- TODO: minus
"previous month's" accumulated_balance_after_payout if negative , 0) *
lh.overtime_rate/100) AS total_time , lh.payout , lh.compensatory_time --
Accumulated balance: (total_time - payout - compensatory_time + "previous
month's" accumulated_balance , lh.balance -- extra_overtime + (GREATEST
(lh.balance - lh.compensatory_time-- TODO: minus "previous month's"
accumulated_balance_after_payout if negative , 0) * lh.overtime_rate/100) -
lh.payout - lh.compensatory_time-- TODO: plus "previous month's"
accumulated_balance_after_payout AS accumulated_balance_after_payout FROM (
SELECTcast(generate_series('2018-01-01' :: DATE, '2018-11-01' :: DATE, '1 month'
)AS DATE) as start_date) AS q CROSS JOIN logged_hours lh WHERE lh.date =
q.start_dateORDER BY lh.date ASC ;
And have tried to use LAG to use value from "previous row" when calculating
"basis_for_extra_overtime":
SELECT lh.date , lh.balance -- Basis for extra overtime: balance -
compensatory_time - "previous month's" accumulated_balance_after_payout if
negative , GREATEST(lh.balance - lh.compensatory_time + LEAST(LAG( lh.balance +
(GREATEST(lh.balance - lh.compensatory_time, 0) * lh.overtime_rate/100) -
lh.payout - lh.compensatory_time )OVER (order by lh.date) , 0) , 0) AS
basis_for_extra_overtime , (GREATEST(lh.balance - lh.compensatory_time -- TODO:
minus "previous month's" accumulated_balance_after_payout if negative , 0) *
lh.overtime_rate/100) as extra_overtime -- balance + extra_overtime ,
lh.balance + (GREATEST(lh.balance - lh.compensatory_time -- TODO: minus
"previous month's" accumulated_balance_after_payout if negative , 0) *
lh.overtime_rate/100) AS total_time , lh.payout , lh.compensatory_time --
Accumulated balance: (total_time - payout - compensatory_time + "previous
month's" accumulated_balance , lh.balance + (GREATEST(lh.balance -
lh.compensatory_time ,0) * lh.overtime_rate/100) - lh.payout -
lh.compensatory_time-- TODO: plus "previous month's"
accumulated_balance_after_payout AS accumulated_balance_after_payout FROM (
SELECTcast(generate_series('2018-01-01' :: DATE, '2018-11-01' :: DATE, '1 month'
)AS DATE) as start_date) AS q CROSS JOIN logged_hours lh WHERE lh.date =
q.start_dateORDER BY lh.date ASC ;
In the query above I use LEAST(<expr>, 0) to only add the value if negative,
effectively subtracting it, which is what I want.
The problem with this as I've written it above is that it tries to subtract
the previous row's accumulated_balance_after_payout, but the calculation of
that is not correct because in order to do that we need the previous
row's basis_for_extra_overtime, which again is dependent of the
previous-previous-row, and it kind of gets difficult from here...
Anyone has a clever way to solve this kinds of issues and craft a query which
produces the desired result as in the table above?
Thanks.
-- Andreas Joseph Krogh
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Difficulties with LAG-function when calculating overtime
2018-11-19 13:27 Difficulties with LAG-function when calculating overtime Andreas Joseph Krogh <andreas@visena.com>
@ 2018-11-19 16:08 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2018-11-19 16:08 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Mon, Nov 19, 2018 at 6:24 AM Andreas Joseph Krogh <andreas@visena.com>
wrote:
> Anyone has a clever way to solve this kinds of issues and craft a query
> which produces the desired result as in the table above?
>
Thinking in terms of theory - you need to calculate the first row and then
calculate the next row using data from the first row. Then calculate the
third row using data from the second row (you might need to carry-forward
some value from the first row so that the third row can see them...). That
sounds like the algorithm for iteration which is implemented in SQL via
"WITH RECURSIVE".
David J.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-11-19 16:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-19 13:27 Difficulties with LAG-function when calculating overtime Andreas Joseph Krogh <andreas@visena.com>
2018-11-19 16:08 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox