-1

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'
Tony Pham
  • 1
  • 2
  • `exclude the entire month where Video or Face-to-Face exist` literally uses the word OR. So, if one OR other exists, exclude the month. – MatBailie Nov 17 '22 at 22:43
  • `but keep the months where either one of those options is not found` uses the word EITHER, which effectively means OR. So, KEEP months where either value is missing. – MatBailie Nov 17 '22 at 22:45
  • How do you exclude a month where A OR B is present, yet keep a month where A OR B is missing? Neither present: keep. Both present: excluded. Only A: keep AND exclude. Only B: keep AND exclude. – MatBailie Nov 17 '22 at 22:47

3 Answers3

0

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')
)
Orkad
  • 630
  • 5
  • 15
b_loy
  • 1
  • 1
  • Pleae don't link images of data or results. Include them in the answer as text and use the inbuilt text formatting. – MatBailie Nov 17 '22 at 22:54
  • I don't like your answer. You have a link to an image of data. Which is against SO's best practices, for a litany of reasons. Please read this; [Please do not upload images of code/data/errors.](//meta.stackoverflow.com/q/285551) – MatBailie Nov 18 '22 at 16:49
0

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'))
Tony Pham
  • 1
  • 2
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 21 '22 at 03:03
-1

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')
)
Abe
  • 11
  • 6
  • Extract didn't work in SMSS or Cache but it gave me an idea on using MONTH(date-expression). I'll see what happens. – Tony Pham Nov 17 '22 at 19:14