Tricky (for me) aggregate question 1

[DeveloperJr]

Hello everyone,

My question maybe simple for you, but it puzzles me.

How can I get a list of all the customers (along with their Order_Count) who placed orders with us, the question up to here is easy but here is the tricky part. I want to count the orders that only a certain period of time apart (relative to each other)

Example (assuming that the time period = 30 days)

Joe Ordered in 01-01-2007 (Seed Record) Will include this
Joe Ordered in 01-29-2007 ( less than 30 days from the seed record) Will include this
Joe Ordered in 02-15-2007 (less than 30 days from the 01-29-2007 record) Include it

Joe Ordered in 04-30-2007 (more than 30 days from the any record) exclude it.


Thanks for all of you in advance

 

[AlexCuse]

Have a look at this, I think it will do what you want:

[COLOR=green]--set up sample data
[/color][COLOR=blue]declare[/color] @t [COLOR=blue]table[/color] ([COLOR=blue]name[/color] nvarchar(3), dt [COLOR=#FF00FF]datetime[/color])

[COLOR=blue]insert[/color] @t
[COLOR=blue]select[/color] [COLOR=red]'Joe'[/color], [COLOR=red]'20070101'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'Joe'[/color],[COLOR=red]'20070129'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'Joe'[/color], [COLOR=red]'20070215'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'Joe'[/color], [COLOR=red]'20070430'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'Jim'[/color], [COLOR=red]'20070101'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'Jim'[/color], [COLOR=#FF00FF]getdate[/color]()

[COLOR=green]--query
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]distinct[/color] a.Name, a.dt
[COLOR=blue]from[/color] @t a
[COLOR=blue]inner[/color] [COLOR=blue]join[/color] @t b
[COLOR=blue]on[/color] a.Name = b.Name
and (a.dt > b.dt or a.dt = ([COLOR=blue]select[/color] [COLOR=#FF00FF]min[/color](dt) [COLOR=blue]from[/color] @t [COLOR=blue]where[/color] [COLOR=blue]name[/color] = a.name))
and [COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]day[/color], b.dt, a.dt) < 30

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom