Finding maximum sum/avg for a rolling range? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Finding maximum sum/avg for a rolling range?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-02-26 : 15:20:43

I've got one for the set-based masters.


create table sales (sale_date smalldatetime,amount money)
insert into sales (sale_date,amount)
select '2005-01-01 11:50',68.5
union all
select '2005-01-03 15:02',45.91
union all
select '2005-01-20 16:41',37.5
union all 
select '2005-01-26 04:35',48.5
union all
select '2005-02-02 18:22',56.25
union all
select '2005-02-10 03:55',28.9
union all
select '2005-02-15 19:21',38.58
union all
select '2005-02-19 03:30',22.5

...given data like that, I'm looking to compute 1) the start of the 30 day period with the highest number of sales, 2) the start of the 30 day period with the highest total dollars in sales, and 3) the start of the 30 day period with the highest average dollar-per-sale.

I can do it with a cursor and a join table with one row per day, but that's pretty ugly. Is there a more elegant approach?

FWIW, it would be preferable to compute the 30 day windows as calendar days, but it seems to be that any solution that looks ahead 30 days from the exact datetime would be easily adapted via a UDF or something to only get the date part.

Cheers
-b

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-26 : 19:13:50

There may be a better way but here is one set-based possibility:


--load 30 day counts into a temp table
select	sale_date
	,numberOfSales =  (select count(*)    from #sales where sale_date >= a.sale_date and sale_date < dateadd(day,30,a.sale_date))
	,SalesInDollars = (select sum(amount) from #sales where sale_date >= a.sale_date and sale_date < dateadd(day,30,a.sale_date))
	,avgSales =	  (select avg(amount) from #sales where sale_date >= a.sale_date and sale_date < dateadd(day,30,a.sale_date))
into	#StartPeriods
from	#sales a

--Get date for each Max from temp table
select	'NumberOfSales' Metric, b.sale_date,  b.NumberOfSales
from	(select	max(numberOfSales) numberOfSales from #startPeriods) a
join	#StartPeriods b
		on b.NumberOfSales = a.NumberOfSales
Union all
select	'SalesInDollars', b.sale_date,  b.SalesInDollars
from	(select	max(SalesInDollars) SalesInDollars from #startPeriods) a
join	#StartPeriods b
		on b.SalesInDollars = a.SalesInDollars
union all
select	'avgSales', b.sale_date,  b.avgSales
from	(select	max(avgSales) avgSales from #startPeriods) a
join	#StartPeriods b
		on b.avgSales = a.avgSales

Output:

Metric         sale_date                                              NumberOfSales         
-------------- ------------------------------------------------------ --------------------- 
NumberOfSales  2005-01-20 16:41:00                                    6.0000
SalesInDollars 2005-01-20 16:41:00                                    232.2300
avgSales       2005-01-01 11:50:00                                    50.1025

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources