dimanche 31 janvier 2021

IF Else or Case Function for SQL select problem

Hi I would like to make a select expression using case or if/else which seems to be a simple solution from logic perspective but I can't seem to get it to work. Basically I am joining against two table here, the first table is customer record with date filter called delivery_date and then the second table for the model scoring table with BIN and update_date parameters.

There are two logics I want to display

  1. Picking the model score that was the month before delivery_date
  2. If model score month before delivery is greater than 50 (Bin > 50) then pick the model score for same month as delivery_date

My 1st logic code is below

with cust as (
    select 
    distinct cust_no, max(del_date) as del_date, min(del_date) as min_del_date, (EXTRACT(YEAR FROM min(del_date)) -1900)*12 + EXTRACT(MONTH FROM min(del_date))  AS upd_seq
    from customer.cust_history   
    group by 1
)
,model as (
    select party_id, model_id, update_date, upd_seq, bin, var_data8, var_data2
    from 
    (
        select 
        party_id, update_date, bin, var_data8, var_data2,
        (EXTRACT(YEAR FROM UPDATE_DATE) -1900)*12 + EXTRACT(MONTH FROM UPDATE_DATE) AS upd_seq,
        dense_Rank() over (partition by (EXTRACT(YEAR FROM UPDATE_DATE) -1900)*12 + EXTRACT(MONTH FROM UPDATE_DATE) order by update_date desc) as rank1
        from 
        (
            select party_id,update_date, bin, var_data8, var_data2
            from model.rpm_model
            group by party_id,update_date, bin, var_data8, var_data2
        ) model
    )model_final
    where rank1 = 1
)
-- Add model scores
-- 1st logic Picking the model score that was the month before delivery date
select *
from 
    (
        select cust.cust_no, cust.del_date, cust.min_del_date, model.upd_seq, model.bin
        from cust
        left join cust 
        on cust.cust_no = model.party_id
        and cust.upd_seq = model.upd_seq + 1
    )a

Now I am struggling in creating the 2nd logic in the same query?.. any assistance would be appreciated

Aucun commentaire:

Enregistrer un commentaire