I am trying to find out several values:
- orl_adnr_amt When orl_stage_phase_nm = Closed (should be 1900)
- Minimum orl_adnr_amt When orl_stage_phase_nm <> Closed (should be 1500)
- 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!