oracle query to loop through multiple records and provide the results

oracle query to loop through multiple records and provide the results

Please help qrite an oracle query to achieve above results.

input data

expected output data

Please help write an oracle query to achieve above results.

My logic is as follows-

SELECT
    id,
    ABRV,
    NXTFILL,
    DAYSOUT_INT,
    DAYSOVER_INT,
    TIME,
    CASE
        WHEN DAYSOVER_INT IS NOT NULL THEN DAYSOVER_INT
        ELSE 0
    END AS DAYSOVERX,
    SUM(DAYSOUT_INT) OVER (PARTITION BY id ORDER BY TIME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS daysout_sk,
        SUM(DAYSOVER_INT) OVER (PARTITION BY id ORDER BY TIME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS daysover_ck
FROM
    test1 where
ORDER BY
    ID, TIME;

not working as expected.

If I got it right - this could help:

--       S a m p l e    D a t a :
WITH
  tbl (ID, ABRV, NXTFILL, DAYSOUT_INT, DAYSOVER_INT, TIME) AS
    ( Select 123,   'abc',  null,   null,   null,   21 From Dual Union All
      Select 123,   'abc',  15,     14,     0,      6 From Dual Union All
      Select 123,   'abc',  2,      1,      0,      4 From Dual Union All
      Select 123,   'abc',  4,      0,      27,     0 From Dual Union All
      Select 456,   'xyz',  null,   null,   null,   4 From Dual Union All
      Select 456,   'xyz',  2,      1,      0,      2 From Dual Union All
      Select 456,   'xyz',  2,      1,      0,      0 From Dual Union All
      Select 888,   'pqr',  null,   null,   null,   10 From Dual Union All
      Select 888,   'pqr',  10,     9,      0,      0 From Dual Union All
      Select 888,   'def',  null,   null,   null,   4 From Dual Union All
      Select 888,   'def',  4,      3,      0,      0 From Dual 
  )

1. DAYSOVER_X column is difference of running sums of DAYSOVER_INT and DAYSOUT_INT per ID and ABRV. If result is negative put 0 (that's why Greatest function is there)
2. DAYSOUT_SK column is null if NXTFILL is null - otherwise if the running sum of DAYSOVER_INT is greater or equal than running sum of DAYSOUT_INT put 0 if not take the value of DAYSOUT_INT.
3. DAYSOVER_CK column is null if NXTFILL is null - otherwise the same as 1.DAYSOVER_X ...

--       S Q L : 
Select      ID, ABRV, NXTFILL, DAYSOUT_INT, DAYSOVER_INT, TIME,  
            GREATEST( Case When DAYSOVER_INT Is Not Null 
                          Then  Sum(Nvl(DAYSOVER_INT, 0)) 
                                   Over(Partition By ID, ABRV Order By TIME Desc
                                    Rows Between Current Row And Unbounded Following)
                             - 
                               Sum(Nvl(DAYSOUT_INT, 0)) 
                                   Over(Partition By ID, ABRV Order By TIME Desc
                                    Rows Between Current Row And Unbounded Following) 
                      Else 0
                      End, 0
                  ) "DAYSOVER_X",
          --
            Case When NXTFILL Is Null
                 Then Null
            Else 
                Case When Sum(Nvl(DAYSOVER_INT, 0)) 
                                   Over(Partition By ID, ABRV Order By TIME Desc
                                    Rows Between Current Row And Unbounded Following)
                        >= 
                               Sum(Nvl(DAYSOUT_INT, 0)) 
                                   Over(Partition By ID, ABRV Order By TIME Desc
                                   Rows Between Current Row And Unbounded Following) 
                   Then 0                  
               Else DAYSOUT_INT
               End
            End "DAYSOUT_SK", 
       --
         Case When NXTFILL Is Null
                 Then Null
         Else
             GREATEST( Case When DAYSOVER_INT Is Not Null 
                          Then  Sum(Nvl(DAYSOVER_INT, 0)) 
                                   Over(Partition By ID, ABRV Order By TIME Desc
                                    Rows Between Current Row And Unbounded Following)
                             - 
                               Sum(Nvl(DAYSOUT_INT, 0)) 
                                   Over(Partition By ID, ABRV Order By TIME Desc
                                    Rows Between Current Row And Unbounded Following) 
                      Else 0
                      End, 0
                  )
        End "DAYSOVER_CK"
From        tbl
Order By    ID, ABRV Desc, TIME Desc

R e s u l t :

fiddle