Count number of orders

Question

Post reply

Count number of orders

atsql

Right there with Babe

Points: 740
More actions
June 25, 2010 at 12:28 pm

#220535

Could someone point me to a simplest way to count eligible number of orders for a customer:
it should be last n orders which do not have interval greater then defined number of days.
If number of days is 150 (5 months):
Sample orders for a customer:
order number date
5000 06/01/2010
4900 04/01/2010
3800 01/01/2010
2500 05/01/2009
2301 03/01/2009
count should be 3. Order number is not important.

Viewing 7 posts - 1 through 6 (of 6 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

select customer_id, count(*) cnt

from orders

where orderdate > (getdate() -150)

group by customer_id

* Noel

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 2

Good, but I suggest also stripping the time off the date. Otherwise you could get different results at different times of the same day, which could drive you crazy 🙂 :

select customer_id, count(*) cnt

from orders

where orderdate >= (dateadd(day, datediff(day, 0, getdate()), 0) -150)

group by customer_id

Scott Pletcher, SQL Server MVP 2008-2010

atsql Right there with Babe Points: 740 More actions · Answer 3

I am sorry I was not clear.

150 is interval between orders and not from current date.

noeld SSC Guru Points: 96590 More actions · Answer 4

declare @t table (order_number int, dte datetime)

insert into @t (order_number, dte )

select 5000 i, '06/01/2010' d union all

select 4900, '04/01/2010' union all

select 3800, '01/01/2010' union all

select 2500, '05/01/2009' union all

select 2301, '03/01/2009'

--select * from @t

;with cte

AS

( select dte, ROW_NUMBER() OVER ( ORDER BY dte ) rn

from @t

)

select COUNT(*)

from cte c1 inner join cte c2 on c1.rn + 1 = c2.rn

where DATEDIFF(d, c1.dte,c2.dte) <=150

* Noel

WayneS SSC Guru Points: 95460 More actions · Answer 5

How's this?

declare @test-2 table (OrderNumber smallint, OrderDate datetime)

-- See how you start off by actually creating a table

-- and then inserting the data into it? Doing this really

-- makes things a lot easier for all the people you want to

-- help you. So, HELP US HELP YOU by doing this for us! See

-- http://www.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

insert into @test-2

SELECT 5000, '06/01/2010' UNION ALL

SELECT 4900, '04/01/2010' UNION ALL

SELECT 3800, '01/01/2010' UNION ALL

SELECT 2500, '05/01/2009' UNION ALL

SELECT 2301, '03/01/2009'

select t1.*,

Qty = (select Qty = count(*) from @test-2 where OrderDate < dateadd(day, -150, t1.OrderDate))

from @test-2 t1

If this doesn't work for you, please read the first link in my signature. Then post table DDL and sample data has that article demonstrates. Then, based on the sample data, post what you want the expected results to be.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

atsql Right there with Babe Points: 740 More actions · Answer 6

Thank you very much,

These are a SQL statements I would like to use.

However, only version with CTE gives correct answer. I will check performance later since real query is more complex.

Alex