Guidance Needed on Aggregating "Sliding" Range

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 2005 Forums

Transact-SQL (2005)

Guidance Needed on Aggregating "Sliding" Range

Author

Topic

clutchjohnson
Starting Member

1 Post

Posted - 2008-08-15 : 04:16:46

Hi

I have recently begun to learn SQL and feel I have been making great strides. Lurking on this forum has really helped me learn a lot, so thanks!

Anyway, my problem is as follows.

Imagine a table with two fields: date and price.

How would I go about finding the average price for the days 2 to 4 days before each date?

Date

Example:
tbl
Date Price
1/1 $5
1/2 $10
1/3 $15
1/4 $20
1/5 $25
1/6 $30

desired result for 1/6 = $15 (because 1/2,1/3, and 1/4 are the 2nd, 3rd, and 4th days before 1/6 and their values are $10,$15, and $20)

I would want this calculation done for each record.

I hope I have explained this clearly- I really need to learn more sql vocab.

Thanks in advance for any guidance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-15 : 04:35:59

[code]DECLARE @tbl TABLE
(
Date datetime,
Price decimal(10,2)
)
INSERT INTO @tbl
SELECT '20080101', 5 UNION ALL
SELECT '20080102', 10 UNION ALL
SELECT '20080103', 15 UNION ALL
SELECT '20080104', 20 UNION ALL
SELECT '20080105', 25 UNION ALL
SELECT '20080106', 30

SELECT t.[Date], t.Price, d.AvgPrice
FROM @tbl t
CROSS apply
(
SELECT AvgPrice = avg(Price)
FROM (
SELECT [Date], Price,
row_no = row_number() OVER (ORDER BY [Date] DESC)
FROM @tbl y
WHERE y.[Date] < t.[Date]
) x
WHERE x.row_no BETWEEN 2 AND 4
) d
ORDER BY [Date]

/*
Date Price AvgPrice
----------- ------------ ------------
2008-01-01 5.00 NULL
2008-01-02 10.00 NULL
2008-01-03 15.00 5.000000
2008-01-04 20.00 7.500000
2008-01-05 25.00 10.000000
2008-01-06 30.00 15.000000

(6 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2008-08-15 : 05:13:40

using khtan's ddl

select date
,price
,(select avg(price) from
@tbl where date between dateadd(day,-4,t.date) and dateadd(day,-2,t.date)
)
from @tbl t

--------------------
keeping it simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 05:30:29

Hi Jen!
Nice to see you on the forum.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 05:32:16

What if there are breaks in the sample data?

tbl
Date Price
1/1 $5
1/2 $10
1/3 $15

1/5 $25
1/6 $30

Should the average price for 1/6 still be calculated on prices for 1/2, 1/3 and 1/4?
Or, since there is a gap, should the average price for 1/6 still be calculated on prices for 1/1, 1/2 and 1/3?

E 12°55'05.25"
N 56°04'39.16"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2008-08-15 : 05:37:01

Thanks Peter,

I'm in relax mode right now so good timing to visit the site
Dreary weather...

quote:
Originally posted by Peso

Hi Jen!
Nice to see you on the forum.

E 12°55'05.25"
N 56°04'39.16"

--------------------
keeping it simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 05:53:25

Do you know what?
Isabelle took her first steps by herself yesterday.
She sat on the kithcen floor and suddenly she decided to rise and walk 11 steps cross the kitchen floor!

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources