I have data in a table which looks as follows:

ID  ContactId  EffectiveToDate  EffectiveFromDate  Maximum_EffectiveToDate ID_count
1    68        Null             10-2-2005           11-3-2006                2
2    68        11-3-2006        13-1-2006           11-3-2006                2
3    78        Null             01-01-2000          Null                     1
4    80        10-10-2004       19-09-2003          10-10-2004               1
5    82        15-08-2002       19-06-2001          15-08-2002               3
6    82        10-06-2001       01-01-2000          15-08-2002               3
7    82        Null             20-10-2004          15-08-2002               3
8    85        10-06-2005       10-05-2004          10-06-2005               2
9    85        11-07-2004       10-04-2003          10-06-2005               2

ID-count is the count of ContactId column. I want to select the rows in a way that ContactId appears once (no repetition) and the condition is:

1. pick data where ContactId that appears one time (i.e. ID_count =1)
2. If ContactId appears more than one time (i.e. ID_count >1) then pick only rows where EffectiveToDate is Null
3. If ContactId appears more than one time (i.e. ID_count > 1) and EffectiveToDate is not null then pick the row where EffectiveToDate value is equal to Maximum_EffectiveToDate

I wrote the following SQL code to satisfy above-mentioned conditions:

Select * from table
where (ID_count = 1 or (ID_count > 1 AND EffectiveToDate is Null) or
       (ID_count > 1 AND EffectiveToDate is not Null AND EffectiveToDate = Maximum_EffectiveToDate))

The problem with the above code is that it returns rows where ContactId 82 and 68 two times. One row with EffectiveToDate with Null and one row when EffectiveToDate is equal to Maximum_EffectiveToDate. Is there any way in SQL where we stop the remaining conditions to check if the first condition is true?

CodePudding user response：

You could use row_number to assign a rank for each row and return the first qualifying row:

select ID, ContactId, EffectiveToDate, EffectiveFromDate, Maximum_EffectiveToDate, ID_count
from (
  select *, 
    Row_Number() over(
      partition by ContactId
      order by ID_count, EffectiveToDate, Iif(EffectiveToDate = Maximum_EffectiveToDate, 0, 1)
    ) rn
  from t
)t
where rn = 1;

CodePudding user response：

you can try using UNION to achieve desired result.

select * from table where ContactId in (
    select ContactId from table group by ContactId having count(1) = 1)
union
select * from table where ContactId in (
    select ContactId from table group by ContactId having count(1) > 1)
and isnull(EffectiveToDate, '') = ''
union
select * from (
    select row_number() over (partition by ContactId order by EffectiveToDate desc) rn,
        * from table where isnull(EffectiveToDate, '') != '')
where rn = 1 and ContactId not in (select ContactId from table group by ContactId having count(1) = 1)

CodePudding user response：

Amend the third condition to check for the EXISTence of a null record

select *
from t
where id_count = 1 or
      (id_count > 1 and effectivetodate is null) or
      (not exists(select 1 from t t1 where t1.contactid = t.contactid and t1.effectivetodate is null) and effectiveToDate = Maximum_EffectiveToDate)
;