Not In Function - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Not In Function

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2011-08-05 : 09:04:05

Dear All,

I need to reterview returned and not returned customer information using last year and this year data from cst table.

I written the below query

select cst.Name,cst.EmailAddress from cst
where DATEADD(DAY, DATEDIFF(DAY, '19000101',cst.DateCreated), '19000101') = DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE() - 365), '19000101')and cst.EmailAddress IN (select cst.EmailAddress from cst where cst.DateCreated >= getdate()-45 )

I got some resutlt for eg 67 emailid (returned customer)

But i used NOT IN to find not returned customer.I havent get any thing

for eg:-
cst.EmailAddress NOT IN (select cst.EmailAddress from cst where cst.DateCreated >= getdate()-45 )

But there are many record exist.What may be the reason and how to amend this query to get the desired result.Any help regarding this is highly appreciated.

Regards,
SG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-05 : 14:32:00

What is your intention here? Looks like the first criteria is limiting your rows to DateCreated is exactly 365 days ago.
Then you are further limiting to where those rows (that were created 1 year ago) to ones that have the same email address in different rows but were created within the last 45 days. Then it seems you want to add the NOT IN on top of that? That definately won't return anything because the same results can not be both IN and NOT IN the same list.

Finally, I suggest you use table aliases because when you have the same table specified multiple times in the same statement the references are ambiguous.

Be One with the Optimizer
TG

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-08-06 : 17:32:36

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources