May 26, 2010 at 2:05 pm
I have written the following SQL statement to generate a report of customers who log into our storefront but do not place any order during specific period of time. I am sharing my code to get your feedback. I would really appreciate if your have recommendation for me.
SELECT c.CustomerID
,c.FirstName
,c.LastName
,c.CompanyName
,c.BillingAddress1
,c.BillingAddress2
,c.City
,c.State
,c.PostalCode
,c.Country
,c.EmailAddress
FROM Customers AS c
WHERE NOT EXISTS
(SELECT *
FROM Orders AS o
WHERE o.CustomerID = c.CustomerID AND
o.OrderDate BETWEEN '2010-04-01' AND '2010-04-30'
) AND
c.AccessKey = 'C' AND
c.LastLogin BETWEEN '2010-04-01' AND '2010-04-30'
ORDER BY c.CustomerID
GO
Thank you,
Fawad Rashidi
www.fawadafr.com
May 26, 2010 at 2:21 pm
Is there a question there? are you having problems with your code? Is is just not working the way you want it to? Are you looking to do it a better/faster/more efficient way? Have a look at the two links in my signature for ways that you can better help us to help you.
Cheers,
-Luke.
May 26, 2010 at 2:32 pm
Thanks for your reply Luke. No, I did not have any specific question. But, wanted to share my work with you to find out if there are ways I could improve it.
Quick question: Is there a way I could trigger the result by either using either LastLogin date or OrderDate? I am currently using both.
Fawad Rashidi
www.fawadafr.com
May 27, 2010 at 6:58 am
I don't think you could use an OR operator on your date columns without changing your Not Exists Statement into a left outer join. It may be slightly more expensive as per Gail's Article here. But it would allow the use of the OR operator...
Probably something like this might work, but again I can't tell you how it would perform as I'm not sitting in your chair... Depending on Indexes, data volume etc, this may be quite a dog.
SELECT c.CustomerID
,c.FirstName
,c.LastName
,c.CompanyName
,c.BillingAddress1
,c.BillingAddress2
,c.City
,c.State
,c.PostalCode
,c.Country
,c.EmailAddress
FROM Customers AS c
LEFT OUTER JOIN ORDERs o
ON o.CustomerID = c.CustomerID
WHERE
c.AccessKey = 'C' AND
(o.OrderDate BETWEEN '2010-04-01' AND '2010-04-30'
OR
c.LastLogin BETWEEN '2010-04-01' AND '2010-04-30')
ORDER BY c.CustomerID
Also, I'd suggest you take a look at your data and what's going on with the between operators you are using...
'2010-04-30' equals '2010-04-30 00:00:00.000' i.e. Midnight on 4/30. This code excludes any data logged between 2010-04-30 00:00:00.003 and 2010-04-30 23:59:59.997 i.e. any logins or orders placed on the 30th.
You'd be better off using c.LastLogin >= '2010-04-01' AND c.LastLogin < '2010-05-01' which will garuntee that you get all logins on that date. The same applies to the orderdate column as well.
-Luke.
May 27, 2010 at 12:48 pm
Great. Thank you very much for the help!
Fawad Rashidi
www.fawadafr.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply