Find Last, Min Values using Case Statement

Viewed 37

I am trying to find out several values:

  1. orl_adnr_amt When orl_stage_phase_nm = Closed (should be 1900)
  2. Minimum orl_adnr_amt When orl_stage_phase_nm <> Closed (should be 1500)
  3. Maximum orl_adnr_amt When orl_stage_phase_nm <> Closed (should be 1900)

This is my query:

Select
A.oprty_key_nbr,
B.Closed_Won_ADNR,
B.OPEN_Phase_MIN_ADNR,
B.OPEN_Phase_MAX_ADNR,
A.#Changed_ADNR_Amts

            from(select distinct
            A.oprty_key_nbr,
            count (distinct A.orl_adnr_amt) as #Changed_ADNR_Amts

            from isell_prod_view_db.sfdc_opportunity_lifecycle A
            where A.oprty_key_nbr = '983yhjheksi9ru8jdl'
            and A.orl_adnr_amt is not null

            group by 1 ) A
            
            left join (Select distinct
                B.oprty_key_nbr,
                B.orl_adnr_amt,
                B.orl_stage_phase_nm,
                CASE WHEN B.orl_stage_phase_nm = 'Closed - Won' THEN B.orl_adnr_amt else '' end AS Closed_Won_ADNR,
                CASE WHEN B.orl_stage_phase_nm <> 'Closed - Won'THEN MIN(B.orl_adnr_amt) else '' end AS OPEN_Phase_MIN_ADNR,
                CASE WHEN B.orl_stage_phase_nm <> 'Closed - Won'THEN MAX(B.orl_adnr_amt) else '' end AS OPEN_Phase_MAX_ADNR

            from isell_prod_view_db.sfdc_opportunity_lifecycle B    
            where B.oprty_key_nbr = '983yhjheksi9ru8jdl'
            and B.orl_adnr_amt is not null
             group by 1,2,3 ) B
             
on A.oprty_key_nbr = B.oprty_key_nbr

group by 1,2,3,4,5

Here are the results that I am getting: |oprty_key_nbr......|Closed_Won_ADNR|OPEN_Phase_MIN_ADNR|#Changed_ADNR_Amts| |-------------------|---------------|-------------------|------------------| |983yhjheksi9ru8jdl |1900 | |2 | |983yhjheksi9ru8jdl | |1900 |2 | |983yhjheksi9ru8jdl | |1500 |2 |

Here is what I WOULD LIKE to see: |oprty_key_nbr.....|Closed_Won_ADNR|OPEN_Phase_MIN_ADNR|OPEN_Phase_MAX_ADNR|#Changed_ADNR_Amts| |------------------|---------------|-------------------|-------------------|------------------| |983yhjheksi9ru8jdl |1900 |1500 |1900 |2 |

Here is my data: |oprty_key_nbr......|orl_adnr_amt|orl_stage_phase_nm| |-------------------|------------|------------------| |983yhjheksi9ru8jdl |1900 |Closed - Won | |983yhjheksi9ru8jdl |1900 |Open Stage 4 | |983yhjheksi9ru8jdl |1900 |Open Stage 4 | |983yhjheksi9ru8jdl |1500 |Open Stage 4 | |983yhjheksi9ru8jdl |1500 |Open Stage 4 | |983yhjheksi9ru8jdl |1500 |Open Stage 4 | |983yhjheksi9ru8jdl |1500 |Open Stage 3 | |983yhjheksi9ru8jdl |1500 |Open Stage 2 | |983yhjheksi9ru8jdl |? |Open Stage 1 |

Image of tables Appreciate your help!

0 Answers
Related