Home > Back-end >  SQL Excluding data based on certain criteria for a date range
SQL Excluding data based on certain criteria for a date range

Time:11-18

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'))

Result Set

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')
)
  • Related