Difficulties with LAG-function when calculating overtime - Mailing list pgsql-sql
| From | Andreas Joseph Krogh |
|---|---|
| Subject | Difficulties with LAG-function when calculating overtime |
| Date | |
| Msg-id | VisenaEmail.45.93d37729c4f92a2b.1672bef8127@tc7-visena Whole thread Raw |
| Responses |
Re: Difficulties with LAG-function when calculating overtime
|
| List | pgsql-sql |
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
);
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)
;
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 (SELECT cast(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_date ORDER 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 (SELECT cast(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_date ORDER 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