Customer Log into Storefront but No Sales Order

Question

Customer Log into Storefront but No Sales Order

fawadafr

SSC Eights!

Points: 865
More actions
May 26, 2010 at 2:05 pm

#222053

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

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.

To help us help you read this[/url]For better help with performance problems please read this[/url]

fawadafr SSC Eights! Points: 865 More actions · Answer 2

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

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.

To help us help you read this[/url]For better help with performance problems please read this[/url]

fawadafr SSC Eights! Points: 865 More actions · Answer 4

Great. Thank you very much for the help!

Fawad Rashidi
www.fawadafr.com