How to exclude records with certain values in sql select
I need to exclude the entire month where Video or Face-to-Face exist but keep the months where either one of those options is not found. I'm using the NOT EXISTS which works but when I filter based on a date range, it excludes everything because it found a single instance somewhere in the date range
| C1 | c2 | c3 |
|---|---|---|
| 149000 | 2022-06-21 00:00:00.000 | Telephone |
| 149000 | 2022-06-21 00:00:00.000 | Video |
| 149000 | 2022-06-24 00:00:00.000 | Telephone |
| 149000 | 2022-07-08 00:00:00.000 | Telephone |
| 149000 | 2022-07-15 00:00:00.000 | Telephone |
| 149000 | 2022-07-22 00:00:00.000 | Telephone |
| 149000 | 2022-07-29 00:00:00.000 | Telephone |
| 149000 | 2022-08-12 00:00:00.000 | Telephone |
| 149000 | 2022-08-26 00:00:00.000 | Telephone |
| 149000 | 2022-09-01 00:00:00.000 | Face-to-Face |
| 149000 | 2022-09-01 00:00:00.000 | Face-to-Face |
| 149000 | 2022-09-12 00:00:00.000 | Telephone |
| 149000 | 2022-09-12 00:00:00.000 | Video |
The two commented lines are tests lines to see what it would do to my results.
SELECT
c1
,c2
,C3
FROM a1
WHERE
not exists (SELECT * FROM a1 as B WHERE b.c1 = a1.c1 and (b.c3= 'Face-to-Face' or b.c3 = 'Video') )
--and a1.c2 between '2022-06-01' and '2022-06-30')
--and a1.c2 = b.c2)
and c2 between '2022-01-01' and '2022-12-30'
CodePudding user response：
This seems to work:
SELECT
c1
,c2
,C3
FROM a1
WHERE DATEPART(MONTH, c2) not in
(select DATEPART(MONTH, c2) from a1 where c3 in ('Video', 'Face-to-Face'))
CodePudding user response：
Thank you both for your help. Helped me get where I needed to with my data.
SELECT
c1
,c2
,C3
FROM a1
WHERE
DATEADD(month, DATEDIFF(MONTH, 0, c2), 0) not in (
Select DATEADD(month, DATEDIFF(MONTH, 0, c2), 0)
from a1
where c1= a1.c1 and c3 IN ('Video', 'Face-to-Face'))
CodePudding user response：
Maybe something like this?
select main.*
from a1 main
where extract(month from main.c2) not in(
select extract(month from sub.c2)
from a1 sub
where sub.c3 in ('Video', 'Face-to-Face')
)