getting average values - SQL Server Forums

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)

getting average values

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2009-07-29 : 05:09:20

Hi All,
I need to calculate average values for a particular period

Formula is as follows:
if the average duration is selected as 4 days
average value for a particular day will be

(current days value + last 3 days' values) / 4

===========
if the average duration is selected as 7 days
average value for a particular day will be

(current days value + last 6 days' values) / 7

i can use a cursor to calculate each value, but is there a better way to do this?
[CODE]
declare @tmptbl table
(
id int
,dateval datetime
,val float
,avgval float
)

insert into @tmptbl(id,dateval,val)
select 1,'2009-01-27 00:00:00.000', 32 UNION ALL
select 2,'2009-01-28 00:00:00.000', 45 UNION ALL
select 3,'2009-01-29 00:00:00.000', 92 UNION ALL
select 4,'2009-01-30 00:00:00.000', 34 UNION ALL
select 5,'2009-03-15 00:00:00.000', 96 UNION ALL
select 6,'2009-03-16 00:00:00.000', 87 UNION ALL
select 7,'2009-03-17 00:00:00.000', 13 UNION ALL
select 8,'2009-05-01 00:00:00.000', 30

select * from @tmptbl
[/CODE]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-29 : 05:14:44

How do you determine current day's value?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-29 : 06:18:32

the "average duration selected" is a parameter pass in to your query ?

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

zion99
Posting Yak Master

141 Posts

Posted - 2009-07-29 : 06:23:34

"average duration selected" is a parameter passed to the query

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-29 : 06:30:34

like this ?


DECLARE @tmptbl TABLE 
(
	id int
	,dateval datetime
	,val float
	,avgval float
)

INSERT INTO @tmptbl(id,dateval,val)
SELECT 1,'2009-01-27 00:00:00.000',	32 UNION ALL
SELECT 2,'2009-01-28 00:00:00.000',	45 UNION ALL
SELECT 3,'2009-01-29 00:00:00.000',	92 UNION ALL
SELECT 4,'2009-01-30 00:00:00.000',	34 UNION ALL
SELECT 5,'2009-03-15 00:00:00.000',	96 UNION ALL
SELECT 6,'2009-03-16 00:00:00.000',	87 UNION ALL
SELECT 7,'2009-03-17 00:00:00.000',	13 UNION ALL
SELECT 8,'2009-05-01 00:00:00.000',	30

DECLARE	@today		datetime,
	@days		int

SELECT	@today	= '2009-01-30',
	@days	= 3

SELECT	AVG(val)
FROM	@tmptbl
WHERE	dateval		>= DATEADD(DAY, -@days + 1, @today)
AND	dateval		<= @today

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

zion99
Posting Yak Master

141 Posts

Posted - 2009-07-29 : 06:30:43

its like... every date will be taken as current date - formula will be applied to calculate the average value for that particular date.

zion99
Posting Yak Master

141 Posts

Posted - 2009-07-29 : 07:19:23

hi khtan,
even in this scenario, i will have to use cursor which i wanted to avoid in the first place.
I need to calculate avgval value for all the available records (8 for this table)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-29 : 07:34:22

[code]
DECLARE @tmptbl TABLE
(
id int
,dateval datetime
,val float
,avgval float
)

INSERT INTO @tmptbl(id,dateval,val)
SELECT 1,'2009-01-27 00:00:00.000', 32 UNION ALL
SELECT 2,'2009-01-28 00:00:00.000', 45 UNION ALL
SELECT 3,'2009-01-29 00:00:00.000', 92 UNION ALL
SELECT 4,'2009-01-30 00:00:00.000', 34 UNION ALL
SELECT 5,'2009-03-15 00:00:00.000', 96 UNION ALL
SELECT 6,'2009-03-16 00:00:00.000', 87 UNION ALL
SELECT 7,'2009-03-17 00:00:00.000', 13 UNION ALL
SELECT 8,'2009-05-01 00:00:00.000', 30

DECLARE @days int

SELECT @days = 4

SELECT id, dateval, val, a.avgval
FROM @tmptbl t
CROSS APPLY
(
SELECT avgval = avg(val)
FROM @tmptbl x
WHERE x.dateval >= DATEADD(DAY, -@days + 1, t.dateval)
AND x.dateval <= t.dateval
) a

/*
id dateval val avgval
----------- ----------- ------- --------------------
1 2009-01-27 32.0 32.0
2 2009-01-28 45.0 38.5
3 2009-01-29 92.0 56.333333333333336
4 2009-01-30 34.0 50.75
5 2009-03-15 96.0 96.0
6 2009-03-16 87.0 91.5
7 2009-03-17 13.0 65.333333333333329
8 2009-05-01 30.0 30.0

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources