Customer Log into Storefront but No Sales Order

  • 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

  • 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]

  • 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

  • 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]

  • 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