1

oracle query to loop through multiple records and provide the results

Please help qrite an oracle query to achieve above results.

input data

ID ABRV NXTFILL DAYSOUT_INT DAYSOVER_INT TIME
123 abc null null null 21
123 abc 15 14 0 6
123 abc 2 1 0 4
123 abc 4 0 27 0
456 xyz null null null 4
456 xyz 2 1 0 2
456 xyz 2 1 0 0
888 pqr null null null 10
888 pqr 10 9 0 0
888 def null null null 4
888 def 4 3 0 0
999 ttt null null null 23
999 ttt 23 0 7 0
999 sss null null null 25
999 sss 25 0 5 0
999 rrr null null null 20
999 rrr 20 0 10 0
444 ddd null null null 3
444 ddd 3 0 87 0
444 eee null null null 3
444 eee 3 0 87 0

expected output data

ID ABRV NXTFILL DAYSOUT_INT DAYSOVER_INT TIME DAYSOVERX daysout_sk daysover_ck
123 abc null null null 21 0 null null
123 abc 15 14 0 6 12 0 12
123 abc 2 1 0 4 26 0 26
123 abc 4 0 27 0 27 0 27
456 xyz null null null 4 0 null null
456 xyz 2 1 0 2 0 1 0
456 xyz 2 1 0 0 0 1 0
888 pqr null null null 10 0 null null
888 pqr 10 9 0 0 0 9 0
888 def null null null 4 0 null null
888 def 4 3 0 0 0 3 0
999 ttt null null null 23 0 null null
999 ttt 23 0 7 0 7 0 7
999 sss null null null 25 0 null null
999 sss 25 0 5 0 5 0 5
999 rrr null null null 20 0 null null
999 rrr 20 0 10 0 10 0 10
444 ddd null null null 3 0 null null
444 ddd 3 0 87 0 87 0 87
444 eee null null null 3 0 null null
444 eee 3 0 87 0 87 0 87

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.

Improve this question
4
  • 2
    Please help us help you. Explain the logic you want to apply, show your attempt and tell us what doesn't work as expected. You might have to be fast because such vague questions are often closed very quickly. Commented Mar 8 at 4:23
  • Added additional details with the query i created, please help Commented Mar 8 at 4:32
  • 1
    Please explain in words your logic which is not immediately clear. Deciphering code that returns undesired results may not help us help you. Also, do explain not working as expected which is not a helpful descriptor. Instead, provide in words the undesired result or error. Commented Mar 8 at 4:40
  • How do you compute DAYSOVERX? Please elaborate. Commented Mar 8 at 15:59

1 Answer 1

Reset to default
2

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 :

ID ABRV NXTFILL DAYSOUT_INT DAYSOVER_INT TIME DAYSOVER_X DAYSOUT_SK DAYSOVER_CK
123 abc null null null 21 0 null null
123 abc 15 14 0 6 12 0 12
123 abc 2 1 0 4 26 0 26
123 abc 4 0 27 0 27 0 27
444 eee null null null 3 0 null null
444 eee 3 0 87 0 87 0 87
444 ddd null null null 3 0 null null
444 ddd 3 0 87 0 87 0 87
456 xyz null null null 4 0 null null
456 xyz 2 1 0 2 0 1 0
456 xyz 2 1 0 0 0 1 0
888 pqr null null null 10 0 null null
888 pqr 10 9 0 0 0 9 0
888 def null null null 4 0 null null
888 def 4 3 0 0 0 3 0
999 ttt null null null 23 0 null null
999 ttt 23 0 7 0 7 0 7
999 sss null null null 25 0 null null
999 sss 25 0 5 0 5 0 5
999 rrr null null null 20 0 null null
999 rrr 20 0 10 0 10 0 10

fiddle

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you, suggested query working for most of the scenario's. For two scenario's, query is not working as expected. i added to the question please check.
@OracleLearner Just posted the updated code - the only difference is including ABRV column in Partition By part oof the Sum() Over() analytic function. Hope this will work. Just as a remark - you should realy explain/describe the logic that should be implemented to get the result (at least something like in my answer 1., 2., 3.). It is not always possible to help without it.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.