Hi, I have user records all in a single table and each user can have many records. I only want my query results to show user records for any user ID if NONE of the records for user A have a date field with a date >= 2017-01-01.
So if say there are multiple records for user A in my table:
RID USER DATE
1 A 2015-01-01
2 A 2016-01-01
3 A 2017-01-01
Since there is a record with a date >= 2017-01-01, then I do NOT want to see ANY of the records for user A above.
In the case below, I want to see ALL THREE records for user A in my query results, because NONE of the records have a date in them >=2017-01-01.
RID USER DATE
1 A 2015-01-01
2 A 2016-01-01
3 A 2014-01-01
I tried a NOT EXIST clause but its not working. It only eliminates the records with the date >= 2017-01-01.
please always provide schema and data to test with?
create table #users(rid int, username varchar(50), date datetime)
insert into #users
select 1, 'A' , '2015-01-01' union
select 2, 'A' ,'2016-01-01' union
select 3, 'A', '2017-01-01' union
select 4, 'B' , '2015-01-01' union
select 5, 'B' ,'2016-01-01' union
select 6, 'B', '2014-01-01'
;with cteExclude
as
(
select *
from #users
where date >= '2017-01-01'
)
select *
from #users u where not exists (select 1 from cteExclude t where t.username = u.username)
drop table #users
SELECT
u.rid,
u.username,
u.date
FROM
#users u
WHERE
NOT EXISTS (
SELECT 1
FROM #users u2
WHERE
u.username = u2.username
AND u2.date >= '2017-01-01'
);
The #TableName is a temporary table as you say, and yes changing that for your actual table is probably all that is needed.
Folk here like to test the solutions they offer and it would be appreciated if you provide sample data with any question you ask, and the expected-outcome results data too ... otherwise multiple people here run the risk that they are all winding up doing that at the same time. You'll get more answers too ... unless I happen to have time I am unlikely to bother to answer a question where I first have to spend 5 minutes creating test data AND then write a solution