Self searching a table 1

[earthandfire]

SalesTable
includes the following fields:
[tt] SalesDate datetime
Customer int
OrderNumber int
NewCustomer bit
[/tt]
The requirement is to set the NewCustomer field to 1 where this is the first sale to a customer for 4 years (or this is a brand new customer)

The table contains all sales data since the late 1990s

Any help would be gratefully appreciated - I've been going round in circles with this since Friday morning.

Unfortunately this is required as a query, not a stored procedure, which is one reason why I am struggling.

 

[AlexCuse]

earthandfire -

have a look at this, I think it will do what you're looking for. It's kinda ugly though, and I'm sure there is some room for improvement.

I assumed that you would want to update both the first row (in 199x) AND the one 4+ years later, is this correct?

[COLOR=blue]declare[/color] @n [COLOR=blue]table[/color] (
  SalesDate [COLOR=#FF00FF]datetime[/color]
  , Customer [COLOR=blue]int[/color]
  , OrderNumber [COLOR=blue]int[/color]
  , NewCustomer [COLOR=blue]bit[/color]
)

[COLOR=blue]insert[/color] @n
[COLOR=blue]select[/color] [COLOR=#FF00FF]getdate[/color](), 15, 273, 0
union all [COLOR=blue]select[/color] [COLOR=#FF00FF]getdate[/color]() - 15, 15, 202, 0
union all [COLOR=blue]select[/color] [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]year[/color], -5, [COLOR=#FF00FF]getdate[/color]()), 92, 5, 0
union all [COLOR=blue]select[/color] [COLOR=#FF00FF]getdate[/color](), 92, 5, 0
union all [COLOR=blue]select[/color] [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]year[/color], -10, [COLOR=#FF00FF]getdate[/color]()), 12, 7, 0
union all [COLOR=blue]select[/color] [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]year[/color], -7, [COLOR=#FF00FF]getdate[/color]()), 12, 9, 0
union all [COLOR=blue]select[/color] [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]year[/color], -1, [COLOR=#FF00FF]getdate[/color]()), 12, 13, 0
union all [COLOR=blue]select[/color] [COLOR=#FF00FF]getdate[/color](), 12, 15, 0

[COLOR=blue]update[/color] a
[COLOR=blue]set[/color] a.NewCustomer = 1
[COLOR=blue]from[/color] @n a
[COLOR=blue]inner[/color] [COLOR=blue]join[/color] (
	[COLOR=green]--we want to update rows with min(SalesDate)
[/color]	[COLOR=blue]select[/color] customer, [COLOR=#FF00FF]min[/color](SalesDate) msd 
	[COLOR=blue]from[/color] @n
	[COLOR=blue]group[/color] [COLOR=blue]by[/color] customer
	[COLOR=green]--also rows with no Sales for 4 years
[/color]	union all
	[COLOR=blue]select[/color] x.Customer, x.SalesDate
	[COLOR=blue]from[/color] @n x
	[COLOR=blue]inner[/color] [COLOR=blue]join[/color] @n z
	[COLOR=blue]on[/color] x.Customer = z.Customer and x.SalesDate > z.SalesDate
		and [COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]year[/color], z.SalesDate, x.SalesDate) >=4
	[COLOR=blue]where[/color] z.SalesDate >= ([COLOR=blue]select[/color] [COLOR=#FF00FF]max[/color](SalesDate) [COLOR=blue]from[/color] @n
				[COLOR=blue]where[/color] Customer = x.Customer
				and SalesDate < x.SalesDate)
) b
[COLOR=blue]on[/color] a.customer = b.customer
and a.SalesDate = b.msd

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @n

Post back if you have any questions.

hope it helps,

Alex

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[earthandfire]

Alex, thanks.

I just couldn't see my way through to the nested join and the union.

A quick check of a couple of records that I expected to see set to true are now true, but I've not had chance to do a detailed check yet.

A different system is currently in place and has been used for historic data, so the importance of this is for sales during 2007 and forward.

Thanks again.