SQL Select help to ignore previous entries

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 2008 Forums

Transact-SQL (2008)

SQL Select help to ignore previous entries

Author

Topic

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2010-01-06 : 17:08:42

So this is what I want to achieve.

The data below is my contact information, the table is linked to the contact master data.

In the contact master data I have specified a field (number1) to have a value. Lets say it is 50 for 50 days.

This value is the maximum time allowed to have no contact with the contact.

Therefore I am trying to write a script to extract this information.

My data below is an exmaple, todays date is January 7, 2010.

Recoord id Note Date Contactid

1 Xxxxx 09/09/09 1
2 Yyyyy 01/01/10 1
3 Zzzzz 05/05/09 2

Now I want the SQL query to ignore the fact that record ID should be returned because record id 2 is within the 50 day no contact boundry.

However record 3 needs to be flagged

So far I have come up with the following, which does not work.

select getdate() as today, notes.date, contacts.firstname, contacts.lastname, contacts.contactid, DATEDIFF(day, notes.date, GETDATE()) AS NumberOfDays, contacts.number1, (DATEDIFF(day, notes.date, GETDATE())-contacts.number1) as diff1
from notes
right join contacts
on notes.contactid = contacts.contactid
where (DATEDIFF(day, notes.date, GETDATE())> contacts.number1)
or ((DATEDIFF(day, notes.date, GETDATE())-contacts.number1) < 0)

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2010-01-06 : 22:56:26

I think this fixed it,

select distinct contacts.firstname, contacts.lastname, contacts.email1, DATEDIFF(day, notes.date, GETDATE()) AS NumberOfDays, contacts.number1, (DATEDIFF(day, notes.date, GETDATE())-contacts.number1) as diff
from notes
left join contacts
on notes.contactid = contacts.contactid
where notes.contactid not in (SELECT notes.contactid from notes where DATEDIFF(day, notes.date, GETDATE())< contacts.number1)
and (DATEDIFF(day, notes.date, GETDATE())- contacts.number1 > contacts.number1)

any thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 01:03:57

whats the purpose of left join? dont all notes record have associated contacts?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources