CODEWITHSUNDEEP
sqloracle11g
amincahcepu
amincahcepu

Reputation: 43

Oracle SQL Lag Function using previous row calculation/condition

I'm trying to calculate using constanta as first value
and then the second row will calculate based on first row calculation result and so on(3rd row using second row result)

the base table look like this, already ordered by date as key

CREATE TABLE T42
    ("Daytime" timestamp, "A" int, "B" int, "C" int)
;

INSERT ALL 
    INTO T42 ("Daytime", "A", "B", "C")
         VALUES ('01-Apr-2020 12:00:00 AM', -7021, 151.6445, -15)
    INTO T42 ("Daytime", "A", "B", "C")
         VALUES ('02-Apr-2020 12:00:00 AM', -7037, 152.3164, -15)
    INTO T42 ("Daytime", "A", "B", "C")
         VALUES ('03-Apr-2020 12:00:00 AM', -7053, 155.0313, -16)
    INTO T42 ("Daytime", "A", "B", "C")
         VALUES ('04-Apr-2020 12:00:00 AM', -7065, 151.9258, -11)
SELECT * FROM dual
;

For the first april,the constanta is 7005.98.

the result i need should be like this

+----------+-------+-----+-----+---------+----------+
| Daytime  |   A   |  B  |  C  |  CALC1  | PREV_SAL |
+----------+-------+-----+-----+---------+----------+
| 4/1/2020 | -7021 | 152 | -15 | 7020.98 |        0 |
| 4/2/2020 | -7037 | 152 | -15 | 7035.98 |  7020.98 |
| 4/3/2020 | -7053 | 155 | -16 | 7051.98 |  7035.98 |
| 4/4/2020 | -7065 | 152 | -11 | 7062.98 |  7051.98 |
+----------+-------+-----+-----+---------+----------+

My First try for 1st April is to use this

SELECT "Daytime",
    A ,
    B ,
    C ,
    CASE
      WHEN "Daytime" = '1-APR-20'
      THEN
        CASE
          WHEN 7005.98 +(C*-1)>7080
          THEN 7080
          ELSE 7005.98 +(C*-1)
        END          
       ELSE    null       
    END calc1

  FROM T42

but then i try using LAG function to get previous result but it error invalid identifier on calc1

SELECT "Daytime",
    A ,
    B ,
    C ,
    CASE
      WHEN "Daytime" = '1-APR-20'
      THEN
        CASE
          WHEN 7005.98 +(C*-1)>7080
          THEN 7080
          ELSE 7005.98 +(C*-1)
        END
      WHEN "Daytime" > '1-APR-20'
      THEN 
        CASE
          WHEN calc1 +(C*-1)>7080
          THEN 7080
          ELSE calc1 +(C*-1)
        END 

       ELSE    null       
    END calc1

  FROM T42

I also try using CTE, it good for 2nd april, but for the rest are null

with cte as
(
SELECT "Daytime",
    A ,
    B ,
    C ,
    CASE
      WHEN "Daytime" = '1-APR-20'
      THEN
        CASE
          WHEN 7005.98 +(C*-1)>7080
          THEN 7080
          ELSE 7005.98 +(C*-1)
        END

    END calc1

  FROM T42
  )

  select 

  cc."Daytime",
  A,
  B,
  C,
  cc.calc1,
  LAG(cc. calc1, 1, 0) OVER (ORDER BY cc."Daytime") AS prev_sal,
  CASE
    WHEN cc."Daytime" > '1-APR-20'
    THEN
      CASE
        WHEN LAG(cc. calc1, 1, 0) OVER (ORDER BY cc."Daytime")+(cc.C*-1) >7080
        THEN 7080
        ELSE LAG(cc. calc1, 1, 0) OVER (ORDER BY cc."Daytime")+(cc.C*-1)
      END
  END calc2

  from cte cc

cte result

+---------+-------+-----+-----+---------+----------+---------+
| Daytime |   A   |  B  |  C  |  CALC1  | PREV_SAL |  CALC2  |
+---------+-------+-----+-----+---------+----------+---------+
| 00:00.0 | -7021 | 152 | -15 | 7020.98 | 0        | (null)  |
| 00:00.0 | -7037 | 152 | -15 | (null)  | 7020.98  | 7035.98 |
| 00:00.0 | -7053 | 155 | -16 | (null)  | (null)   | (null)  |
| 00:00.0 | -7065 | 152 | -11 | (null)  | (null)   | (null)  |
+---------+-------+-----+-----+---------+----------+---------+

is it possible to do this kind of calculation?

here my fiddle link

Upvotes: 1

Views: 378

Answers (1)

Nick
Nick

Reputation: 147146

You can generate the results you want with a recursive CTE; first generating a list of row numbers according to Daytime to make recursion easier, then computing CALC1 from the previous value and the current C, and PREV_SAL from the previous CALC1:

WITH CTE AS (
  SELECT T42.*,
         ROW_NUMBER() OVER (ORDER BY Daytime) AS rn
  FROM T42
),
CTE2 (Daytime, A, B, C, CALC1, PREV_SAL, rn) AS (
  SELECT Daytime, A, B, C,
         LEAST(7005.98 - C, 7080.0) AS CALC1,
         0 AS PREV_SAL,
         rn
  FROM CTE
  WHERE rn = 1
  UNION ALL 
  SELECT c.Daytime, c.A, c.B, c.C,
         LEAST(c2.CALC1 - c.C, 7080.0),
         c2.CALC1,
         c.rn
  FROM CTE c
  JOIN CTE2 c2 ON c.rn = c2.rn + 1
)
SELECT Daytime, A, B, C, CALC1, PREV_SAL
FROM CTE2

Output:

DAYTIME                 A       B       C       CALC1       PREV_SAL
2020-04-01 00:00:00.0   -7021   152     -15     7020.98     0
2020-04-02 00:00:00.0   -7037   152     -15     7035.98     7020.98
2020-04-03 00:00:00.0   -7053   155     -16     7051.98     7035.98
2020-04-04 00:00:00.0   -7065   152     -11     7062.98     7051.98

Demo on SQLFiddle

Upvotes: 1

Related Questions