Sv: Sv: Re: Difficulties with LAG-function when calculatingovertime
| От | Andreas Joseph Krogh |
|---|---|
| Тема | Sv: Sv: Re: Difficulties with LAG-function when calculatingovertime |
| Дата | |
| Msg-id | VisenaEmail.4e.f4aac1334f04dcd5.1672cdde6b4@tc7-visena обсуждение исходный текст |
| Ответ на | Sv: Re: Difficulties with LAG-function when calculating overtime (Andreas Joseph Krogh <andreas@visena.com>) |
| Список | pgsql-sql |
På mandag 19. november 2018 kl. 17:20:23, skrev Andreas Joseph Krogh <andreas@visena.com>:
På mandag 19. november 2018 kl. 17:08:57, skrev David G. Johnston <david.g.johnston@gmail.com>: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.Yea, I kind of figured RECURSIVE CTE was the way foreward...If anyone has got this working, give me a tip:-)
Got it, thanks for getting me on the right track!
For the archives:
WITH RECURSIVE prev AS ( SELECT lh.date , lh.balance -- Basis for extra overtime: balance - compensatory_time , GREATEST(lh.balance - lh.compensatory_time , 0) AS basis_for_extra_overtime , (GREATEST(lh.balance - lh.compensatory_time , 0) * lh.overtime_rate/100) as extra_overtime -- balance + extra_overtime , lh.balance -- extra_overtime + (GREATEST(lh.balance - lh.compensatory_time , 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 , 0) * lh.overtime_rate/100) - lh.payout - lh.compensatory_time AS accumulated_balance_after_payout FROM logged_hours lh WHERE lh.date = '2018-01-01' :: DATE UNION ALL 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 -- minus "previous month's" accumulated_balance_after_payout if negative + LEAST(coalesce(prev.accumulated_balance_after_payout, 0), 0) , 0) AS basis_for_extra_overtime , (GREATEST(lh.balance - lh.compensatory_time -- minus "previous month's" accumulated_balance_after_payout if negative + LEAST(coalesce(prev.accumulated_balance_after_payout, 0), 0) , 0) * lh.overtime_rate/100) as extra_overtime -- balance + extra_overtime , lh.balance -- extra_overtime + (GREATEST(lh.balance - lh.compensatory_time -- minus "previous month's" accumulated_balance_after_payout if negative + LEAST(coalesce(prev.accumulated_balance_after_payout, 0), 0) , 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 -- minus "previous month's" accumulated_balance_after_payout if negative + LEAST(coalesce(prev.accumulated_balance_after_payout, 0), 0) , 0) * lh.overtime_rate/100) - lh.payout - lh.compensatory_time + coalesce(prev.accumulated_balance_after_payout, 0) AS accumulated_balance_after_payout FROM logged_hours lh JOIN prev ON lh.date = prev.date + '1 MONTH'::INTERVAL ) select * from prev;
Produces the correct result:
| date | balance | basis_for_extra_overtime | extra_overtime | total_time | payout | compensatory_time | accumulated_balance_after_payout |
|---|---|---|---|---|---|---|---|
| 2018-01-01 | 17.50 | 17.5 | 8.75 | 26.25 | 26.25 | 0.00 | 0 |
| 2018-02-01 | 2.50 | 0 | 0 | 2.5 | 0.00 | 5.00 | -2.5 |
| 2018-03-01 | 14.00 | 7.5 | 3.75 | 17.75 | 3.75 | 4.00 | 7.5 |
| 2018-04-01 | -10.00 | 0 | 0 | -10 | 0.00 | 10.00 | -12.5 |
--
Andreas Joseph Krogh
В списке pgsql-sql по дате отправления:
Предыдущее
От: Andreas Joseph KroghДата:
Сообщение: Sv: Re: Difficulties with LAG-function when calculating overtime
Следующее
От: "Voillequin, Jean-Marc"Дата:
Сообщение: Weird "could not determine which collation to use for stringcomparison" with LEAST/GREATEST on PG11 procedure