How to prove a negative with SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to prove a negative with SQL

Author

Topic

itvelocity.com
Starting Member

3 Posts

Posted - 2007-06-28 : 17:10:07

DB Structure

PERSON
Person_ID (UNIQUE ID)
First (TEXT)
Last (TEXT)

VISIT
Person_ID (UNIQUE ID)
Visit_ID (UNIQUE ID)
DateOfVisit (DATETIME)

Person to Visits is a one-to-many relationship

Looking for a way to identify all people who visited more than one year ago but not in the last year.

Ex.
Today is July 1, 2007

Andy visited June 1, 2006
Andy visited June 1, 2007

Bob visited May 1, 2006

Charles visited January 1, 2004

Donald visited March 1, 2007

My results should yield

Bob (reason: > 365 days)
Charles (reason: > 365 days)

Not Andy (reason: Did visit > 365 days but also visited < 365 days)
Not Donald (reason: < 365 days)

Our idea so far is:

create tempdb1
insert all greater than 365 days (Andy,Bob,Charles)

create tempdb2
insert all < 365 days (Andy, Donald)

traverse tempdb1 and compare to tempdb2
CASE Person_ID from tempdb1 exists in tempdb2 THEN DELETE from tempdb1 (Andy would be deleted from tempdb1)

Is there a quicker way to execute this query by using a Left Join or some other method?

hey001us
Posting Yak Master

185 Posts

Posted - 2007-06-28 : 17:32:57

SELECT p.Person_ID
FROM Person p
INNER JOIN Visit v ON p.person_id = v.person_id
WHERE DATEDIFF(dateofvisit, GETDATE()) > 365
AND p.Person_ID NOT IN
(SELECT person_id FROM Visit WHERE DATEDIFF(dateofvisit, GETDATE()) < 365)

hey

itvelocity.com
Starting Member

3 Posts

Posted - 2007-06-28 : 17:37:43

Thank you. I knew I was going down the wrong path but I couldn't seem to get it right..

sshelper
Posting Yak Master

216 Posts

Posted - 2007-06-28 : 17:41:18

Try this one as well:

SELECT P.*
FROM Person P INNER JOIN (SELECT Person_ID, MAX(DateOfVisit) DateOfVisit FROM Visit GROUP BY Person_ID) V ON P.Person_ID = V.Person_ID AND DATEDIFF(V.DateOfVisit, GETDATE()) > 365

SQL Server Helper
http://www.sql-server-helper.com

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-28 : 17:55:24

[code]
select
p.*
from
PERSON p
where
p.PERSON_ID in
(
select
v.PERSON_ID
from
VISIT v
group by
v.PERSON_ID
having
max(v.DateOfVisit) <
-- One year before today at midnight
dateadd(dd,datediff(dd,0,dateadd(yy,-1,getdate())),0)
)
[/code]

CODO ERGO SUM

itvelocity.com
Starting Member

3 Posts

Posted - 2007-06-28 : 21:51:56

Thanks everyone. I'll try all of these and report back with the hopes that this helps someone else someday. I appreciate you taking the time to respond.

Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources