Finding records that are only before a given date (SQL)

Question

I've tried multiple queries but none of them work. It's probably really simple.

Here's an example table :

ordernr debnaam     debnr   orddat
1       Coca-Cola   123     2019-02-07
12      Altec       456     2019-02-07
123     Coca-Cola   123     2016-01-01
1234    Brady       789     2015-03-18

So the point is to find the clients (debnaam) that haven't ordered since the last 2 years. In my example the only record should be Brady.

I've tried following query :

SELECT a.ordernr, a.debnaam, a.debnr, a.orddat 
FROM orkrg as a 
WHERE NOT EXISTS(SELECT b.debnr 
                 FROM orkrg as b 
                 WHERE a.ordernr = b.ordernr 
                 AND b.orddat > CONVERT(date, dateadd(year,-2,getdate())))

Or with a Left outer join :

SELECT * 
FROM (
      SELECT orkrg.ordernr, orkrg.debnaam, orkrg.debnr, orkrg.orddat 
      FROM orkrg 
      WHERE orkrg.orddat < CONVERT(date, dateadd(year,-2,getdate()))
     ) AS a
LEFT OUTER JOIN 
     (
      SELECT orkrg.ordernr, orkrg.debnaam, orkrg.debnr, orkrg.orddat 
      FROM orkrg 
      WHERE orkrg.orddat > CONVERT(date, dateadd(year,-2,getdate()))
     ) as b 
  ON a.ordernr = b.ordernr

But I always get following result :

ordernr debnaam    debnr    orddat
123     Coca-Cola  123      2016-01-01
1234    Brady      789      2015-03-18

Could someone please help me?

Thanks!

Check out not exist, do a where > 2 years old and use the NOT exist for a query in the past 2 years to exclude the ones that have ordered in recent 2 years https://www.tutorialgateway.org/sql-not-exists-operator/ — Brad, Feb 07 '19 at 15:46
Also you are converting your dates to date (assuming to remove year), unless you need to check by the couple hours possibly on the exact day 2 years ago you shouldnt need to do that and will take longer to run — Brad, Feb 07 '19 at 15:50
Which dbms are you using? (CONVERT, dateadd etc are product specific functions.) — jarlh, Feb 07 '19 at 15:59

Patrick · Answer 1 · 2019-02-07T15:47:57.590

1

You need to use DATEDIFF() to filter out the older dates:

SELECT a.ordernr, a.debnaam, a.debnr, a.orddat 
FROM orkrg as a 
WHERE DATEDIFF(year, a.orddat, GETDATE()) > 2
AND A.debnr NOT IN (SELECT b.debnr FROM orkrg as b WHERE
DATEDIFF(year, b.orddat, GETDATE()) <= 2)

edited Feb 07 '19 at 15:47

answered Feb 07 '19 at 15:44

Patrick

5,526
14
64
101

That will work to get anyone ordering >2 years but have to include a check where NOT in last 2 years. – Brad Feb 07 '19 at 15:45
@Brad good catch, edited for that. Untested but the concept should be correct now. Assumes b.debnr is unique identifier for the buyer. – Patrick Feb 07 '19 at 15:48
That should work, performance wise not sure if NOT Exist would be faster, but if he does not have GIANT databse should be fine. – Brad Feb 07 '19 at 15:49
Your query doesn't return any values in my db. Must be something wrong your datediff statement i'm assuming – Jeremy Feb 07 '19 at 15:59

score 1 · Accepted Answer · answered Feb 07 '19 at 15:48

1

select a.*
from orkrg as a
where a.orddat < dateadd(year,-2,getdate()) -- this is kinda not needed
and not exists (select 1  -- NOT EXISTS is a safer option than NOT IN, where a null result can cause issues
                from orkrg as b 
                where a.debnaam = b.debnaam and 
                b.orddat > dateadd(year,-2,getdate()))

I use not exists over not in as default, see here for why

answered Feb 07 '19 at 15:48

JohnHC

10,935
1
24
40

This works! Could you explain what the SELECT 1 means? Thanks – Jeremy Feb 07 '19 at 15:54
@Jeremy it's just returning a 1 for each row in the query. If it returns a 1, the row exists, so in a `not exists` it is excluded. You can also use `select *` or `select some_column`, but a 1 is all the data you need to determine if a row exists. – JohnHC Feb 07 '19 at 16:04

score 1 · Answer 3 · answered Feb 07 '19 at 16:08

1

select * 
from orders as o 
where o.debnr not in (select debnr 
                      from orders as u 
                      where orddat > CONVERT(date, dateadd(year,-2,getdate())))

answered Feb 07 '19 at 16:08

kopasovska

11
1
2

Finding records that are only before a given date (SQL)

3 Answers3