Order query - 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

Order query

Author

Topic

deanfp
Starting Member

26 Posts

Posted - 2012-03-28 : 04:14:22

Hi I am trying to find a list of our customers who have ordered from us in the past but not in the past six months. I thought the query below would work but it seems to be showing the same amount of customer ID's as what is in the customer table iteself. Any idea where I am going wrong?

select c.CustomerID, c.Email
from Customer c
where CustomerID not in
(
select distinct CustomerID
from orders o
where o.OrderDate between GETDATE() and dateadd(month,-6,getdate())
)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-28 : 04:28:18

"between GETDATE() and dateadd(month,-6,getdate())"

should be between <smaller value> and <larger value>. You have it the other way round

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-28 : 12:25:55

[code]
select c.CustomerID, c.Email
from Customer c
where not exists
(select 1 from orders where OrderDate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-6,0)
AND OrderDate <DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)
AND CustomerID = c.CustomerID
)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources