Oracle query to keep looking until value is not 0 anymore-CodePudding

I am using Oracle 11. I have 2 tables

TblA with columns id, entity_id and effective_date. TblADetail with columns id and value. If Value = 0 for the effective date, I want to keep looking for the next effective date until I found value <> 0 anymore.

The below query only look for value on 3/10/21. If value = 0, I want to look for value on 3/11/21. If that's not 0, I want to stop. But, if that's 0, I want to look for value on 3/12/21. If that's not 0, I want to stop. But, if that's 0, I want to keep looking until value is not 0. How can I do that ?

SELECT SUM(pd.VALUE) 
FROM TblA p,TblADetail pd
WHERE     p.id = pd.id
AND p.effective_date = to_date('03/10/2021','MM/DD/YYYY')
AND TRIM (p.entity_id) = 123

Sample data: TblA

id   entity_id effective_date
1    123       3/10/21
2    123       3/11/21
3    123       3/12/21

TblADetail

id value
1  -136
1  136
2  2000
3  3000

In the above data, for entity_id 123, starting from effective_date 3/10/21, I would like to to return value 2000 (from TblADetail) effective_date 3/11/21.

So, starting from a certain date, I want the results from the minimum date that has non-zero values.

Thank you.

CodePudding user response：

Straightforward; read comments within code. Sample data in lines #1 - 13, query begins at line #14.

SQL> with
  2  -- sample data
  3  tbla (id, entity_id, effective_date) as
  4    (select 1, 123, date '2021-03-10' from dual union all
  5     select 2, 123, date '2021-03-11' from dual union all
  6     select 3, 123, date '2021-03-12' from dual
  7    ),
  8  tblb (id, value) as
  9    (select 1, -136 from dual union all
 10     select 1,  136 from dual union all
 11     select 2, 2000 from dual union all
 12     select 3, 3000 from dual
 13    ),
 14  tblb_temp as
 15    -- simple grouping per ID
 16    (select id, sum(value) value
 17     from tblb
 18     group by id
 19    )
 20  -- return TBLA values whose ID equals TBLB_TEMP's minimum ID
 21  -- whose value isn't zero
 22  select a.id, a.entity_id, a.effective_date
 23  from tbla a
 24  where a.id = (select min(b.id)
 25                from tblb_temp b
 26                where b.value > 0
 27               );

        ID  ENTITY_ID EFFECTIVE_
---------- ---------- ----------
         2        123 03/11/2021

SQL>

CodePudding user response：

You can do what you need to do by grouping the sum on the effective date, and using the MIN analytic function to find the earliest date. Once you've done that, you simply need to select the date that matches the earliest date.

E.g.:

with     tbla as (select 1 id, ' 123' entity_id, to_date('10/03/2021', 'dd/mm/yyyy') effective_date from dual union all
                  select 2 id, ' 123' entity_id, to_date('11/03/2021', 'dd/mm/yyyy') effective_date from dual union all
                  select 3 id, ' 123' entity_id, to_date('12/03/2021', 'dd/mm/yyyy') effective_date from dual),
  tbla_detail as (select 1 id, -136 value from dual union all
                  select 1 id,  136 value from dual union all
                  select 2 id, 2000 value from dual union all
                  select 3 id, 3000 value from dual),
      results as (select a.effective_date,
                         sum(ad.value) sum_value,
                         min(case when sum(ad.value) != 0 then a.effective_date end) over () min_effective_date
                  from   tbla a
                         inner join tbla_detail ad on a.id = ad.id
                  where  a.effective_date >= to_date('10/03/2021', 'dd/mm/yyyy')
                  and    trim(a.entity_id) = '123'
                  group by a.effective_date)
select sum_value
from   results
where  effective_date = min_effective_date;

 SUM_VALUE
----------
      2000