Script not working...any ideas why? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Script not working...any ideas why?

Author

Topic

deanfp
Starting Member

26 Posts

Posted - 2011-06-23 : 16:08:15

Hi

I have a script that queries our customer table to show all customers who have not purchased from us in the last six months. This code works fine and reads as

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())
)

However, when I try and run the opposite the SQL script (to show those who have purchesed) it executes but nothing is returned. I have swapped the Customer ID not in to in so the code reads

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

Any ideas where I am going wrong?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-23 : 16:11:16

Change the order of the dates in the BETWEEN clause. You have to give the earlier date first.


where o.OrderDate between  dateadd(month,-6,getdate()) and GETDATE()

deanfp
Starting Member

26 Posts

Posted - 2011-06-23 : 16:16:56

Thanks for the reply. Sorry but change the order to be what?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-23 : 16:22:53

Even though in the English language, "a date between Jan 1, 2011 and June 30, 2011" has the same meaning as "between June 30, 2011 and Jan 1, 2011", in SQL that is not the case.

When you have an expression such as


where o.OrderDate between GETDATE() and dateadd(month,-6,getdate())

that is equivalent to


where o.OrderDate >= GETDATE() and o.OrderDate <= dateadd(month,-6,getdate())

That would return no dates at all. So instead you should use


where o.OrderDate between dateadd(month,-6,getdate()) and GETDATE()

which would be equivalent to

where o.OrderDate >= dateadd(month,-6,getdate()) and  o.OrderDate <= GETDATE()

That would pick up the six-month period.

So change wherever you have "where o.OrderDate between GETDATE() and dateadd(month,-6,getdate())" to "where o.OrderDate betweendateadd(month,-6,getdate()) and GETDATE() "

deanfp
Starting Member

26 Posts

Posted - 2011-06-23 : 16:48:00

I understand! Thanks for the explanation!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources