Select "New Customer"

Question

Select "New Customer"

gjoelson 29755

SSC Eights!

Points: 848
More actions
January 19, 2021 at 9:37 pm

#3835223

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.

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

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

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 3 years, 10 months ago by pietlinden.

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 2

Thank you.. I was only able to get to this today.

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

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 3

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.

ScottPletcher SSC Guru Points: 101097 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".