Post reply

Conditional filter using multiple columns

  • February 25, 2022 at 10:34 am

    Go to Answer

    #3988754

    Hi Guys,

    Please see the following code, I have 4 proposals in total, two of them are associated with one opportunity and two of them are associated with another opportunity.

    create table #proposal (proposal_id int, opportunity_id int, proposal_number int, change_ref varchar(255), is_primary bit)

    insert into #proposal values 
    (1, 200, 20, 'Original', 1),
    (2, 200, 40, 'CR-123', 0),
    (3, 400, 80, null, 0),
    (4, 400, 90, null, 1)

    select *
    from #proposal

    I would like to apply a filter to only select a valid proposal for each opportunity_id based on the following criteria:

    1. If change_reference = 'Original' this should be the valid proposal
    2. If no rows exist where change_reference = 'Original' then is_primary = 1 should be the valid proposal

    One of the above criteria will always be true.

    So based on the above logic, I would like a query that only returns proposal_id 1 and 4

    Thanks 🙂

    • This topic was modified 2 years, 9 months ago by  Jim-S.

  • February 25, 2022 at 12:58 pm

    Answer

    #3988806

    How about this?

    select distinct p.*
    from #proposal p
    cross apply (
        select top(1) proposal_id
        from #proposal op
        where (op.change_ref = 'Original' or op.is_primary = 1)  
        and opportunity_id=p.opportunity_id
        order by case when change_ref = 'Original' then 1 else 2 end
        ) eligible
    where p.proposal_id = eligible.proposal_id

  • February 25, 2022 at 1:47 pm

    #3988844

    I like it, thank you 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply