Select "New Customer"

  • Hi ,

    Has anyone tried to do this...I'm trying to select only "new customers" revenue, by defining a new customer based on no transaction before the last 6 month.

    I've looked at using Top 1 and Max , etc. on the ship_date ,  but could use some help here

    thanks.

     

  • This gets the "new" customers.  Then you'd just join this back to the Transactions/Sales table and do a simple sum.

    SELECT CustomerID
    FROM Customer c
    WHERE NOT EXISTS (SELECT 1
    FROM Transactions t
    WHERE t.CustomerID = c.CustomerID
    AND t.TransactionDate <= DATEADD(month,-6,GETDATE())

     

    • This reply was modified 1 month, 1 week ago by  pietlinden.
  • Thank you.. I was only able to get to this today.

    I ran this and its seems to be what I needed.

     

  • Hi ,

    I initially thought the "where not exist"   was working but it is not....

    I have only one new customer this year , and the below query is returning many records. I should only be getting 3 records back.

    select 
    co_num,co_cust_num
    FROM [EES_App].[dbo].[coitem] as new1
    where
    NOT EXISTS( SELECT * FROM [EES_App].[dbo].[coitem] as new2
    where new1.co_cust_num = new2.co_cust_num
    and ship_date >= DATEADD(month,-12,GETDATE()))

    thanks for the help.

  • Hmm, yeah, I would think that would give you a lot of old customers, since there's no "recent sale date" requirement.  Maybe you need something more along these lines...?

    select 
    co_num,co_cust_num
    FROM [EES_App].[dbo].[coitem] as new1
    where
    ship_date >= DATEADD(DAY, -7, CAST(GETDATE() AS date)) AND
    NOT EXISTS( SELECT * FROM [EES_App].[dbo].[coitem] as new2
    where new1.co_cust_num = new2.co_cust_num
    and ship_date >= DATEADD(month,-12,GETDATE()) AND ship_date < DATEADD(DAY, -7, CAST(GETDATE() AS date)) )

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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