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 1gOjXb-00053X-On for pgsql-sql@arkaria.postgresql.org; Mon, 19 Nov 2018 13:24:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gOjXa-0008Hn-1l for pgsql-sql@arkaria.postgresql.org; Mon, 19 Nov 2018 13:24:38 +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 1gOjXZ-0008Hg-Ej for pgsql-sql@lists.postgresql.org; Mon, 19 Nov 2018 13:24:37 +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 1gOjXV-0004xq-26 for pgsql-sql@lists.postgresql.org; Mon, 19 Nov 2018 13:24:36 +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:Message-ID:To:From:Date; bh=MLsqC2pecxaDPfKCWWEpzcHngq24JiCX3+xaFHPTivE=; b=KoO4y+2aBsUh/jZ9UVrTHAwt5pUYHegvF8RcyzMvbF6kdwUZHJ8PnJW+T1qwgTz5AziYNffxkNr1Bm0Ya+EV6DOJWu55OpLA6vnyuZMnRgUAhUBHvjgtedTj2axI68lf9GEnIxnLzPyrbTUc/keBgA2utGbkT0GxTkgy7jo/xdc=; 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 1gOjXP-0001sj-FC for pgsql-sql@lists.postgresql.org; Mon, 19 Nov 2018 14:24:29 +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 1gOjZt-0001KW-OI for pgsql-sql@lists.postgresql.org; Mon, 19 Nov 2018 14:27:01 +0100 Date: Mon, 19 Nov 2018 14:27:01 +0100 (CET) From: Andreas Joseph Krogh To: pgsql-sql@lists.postgresql.org Message-ID: Subject: Difficulties with LAG-function when calculating overtime MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_300_1613670134.1542634021639" 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_300_1613670134.1542634021639 Content-Type: multipart/related; boundary="----=_Part_301_1453041292.1542634021639" ------=_Part_301_1453041292.1542634021639 Content-Type: multipart/alternative; boundary="----=_Part_302_879688681.1542634021653" ------=_Part_302_879688681.1542634021653 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi all, I'm having difficulties dealing with values from "previous-rows" (L= AG). =C2=A0 I have these variables: balance: A user's balance of worked hours for month, may be negative if wor= ked=20 too little. overtime_rate: percent-value=C2=A0used to calculate "bonus hours". Ex: If a= user=20 has a balance of 10 hours (meaning 10 hours overtime) and 50% then he gets = 5=20 "extra hours" which is added to the accumulated total. compensatory_time: A user may have hours logged as compensatory time when= =20 having some hours off work. payout: A number of hours the user gets "payed" (withdraw) when getting=20 compensated for overtime for the current month. =C2=A0 I have this (simplified example) schema: =C2=A0 drop table if exists logged_hours; create table logged_hours( =C2=A0 =C2=A0 date DATE NOT NULL, =C2=A0 =C2=A0 balance NUMERIC(100,2) NOT NULL, =C2=A0 =C2=A0 overtime_rate INT NOT NULL, =C2=A0 =C2=A0 compensatory_time NUMERIC(100,2) NOT NULL, =C2=A0 =C2=A0 payout NUMERIC(100,2) NOT NULL ); INSERT INTO logged_hours(date, balance, overtime_rate, compensatory_time,= =20 payout) VALUES =C2=A0 =C2=A0 =C2=A0 ('2018-01-01',=C2=A0 17.5, 50, 0, 26.25) =C2=A0 =C2=A0 , ('2018-02-01',=C2=A0 =C2=A02.5, 50, 5=C2=A0 =C2=A0, 0) =C2=A0 =C2=A0 , ('2018-03-01',=C2=A0 14=C2=A0 , 50, 4=C2=A0 =C2=A0, 3.75) =C2=A0 =C2=A0 , ('2018-04-01', -10=C2=A0 , 50, 10=C2=A0 , 0) ; And I'm trying to craft a query to calculate, for each month: - balance (the "input-value") - extra overtime =C2=A0 This is GREATEST(balance minus compensatory-time minus "accumulated = total=20 from previous period if negative", 0) - Accumulated balance =C2=A0 balance + extra_overtime - compensatory_time - payout + "accumulated= =20 balance=C2=A0from previous period" =C2=A0 Here we see that extra_overtime and accumulated_balance are "inter-related" =C2=A0 The goal is to come up with a query which gives this data (from the above= =20 INSERT): =C2=A0 date balance basis_for_extra_overtime extra_overtime (50%) total_time payou= t=20 compensatory_time accumulated_balance_after_payout 2018-01-01 17.50 17.5 (1= 7.5=20 * 0.5) 8.75 (17.5 + 8.75) 26.25 26.25 0.00 (26.26 =E2=80=93 26.25)=C2=A00 2= 018-02-01 2.50 0 0 2.5 0.00 5.00 (2.5 =E2=80=93 5) =E2=80=932.5 2018-03-01 14.00 (14 =E2=80=93= 4 =E2=80=93 2.5) 7.5 (7.5 * 0.5)=20 3.75 (14 + 3.75) 17.75 3.75 4.00 (17.75 =E2=80=93 3.75 =E2=80=93 4 =E2=80= =93 2.5) 7.5 2018-04-01 =E2=80=9310.00=20 0 0 =E2=80=9310 0.00 10.00 (=E2=80=9310 =E2=80=93 10 + 7.5) =E2=80=9312.5= =20 =C2=A0 =C2=A0 As we see, the "previous period"'s value for=20 "accumulated_balance_after_payout" is used in the calculation of the fields= =20 "basis_for_extra_overtime" (for period 2018-03, because the accumulated bal= ance=20 is negative) and "accumulated_balance_after_payout". =C2=A0 I have this basis-query: =C2=A0 =C2=A0 SELECT lh.date , lh.balance -- Basis for extra overtime: balance -=20 compensatory_time - "previous month's" accumulated_balance_after_payout if= =20 negative , GREATEST(lh.balance - lh.compensatory_time -- TODO: minus "previ= ous=20 month's" accumulated_balance_after_payout if negative , 0) AS=20 basis_for_extra_overtime , (GREATEST(lh.balance - lh.compensatory_time -- T= ODO:=20 minus "previous month's" accumulated_balance_after_payout if negative , 0) = *=20 lh.overtime_rate/100) as extra_overtime -- balance + extra_overtime , lh.ba= lance -- extra_overtime + (GREATEST(lh.balance - lh.compensatory_time -- TODO: mi= nus=20 "previous month's" accumulated_balance_after_payout if negative , 0) *=20 lh.overtime_rate/100) AS total_time , lh.payout , lh.compensatory_time --= =20 Accumulated balance: (total_time - payout - compensatory_time + "previous= =20 month's" accumulated_balance , lh.balance -- extra_overtime + (GREATEST (lh.balance - lh.compensatory_time-- TODO: minus "previous month's"=20 accumulated_balance_after_payout if negative , 0) * lh.overtime_rate/100) -= =20 lh.payout - lh.compensatory_time-- TODO: plus "previous month's"=20 accumulated_balance_after_payout AS accumulated_balance_after_payout FROM ( SELECTcast(generate_series('2018-01-01' :: DATE, '2018-11-01' :: DATE, '1 m= onth' )AS DATE) as start_date) AS q CROSS JOIN logged_hours lh WHERE lh.date =3D= =20 q.start_dateORDER BY lh.date ASC ;=20 =C2=A0 And have tried to use LAG to use value from "previous row" when calculating= =20 "basis_for_extra_overtime": =C2=A0 SELECT lh.date , lh.balance -- Basis for extra overtime: balance -=20 compensatory_time - "previous month's" accumulated_balance_after_payout if= =20 negative , GREATEST(lh.balance - lh.compensatory_time + LEAST(LAG( lh.balan= ce +=20 (GREATEST(lh.balance - lh.compensatory_time, 0) * lh.overtime_rate/100) -= =20 lh.payout - lh.compensatory_time )OVER (order by lh.date) , 0) , 0) AS=20 basis_for_extra_overtime , (GREATEST(lh.balance - lh.compensatory_time -- T= ODO:=20 minus "previous month's" accumulated_balance_after_payout if negative , 0) = *=20 lh.overtime_rate/100) as extra_overtime -- balance + extra_overtime ,=20 lh.balance + (GREATEST(lh.balance - lh.compensatory_time -- TODO: minus=20 "previous month's" accumulated_balance_after_payout if negative , 0) *=20 lh.overtime_rate/100) AS total_time , lh.payout , lh.compensatory_time --= =20 Accumulated balance: (total_time - payout - compensatory_time + "previous= =20 month's" accumulated_balance , lh.balance + (GREATEST(lh.balance -=20 lh.compensatory_time ,0) * lh.overtime_rate/100) - lh.payout -=20 lh.compensatory_time-- TODO: plus "previous month's"=20 accumulated_balance_after_payout AS accumulated_balance_after_payout FROM ( SELECTcast(generate_series('2018-01-01' :: DATE, '2018-11-01' :: DATE, '1 m= onth' )AS DATE) as start_date) AS q CROSS JOIN logged_hours lh WHERE lh.date =3D= =20 q.start_dateORDER BY lh.date ASC ;=20 =C2=A0 In the query above I use LEAST(, 0) to only add the value if negative= ,=20 effectively subtracting it, which is what I want. The problem with this as I've written it above is that it tries to subtract= =20 the previous row's=C2=A0accumulated_balance_after_payout, but the calculati= on of=20 that is not correct because in order to do that we need the previous=20 row's=C2=A0basis_for_extra_overtime, which again is dependent of the=20 previous-previous-row, and it kind of gets difficult from here... =C2=A0 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 Thanks. =C2=A0 -- Andreas Joseph Krogh ------=_Part_302_879688681.1542634021653 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi all, I'm having difficulties dealing with values from "previou= s-rows" (LAG).
=C2=A0
I have these variables:
balance: A user's balance of worked hours for month, may be negative i= f worked too little.
overtime_rate: percent-value=C2=A0used to calculate "bonus hours&= quot;. 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 accumu= lated total.
compensatory_time: A user may have hours logged as compensatory time w= hen having some hours off work.
payout: A number of hours the user gets "payed" (withdraw) w= hen getting compensated for overtime for the current month.
=C2=A0
I have this (simplified example) schema:
=C2=A0
drop table if exists logged_hours;
create table logged_hours(
=C2=A0 =C2=A0 date DATE NOT NULL,
=C2=A0 =C2=A0 balance NUMERIC(100,2) NOT NULL,
=C2=A0 =C2=A0 overtime_rate INT NOT NULL,
=C2=A0 =C2=A0 compensatory_time NUMERIC(100,2) NOT NULL,
=C2=A0 =C2=A0 payout NUMERIC(100,2) NOT NULL
);
INSERT INTO logged_hours(date, balance, overtime_rate, compensatory_ti= me, payout)
VALUES
=C2=A0 =C2=A0 =C2=A0 ('2018-01-01',=C2=A0 17.5, 50, 0, 26.25)
=C2=A0 =C2=A0 , ('2018-02-01',=C2=A0 =C2=A02.5, 50, 5=C2=A0 =C2=A0, 0)
=C2=A0 =C2=A0 , ('2018-03-01',=C2=A0 14=C2=A0 , 50, 4=C2=A0 =C2=A0, 3.75) =C2=A0 =C2=A0 , ('2018-04-01', -10=C2=A0 , 50, 10=C2=A0 , 0)
;
And I'm trying to craft a query to calculate, for each month:
- balance (the "input-value")
- extra overtime
=C2=A0 This is GREATEST(balance minus compensatory-time minus "ac= cumulated total from previous period if negative", 0)
- Accumulated balance
=C2=A0 balance + extra_overtime - compensatory_time - payout + "a= ccumulated balance=C2=A0from previous period"
=C2=A0
Here we see that extra_overtime and accumulated_balance are "inte= r-related"
=C2=A0
The goal is to come up with a query which gives this data (from the ab= ove INSERT):
=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=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09
datebalancebasis_for_extra_overtimeextra_overtime (50%)total_timepayoutcompensatory_timeaccumulated_balance_after_payout
2018-01-0117.5017.5(17.5 * 0.5) 8.75(17.5 + 8.75) 26.2526.250.00(26.26 =E2=80=93 26.25)=C2=A00
2018-02-012.50002.50.005.00(2.5 =E2= =80=93 5) =E2=80=932.5
2018-03-0114.00(14 =E2=80=93 4 = =E2=80=93 2.5) 7.5(7.5 * 0.5) 3.75(14 + 3.75) 17.753.754.00(17.75 =E2=80=93 3.75 <= /span>=E2=80=93 4 =E2=80=93 2.5) 7.5
2018-04-01=E2=80=9310.00 =09=09=0900=E2=80=93100.0010.00(=E2=80=9310 =E2=80=93 = 10 + 7.5) =E2=80=93= 12.5
=C2=A0
=C2=A0
As we see, the "previous period"'s value for "accumulat= ed_balance_after_payout" is used in the calculation of the fields &quo= t;basis_for_extra_overtime" (for period 2018-03, because the accumulat= ed balance is negative) and "accumulated_balance_after_payout".
=C2=A0
I have this basis-query:
=C2=A0
=C2=A0
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
               -- <=
span style=3D"color:#0000ff;font-weight:bold;font-style:italic;">TODO: minu=
s "previous month's" accumulated_balance_after_payout if negative
  =
        , 0) AS basis_for_extra_overtime
    , (GREATEST(lh.balance
                    - lh.compensatory_time
                -- =
TODO: min=
us "previous month's" accumulated_balance_after_payout if negativ=
e
  =
         , 0) * lh.overtime_ra=
te/100) as extra_overtime
-- balance + extra_overtim=
e
    , lh.bal=
ance
      -- extra_overtime
          + =
(GREATEST(lh.balance
                          - lh.compensatory_time
                      -- <=
/span>TOD=
O: minus "previous month's" accumulated_balance_after_payout if n=
egative
  =
               , 0) * lh.overt=
ime_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.bal=
ance
      -- extra_overtime
          + =
(GREATEST(lh.balance
                          - lh.compensatory_time
                      -- <=
/span>TOD=
O: minus "previous month's" accumulated_balance_after_payout if n=
egative
  =
               , 0) * lh.overt=
ime_rate/100)
          - lh.payout
          - lh.compensatory_time
    -- TODO: plus "pre=
vious month's" accumulated_balance_after_payout
  =
  AS accumula=
ted_balance_after_payout
FROM
     (SELECT cast(generate_series('20=
18-01-01' :: DATE, '=
2018-11-01' :: DATE, '1 month') AS DATE) as start_dat=
e) AS q
         CROSS JOIN
         logg=
ed_hours lh
WHERE lh.date =3D q.=
start_date
ORDER BY lh.date ASC
;
=C2=A0
And have tried to use LAG to use value from "previous row" w= hen calculating "basis_for_extra_overtime":
=C2=A0
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
                   + LEAST(LAG(
                               lh.balance
                                   + (GR=
EATEST(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: min=
us "previous month's" accumulated_balance_after_payout if negativ=
e
  =
         , 0) * lh.overtime_ra=
te/100) as extra_overtime
-- balance + extra_overtim=
e
    , lh.bal=
ance
          + (GREATEST(lh.balance
                          - lh.compensatory_time
                      -- <=
/span>TOD=
O: minus "previous month's" accumulated_balance_after_payout if n=
egative
  =
               , 0) * lh.overt=
ime_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.bal=
ance
          + (GREATEST(lh.balance
                          - lh.compensatory_time
                 , 0) * lh.overtime_r=
ate/100)
          - lh.payout
          - lh.compensatory_time
    -- TODO: plus "pre=
vious month's" accumulated_balance_after_payout
  =
  AS accumula=
ted_balance_after_payout
FROM
     (SELECT cast(generate_series('20=
18-01-01' :: DATE, '=
2018-11-01' :: DATE, '1 month') AS DATE) as start_dat=
e) AS q
         CROSS JOIN
         logg=
ed_hours lh
WHERE lh.date =3D q.=
start_date
ORDER BY lh.date ASC
;
=C2=A0
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 sub= tract the previous row's=C2=A0accumulated_balance_after_payout, but the cal= culation of that is not correct because in order to do that we need the pre= vious row's=C2=A0basis_for_extra_overtime, which again is dependent of the = previous-previous-row, and it kind of gets difficult from here...
=C2=A0
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
Thanks.
=C2=A0
">
--
Andrea= s Joseph Krogh
------=_Part_302_879688681.1542634021653-- ------=_Part_301_1453041292.1542634021639-- ------=_Part_300_1613670134.1542634021639--