In Oracle SQL, How to query to extract latest date values without missing out values from join table-CodePudding

I have two different tables in DB, SR table and Quotestable.

I have done left join both the table in the below query,

Select 
sr.sr#, sr.sub_status, sq.quotes_status, sr.Equipment_status
from svcops_emea.s_sr sr
left join svcops_emea.s_quotes sq on sq.sr# = sr.sr#
Where s.srtype = 'Repair';

I'm getting the extracts with the duplicates because for the same SR(1-5676068874) there is two different quote_status(Quote-Cancelled, Quote-Accepted)

Now I changed my query below, I'm getting unique data based on the latest 'created' date from the Quotes table but in extract, it's missing SRs(1-8376068836) because it's not present in the Quotes table.

Select sr.sr#, sr.sub_status, sq.quotes_status, sr.Equipment_status 
from svcops_emea.s_sr sr 
left join svcops_emea.s_quotes sq on sq.sr# = sr.sr# 
inner join 
(
  Select sr#, max(Created) as maxdate 
  from svcops_emea.s_quotes 
  group by sr#
) tm on sq.sr# = tm.sr# and sq.Created = tm.maxdate and sq.sr# = sr.sr# 
Where s.srtype = 'Repair'

Could anyone please help me to query this condition where I can get the unique data based on a date without missing out on any SRs from SR Table?

CodePudding user response：

If you rank rows in S_QUOTES per CREATED date value in descending order per each SR#, you can then outer-join that result to the S_SR table.

See comments within code. The 1st part of query represents sample data; query begins at line #13.

SQL> with
  2  s_sr (sr#, sub_status, equipment_status) as
  3    (select '1-7576068874', null        , 'System running' from dual union all
  4     select '1-5676068874', 'In Process', 'System down'    from dual union all
  5     select '1-8376068836', 'In Process', 'System down'    from dual
  6    ),
  7  s_quotes (sr#, quotes_status, created) as
  8    (select '1-7576068874', 'Cancel'         , date '2021-08-03' from dual union all
  9     select '1-7576068874', 'Open'           , date '2021-08-04' from dual union all
 10     select '1-5676068874', 'Quote-Cancelled', date '2021-02-03' from dual union all
 11     select '1-5676068874', 'Quote-Cancelled', date '2021-02-22' from dual
 12    ),

 13  t_quotes as
 14    -- rank S_QUOTES rows per CREATED column (for each SR#)
 15    (select q.*,
 16       row_number() over (partition by q.sr# order by created desc) rn
 17     from s_quotes q
 18    )
 19  -- your 1st query, slightly modified - it joins T_QUOTES and has another join condition
 20  select sr.sr#, sr.sub_status, sq.quotes_status, sr.Equipment_status
 21  from s_sr sr left join t_quotes sq on sq.sr# = sr.sr#
 22                                    and sq.rn = 1;

SR#          SUB_STATUS QUOTES_STATUS   EQUIPMENT_STAT
------------ ---------- --------------- --------------
1-5676068874 In Process Quote-Cancelled System down
1-7576068874            Open            System running
1-8376068836 In Process                 System down

SQL>

CodePudding user response：

The easiest solution is a lateral join (OUTER APPLY) where you can select the latest sq row for a sr row.

select sr.sr#, sr.sub_status, sq.quotes_status, sr.equipment_status
from svcops_emea.s_sr sr
outer apply
(
  select *
  from svcops_emea.s_quotes
  where s_quotes.sr# = sr.sr#
  order by created desc
  fetch first row only
) sq
where sr.srtype = 'repair';

Your own query is close, too. Only, don't join. Use INor EXISTS for the lookup in the ON clause:

Select sr.sr#, sr.sub_status, sq.quotes_status, sr.Equipment_status 
from svcops_emea.s_sr sr 
left join svcops_emea.s_quotes sq
  on sq.sr# = sr.sr# 
  and (sq.sr#, sq.Created) in
  ( 
    Select sr#, max(Created)
    from svcops_emea.s_quotes 
    group by sr#
  )
Where s.srtype = 'Repair';

Anoter solution would be to get the lastest status with Oracle's KEEP LAST aggregation function. And yet another would use a window function, e.g. MAX OVER.