This query produces the results I want.
Is there a less wordy or more effective way to write this query?
Criteria: 1. Vendor in detail record must have a record in TRANSEFFECTIVE 2. Get the most recent (begin_frame) record from TRANSDETAIL 3. Tells me if the vendor has any other categories in the TRANSMODAL table.
In this case,
Since DDAN has a detail record and has record in TRANSEFFECTIVE, report that DDAN has an AIR,GROUND,and SEA modal record.
Since POKE has a detail record and has record in TRANSEFFECTIVE, report that POKE has only AIR and GROUND modal records.
select vend_code, vend_numb, description, freight_mode from ( select vend_code, vend_numb, begin_frame, freight_mode, freight_count, description, row_number() over (partition by vend_code, vend_numb, (case modal_class_code when 'A1' then 1 when 'A2' then 1 when 'G1' then 2 when 'S1' then 3 end) order by vend_code, vend_numb) as modal_class_count from ( select td.modal_vend_code as vend_code, td.modal_vend_numb as vend_numb, td.modal_begin_frame as begin_frame, td.modal_description as description, te.modal_vend_begin_frame as te_begin, te.modal_vend_close_frame as te_close, tm.modal_class_code as modal_class_code, case tm.modal_class_code when 'A1' then 'AIRFREIGHT' when 'A2' then 'AIRFREIGHT' when 'G1' then 'GROUND' when 'S1' then 'SEABOUND' else 'UNKNOWN' end as freight_mode, rank() over (partition by td.modal_vend_code, td.modal_vend_numb order by td.modal_begin_frame desc) as freight_choice, count(*) over (partition by td.modal_vend_code, td.modal_vend_numb) freight_count from transdetail td inner join transeffective te on td.modal_vend_code = te.modal_vend_code and td.modal_vend_numb = te.modal_vend_numb inner join transmodal tm on td.modal_vend_code = tm.modal_vend_code and td.modal_vend_numb = tm.modal_vend_numb ) where freight_choice=1 ) where modal_class_count=1 ;