Query Help - New to SQL

1

I am sure there is an easy way to do this...just haven't been able to wrap my mind around it!

My table has the following columns: ClientID, StartDate, EndDate. The table may contain multiple entries for each ClientID e.g.

ClientID StartDate EndDate
1 Jan 1, 2018 Jan 10, 2018
2 Jan 5, 2018 Jan 6, 2018
2 Feb 1, 2018 Feb 10, 2018
3 Feb 4, 2018 Feb 6, 2018
3 March 6, 2018

I am looking for a query that does not return any instance of a client ID if one of the records has a null entry for EndDate. In the example above, I only want ClientID 1 and 2.

Thanks in advance!

2

create table dbo.test (
ClientID int,
StartDate date,
EndDatedate date
)

insert into dbo.test values
(1,'20180101','20180110')
,(2,'20180105','20180106')
,(2,'20180201','20180210')
,(3,'20180204','20180204')
,(3,'20180306',NULL)

select *
from dbo.test as fox
where exists (
select * from (
select ROW_NUMBER() over (partition by ClientID order by EndDatedate ) rn,
ClientID
,EndDatedate
from dbo.test
) main
where
main.rn = 1
and main.EndDatedate is not null
and main.ClientID = fox.ClientID
)

3

select TN.ClientID, TN.StartDate, TN.EndDate
from dbo.tableName TN
where not exists(select 1 from dbo.tableName TN2 where TN2.ClientID = TN.ClientID and TN2.EndDate IS NULL)