agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Sv: Sv: Re: Difficulties with LAG-function when calculating overtime
Date: Mon, 19 Nov 2018 17:49:22 +0100 (CET)
Message-ID: <VisenaEmail.4e.f4aac1334f04dcd5.1672cdde6b4@tc7-visena> (raw)
In-Reply-To: <VisenaEmail.4c.51bde41e996a86d1.1672cc0ac94@tc7-visena>
På mandag 19. november 2018 kl. 17:20:23, skrev Andreas Joseph Krogh <
andreas@visena.com <mailto:andreas@visena.com>>:
På mandag 19. november 2018 kl. 17:08:57, skrev David G. Johnston <
david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>:
On Mon, Nov 19, 2018 at 6:24 AM Andreas Joseph Krogh <andreas@visena.com
<mailto: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_payoutFROM 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
FROMlogged_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
Message-ID: <VisenaEmail.4e.f4aac1334f04dcd5.1672cdde6b4@tc7-visena>
Permalink: ../VisenaEmail.4e.f4aac1334f04dcd5.1672cdde6b4@tc7-visena/
Also on: postgresql.org/message-id/VisenaEmail.4e.f4aac1334f04dcd5.1672cdde6b4@tc7-visena
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@visena.com, david.g.johnston@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: Sv: Sv: Re: Difficulties with LAG-function when calculating overtime
In-Reply-To: <VisenaEmail.4e.f4aac1334f04dcd5.1672cdde6b4@tc7-visena>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox