Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gOmhT-00085L-LW for pgsql-sql@arkaria.postgresql.org; Mon, 19 Nov 2018 16:47:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gOmhQ-0006xV-5I for pgsql-sql@arkaria.postgresql.org; Mon, 19 Nov 2018 16:47:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gOmhP-0006xO-Mu for pgsql-sql@lists.postgresql.org; Mon, 19 Nov 2018 16:46:59 +0000 Received: from post.visena.com ([46.226.10.50]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gOmhI-000188-HG for pgsql-sql@lists.postgresql.org; Mon, 19 Nov 2018 16:46:58 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=visena.com; s=20141101.wh; h=Content-Type:MIME-Version:Subject:In-Reply-To:Message-ID:Cc:To:From:Date; bh=VaOV5KzhHsxjFr9edWyzOpnx6t7ABex5SNcA8ZM1OFY=; b=MSzgeEEIq+mY9kCpdzzAXohZMdnesvJJjuQrQIAKKRkxKOzTHP3LtxbfGMgHx3I94mkhFikQIFZB7o6Q1gPlW0U71Z3wo6Y0krddsX4gQUTVSV5stAA9F6zYasRM1yBkJOjm9UivtnqjQFDWzrrppZqZ0xFLhakzPefw9NX9Y5I=; Received: from [10.0.1.10] (helo=tc7-visena.wh.internal.visena.com) by post.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1gOmhE-0007FG-Gn; Mon, 19 Nov 2018 17:46:50 +0100 Received: from localhost ([127.0.0.1] helo=tc7-visena.wh.internal.visena.com) by tc7-visena.wh.internal.visena.com with esmtp (Exim 4.86_2) (envelope-from ) id 1gOmji-0002Es-TU; Mon, 19 Nov 2018 17:49:22 +0100 Date: Mon, 19 Nov 2018 17:49:22 +0100 (CET) From: Andreas Joseph Krogh To: "David G. Johnston" Cc: pgsql-sql Message-ID: In-Reply-To: Subject: Sv: Sv: Re: Difficulties with LAG-function when calculating overtime MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_340_1279189890.1542646162774" X-Mailer: Visena Mail 2.1.175 X-Spam-Score: -1.0 X-Spam-Report: SpamAssasin (score=-1.0, required 5.0 ALL_TRUSTED=-1,HTML_MESSAGE=0.001) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_340_1279189890.1542646162774 Content-Type: multipart/related; boundary="----=_Part_341_1157958977.1542646162774" ------=_Part_341_1157958977.1542646162774 Content-Type: multipart/alternative; boundary="----=_Part_342_1767237535.1542646162799" ------=_Part_342_1767237535.1542646162799 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 mandag 19. november 2018 kl. 17:20:23, skrev Andreas Joseph Krogh < andreas@visena.com >: P=C3=A5 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 > wrote: Anyone has a clever way to solve this kinds of issues and craft a query whi= ch=20 produces the desired result as in the table above? =C2=A0 Thinking in terms of theory - you need to calculate the first row and then= =20 calculate the next row using data from the first row.=C2=A0 Then calculate = the third=20 row using data from the second row (you might need to carry-forward some va= lue=20 from the first row so that the third row can see them...).=C2=A0 That sound= s like=20 the algorithm for iteration which is implemented in SQL via "WITH RECURSIVE= ". =C2=A0 David J. =C2=A0 Yea, I kind of figured RECURSIVE CTE was the way foreward... If anyone has got this working, give me a tip:-) =C2=A0 Got it, thanks for getting me on the right track! =C2=A0 For the archives: WITH RECURSIVE prev AS ( SELECT lh.date , lh.balance -- Basis for extra=20 overtime: balance - compensatory_time , GREATEST(lh.balance -=20 lh.compensatory_time ,0) AS basis_for_extra_overtime , (GREATEST(lh.balance= -=20 lh.compensatory_time ,0) * lh.overtime_rate/100) as extra_overtime -- balan= ce +=20 extra_overtime , lh.balance -- extra_overtime + (GREATEST(lh.balance -=20 lh.compensatory_time ,0) * lh.overtime_rate/100) AS total_time , lh.payout = ,=20 lh.compensatory_time-- Accumulated balance: (total_time - payout -=20 compensatory_time + "previous month's" accumulated_balance , lh.balance --= =20 extra_overtime + (GREATEST(lh.balance - lh.compensatory_time , 0) *=20 lh.overtime_rate/100) - lh.payout - lh.compensatory_time AS=20 accumulated_balance_after_payoutFROM logged_hours lh WHERE lh.date =3D=20 '2018-01-01':: DATE UNION ALL SELECT lh.date , lh.balance -- Basis for ext= ra=20 overtime: balance - compensatory_time - "previous month's"=20 accumulated_balance_after_payout if negative , GREATEST(lh.balance -=20 lh.compensatory_time-- minus "previous month's"=20 accumulated_balance_after_payout if negative + LEAST(coalesce (prev.accumulated_balance_after_payout,0), 0) , 0) AS basis_for_extra_overt= ime=20 , (GREATEST(lh.balance - lh.compensatory_time -- minus "previous month's"= =20 accumulated_balance_after_payout if negative + LEAST(coalesce (prev.accumulated_balance_after_payout,0), 0) , 0) * lh.overtime_rate/100) = as=20 extra_overtime-- balance + extra_overtime , lh.balance -- extra_overtime + = ( GREATEST(lh.balance - lh.compensatory_time -- minus "previous month's"=20 accumulated_balance_after_payout if negative + LEAST(coalesce (prev.accumulated_balance_after_payout,0), 0) , 0) * lh.overtime_rate/100) = AS=20 total_time , lh.payout , lh.compensatory_time-- Accumulated balance:=20 (total_time - payout - compensatory_time + "previous month's"=20 accumulated_balance , lh.balance -- extra_overtime + (GREATEST(lh.balance -= =20 lh.compensatory_time-- minus "previous month's"=20 accumulated_balance_after_payout if negative + LEAST(coalesce (prev.accumulated_balance_after_payout,0), 0) , 0) * lh.overtime_rate/100) = -=20 lh.payout - lh.compensatory_time +coalesce (prev.accumulated_balance_after_payout,0) AS accumulated_balance_after_payo= ut=20 FROMlogged_hours lh JOIN prev ON lh.date =3D prev.date + '1 MONTH'::INTERVA= L )=20 select* from prev;=20 =C2=A0 Produces the correct result: =C2=A0 date balance basis_for_extra_overtime extra_overtime total_time payout=20 compensatory_time accumulated_balance_after_payout 2018-01-01 17.50 17.5 8.= 75=20 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=20 3.75 17.75 3.75 4.00 7.5 2018-04-01 -10.00 0 0 -10 0.00 10.00 -12.5=20 =C2=A0 -- Andreas Joseph Krogh ------=_Part_342_1767237535.1542646162799 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 mandag 19. november 2018 kl. 17:20:23, skrev Andreas Joseph Kr= ogh <andreas@visena.com>:
P=C3=A5 mandag 19. november 2018 kl. 17:08:57, skrev David G. Johnston= <david.g.johnston@gmail.c= om>:
On Mon, Nov 19, 2= 018 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 quer= y which produces the desired result as in the table above?
=C2=A0
Thinking in terms of theory - you need to calculate the first row and th= en calculate the next row using data from the first row.=C2=A0 Then calcula= te the third row using data from the second row (you might need to carry-fo= rward some value from the first row so that the third row can see them...).= =C2=A0 That sounds like the algorithm for iteration which is implemented in= SQL via "WITH RECURSIVE".
=C2=A0
David J.
=C2=A0
Yea, I kind of figured RECURSIVE CTE was the way foreward...
If anyone has got this working, give me a tip:-)
=C2=A0
Got it, thanks for getting me on the right track!
=C2=A0
For the archives:
WITH RECURSIVE prev =
AS (
    SELECT lh.date
        , lh.balance
-- Basis for extra overtim=
e: balance - compensatory_time
        , GREATEST(lh.balance
                       - lh.compensatory_time
              , 0) AS basis_for_extra_overtime
        , (GREATEST(lh.balance
                        - lh.compensatory_time
               , 0) * lh.overtime_rat=
e/100) as extra_overtime
-- balance + extra_overtim=
e
        , lh=
.balance
          -- extra_overtim=
e
              + (GREATEST(lh.balance
                              - lh.compensatory_time
                     , 0) * lh.overti=
me_rate/100) AS total_time
        , lh.payout
        , lh.compensatory_time
-- Accumulated balance: (t=
otal_time - payout - compensatory_time + "previous month's" accum=
ulated_balance
        , lh=
.balance
          -- extra_overtim=
e
              + (GREATEST(lh.balance
                              - lh.compensatory_time
                     , 0) * lh.overti=
me_rate/100)
              - lh.payout
              - lh.compensatory_time
        AS accumulat=
ed_balance_after_payout
    FROM logged_hour=
s lh
    WHERE lh.date =
=3D '2018-01-01' :: =
DATE

        UNION ALL
        SELECT lh.date
            , lh.balance
-- Basis for extra overtim=
e: balance - compensatory_time - "previous month's" accumulated_b=
alance_after_payout if negative
            =
, GREATEST(lh.balance
                           - lh.compensatory_time
                       -- =
 minus "previous month's" accumulated_balance_after_payout if neg=
ative
                   =
        + LEAST(coalesce(prev.accumulated_bal=
ance_after_payout, 0), 0)
                  , 0) AS basis_for_extra_overtime
            , (GREATEST(lh.balanc=
e
                            - lh.compensatory_time
                        --=
  minus "previous month's" accumulated_balance_after_payout if ne=
gative
                   =
         + LEAST(coalesce(prev.accumulated_ba=
lance_after_payout, 0), 0)
                   , 0) * lh.overtime=
_rate/100) as extra_overtime
-- balance + extra_overtim=
e
            =
, lh.balance
              -- extra_ove=
rtime
                  <=
/span>+ (GREATEST(lh.balance
                                  - lh.compensatory_time
                              --  minus "previous month's" accumulated_balance_after_payout=
 if negative
                   =
               + LEAST(coalesce(prev.accumula=
ted_balance_after_payout, 0), 0)
                         , 0) * lh.ov=
ertime_rate/100) AS total_time
            , lh.payout
            , lh.compensatory_time

-- Accumulated balance: (t=
otal_time - payout - compensatory_time + "previous month's" accum=
ulated_balance
            =
, lh.balance
              -- extra_ove=
rtime
                  <=
/span>+ (GREATEST(lh.balance
                                  - lh.compensatory_time
                              --  minus "previous month's" accumulated_balance_after_payout=
 if negative
                   =
               + LEAST(coalesce(prev.accumula=
ted_balance_after_payout, 0), 0)
                         , 0) * lh.ov=
ertime_rate/100)
                  - lh.payout
                  - lh.compensatory_time
                  + coales=
ce(prev.accumulated_balance_after_payout, 0)
            AS accum=
ulated_balance_after_payout

        FROM logged_=
hours lh JOIN prev <=
span style=3D"color:#000080;font-weight:bold;">ON lh.date =3D prev.d=
ate + '1 MONTH'::INTERVAL

    ) select * from prev;
=C2=A0
Produces the correct result:
=C2=A0
=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09
datebalancebasis_for_extra_overtimeextra_overtimetotal_timepayoutcompensatory_timeaccumulated_balance_after_payout
2018-01-0117.5017.58.7526.2526.250.000
2018-02-012.50002.50.005.00-2.5
2018-03-0114.007.53.7517.753.754.007.5
2018-04-01-10.0000-100.0010.00-12.5
=C2=A0
">
--
Andrea= s Joseph Krogh
------=_Part_342_1767237535.1542646162799-- ------=_Part_341_1157958977.1542646162774-- ------=_Part_340_1279189890.1542646162774--