SQL Multi Conditional CASE WHEN issues [Part 2 - logic adjustments]

Question

I have a follow up question to a question I posted yesterday. Today there are changes to the logic needed. SQL Multi Conditional CASE WHEN issues

Example data table is at the bottom of this post. The original code was written around the following logic:

1. If there is only 1 order number - return the order date
2. If there are >1 alike order numbers and any of those orders was paid with a credit card - return the most recent order date that was paid by credit card
3. If there are >1 alike order numbers and none of the orders were paid with a credit card - return the most recent order date.

Credit to user Gordon Linoff who provided the following code which worked perfectly:

    select o.*
from (select o.*,
             row_number() over (partition by order_number
                                order by (case when payment_method = 'Credit Card' then 1 else 2 end),
                                         order_date desc
                               ) as seqnum
      from orders o
     ) o
where seqnum = 1;

There have been updates to the requirements in the logic which have been expanded to the following:

1. if orders = 1, then use timestamp from order
2. if orders = 2 and one is a credit card, use date from oldest credit card order.
3. if orders = 2 and none are credit card, use the RECENT order date.
4. if orders > 2 and any are credit card, use date from oldest credit card order.
5. if orders > 2 and none are credit card, use the OLDEST order date.

Can this be accomplished by modifying the CASE WHEN in Gordon's code? Or does it require more than that simple of a change.

Original Data Table in question:

Order Number	Payment Method	Order Date
120	Cash	01/01/2021
175	Credit Card	01/02/2021
209	Cash	01/03/2021
209	Credit Card	01/04/2021
209	Personal Check	01/05/2021
209	Credit Card	01/06/2021
209	Cash	01/07/2021
209	Personal Check	01/08/2021
277	Credit Card	01/09/2021
301	Cash	01/10/2021
333	Personal Check	01/11/2021
333	Cash	01/12/2021
333	Cash	01/13/2021
333	Personal Check	01/14/2021
400	Credit Card	01/15/2021
551	Credit Card	01/16/2021
551	Cash	01/17/2021
680	Personal Check	01/18/2021

Answer 1

If I get this logic correctly, it is:

• Take the oldest order date, if any are on credit cards.
• Take the newest order date, if there are exactly two orders and there are no credit cards.
• Otherwise, take the oldest order date.

You can get the auxiliary information using another subquery and window functions:

select o.*
from (select o.*,
             row_number() over (partition by order_number
                                order by (case when cnt = 2 and num_cc = 0 then order_date end) desc,
                                         order_date asc
                               ) as seqnum
      from (select o.*,
                   count(*) over (partition by order_number) as cnt,
                   sum(case when payment_method = 'Credit Card' then 1 else 0 end) over (partition by order_number) as num_cc
            from orders o
           ) o
     ) o
where seqnum = 1;