Return one record per criteria

Question

Hello folks,
I am struggling with a solution for getting one record per criteria.
In the attached example, I would like to get one record per "nbr".
I have started with this, but obviously something does not work as it should :)

SELECT t2.*,  
 CASE   
 WHEN t2.duplicate_flag=1 AND (t2.country=t2.country_active OR t2.country=t2.country_inactive) THEN 1 ELSE t2.duplicate_flag END nbr_match  
FROM  
(SELECT t1.*,  
 CASE d.duplicate WHEN 1 THEN 1 ELSE 999 END duplicate_flag  
FROM [Sheet1$] t1  
JOIN  
(SELECT nbr, COUNT(*) as duplicate  
FROM [Sheet1$]  
GROUP BY nbr) d  
ON t1.nbr=d.nbr) t2  

To explain - I need one record per "nbr" as I mentioned. So, if I had only one row per "nbr" it is OK.
First, I tried to define "duplicate flag" - if count(*)=1 THEN 1 ELSE 99 END.
Then, I tried to modified these "99" rows with CASE WHEN statements but without success.
Now, if I have more than one record per "nbr" I need to follow the next rules in order to get unique record:

1. IF "country"="country_active" return me that row (per "nbr").
2. ELSEIF "country"="country_inactive" return me that row
3. If I have two the same countries between the "country" and the "country_active" THEN look at the MAX "expiration_date_active" OR MAX "expiration_date_inactive" and return me that record. 4. IF there is no match between "country"<>"country_active" AND "country"<>"country_inactive" return the column with the MAX date either from "expiration_date_active" OR "expiration_date_inactive"

In the end, the final table should look like:

Thank you in advance.

Original view:

I can not upload the excel example, don't know why. 19643-return-one-record-per-nbr.txt
I guess this .txt file will be useful.

Answer 1

For this new rule maybe you will solve, in the row_number calculation, also the sorting for expiration_date_inactive desc

with cte as
 (
     select
         row_number() over 
         (
             partition by 
                 nbr 
             order by 
                 nbr
                 , 
                 (
                     case 
                         when country=country_active then 1 
                         when country=country_inactive then 2
                         else 3 
                     end
                 )
                 ,
                 (
                     case
                         when expiration_date_active >= expiration_date_inactive
                             then expiration_date_active
                             else expiration_date_inactive
                     end
                 ) desc
                 , expiration_date_inactive desc
         ) as rn
         , *
     from
         Mytable
 )
 select * from cte
where rn = 1
order by nbr

Answer 2

Maybe it is the same, but for Rule 3 - first I need to find a match between the countries and then to look at MAX date
Rule 4 - since I do not need to look for the countries match if "country" is different from both (country_active and country_inactive) just pick me one with the MAX date.
Pictures below as examples.

Did I clarify now? :)

Answer 3

Hello.
Could this be a solution for you?

with cte as
(
    select
        row_number() over 
        (
            partition by 
                nbr 
            order by 
                nbr
                , 
                (
                    case 
                        when country=country_active then 1 
                        when country=country_inactive then 2
                        else 3 
                    end
                )
                ,
                (
                    case
                        when expiration_date_active >= expiration_date_inactive
                            then expiration_date_active
                            else expiration_date_inactive
                    end
                ) desc
        ) as rn
        , *
    from
        Mytable
)
select * from cte
where rn = 1
order by nbr

Answer 4

One more clarification - for the MAX date, first I would look at the "expiration_date_active".
If they are the same then go and look at MAX "expiration_date_inactive".

Answer 5

Sorry, my bad. I think it works very well. I made a mess with the first two records. I thought it is the same.

Nice work man!

Thank you very much.