calculate 4 weeks average ..need help

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

SQL Server Development (2000)

calculate 4 weeks average ..need help

Author

Topic

escape
Starting Member

3 Posts

Posted - 2006-09-15 : 06:53:15

i have 3 tables, each with a date(it has daily dates) column(column name is same in all tables)
Each table has columns say "value1","value2", "value3"

i want data from all these tables together.such that my first column will have data weeks and other 3 columns count1,count2,count3 will have average of next 4 weeks count..placed infront of week.

weeks count(value1) count(value2) count(value3 )
1/1/2005 101 88 221
1/8/2005 100 81 151
1/15/2005 87 96 301

Average calculations Here :
week data 1 2 3 4
Count1: 101 = ( 99 + 105 + 110 + 87 )/4
100 = (105 + 110 + 87 + 98 )/4

Plz lemme know if u have any suggestions..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-15 : 07:08:27

Something like this, maybe?

SELECT		d.y,
			d.w,
			SUM(d.v1) / 4 v1,
			SUM(d.v2) / 4 v2,
			SUM(d.v3) / 4 v3
FROM		(
				SELECT		datepart(year, date) y,
							datepart(week, date) w,
							SUM(value1) v1,
							SUM(value2) v2,
							SUM(value3) v3
				FROM		MyTable1
				GROUP BY	datepart(year, date),
							datepart(week, date)
				UNION ALL
				SELECT		datepart(year, date),
							datepart(week, date),
							SUM(value1),
							SUM(value2),
							SUM(value3)
				FROM		MyTable2
				GROUP BY	datepart(year, date),
							datepart(week, date)
				UNION ALL
				SELECT		datepart(year, date),
							datepart(week, date),
							SUM(value1),
							SUM(value2),
							SUM(value3)
				FROM		MyTable3
				GROUP BY	datepart(year, date),
							datepart(week, date)
			) d
WHERE		d.y = 2006
			AND d.w BETWEEN 36 AND 39
GROUP BY	d.y,
			d.w

Peter Larsson
Helsingborg, Sweden

escape
Starting Member

3 Posts

Posted - 2006-09-26 : 06:08:35

thnak for the suggestions.actually i need average for each weeks days.
Suppose for
first week it ll be value of week1+week2+week3+week4/4
Second week it ll be value of week2+week3+week4+week5/4
Third week it ll be value of week3+week4+week5+week6/4

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-26 : 06:22:38

Something like this

DECLARE @ThisDate DATETIME

SELECT	@ThisDate = '20060926'


SELECT		d.y,
		d.w,
		SUM(d.v1) / 4 v1,
		SUM(d.v2) / 4 v2,
		SUM(d.v3) / 4 v3
FROM		(
			SELECT		datepart(year, date) y,
					datepart(week, date) w,
					SUM(value1) v1,
					SUM(value2) v2,
					SUM(value3) v3
			FROM		MyTable1
			GROUP BY	datepart(year, date),
					datepart(week, date)
			UNION ALL
			SELECT		datepart(year, date),
					datepart(week, date),
					SUM(value1),
					SUM(value2),
					SUM(value3)
			FROM		MyTable2
			GROUP BY	datepart(year, date),
					datepart(week, date)
			UNION ALL
			SELECT		datepart(year, date),
					datepart(week, date),
					SUM(value1),
					SUM(value2),
					SUM(value3)
			FROM		MyTable3
			GROUP BY	datepart(year, date),
					datepart(week, date)
		) d
WHERE		100 * d.y + d.w BETWEEN 100 * YEAR(@Thisdate) + DATEPART(week, @ThisDate) AND 100 * YEAR(DATEADD(week, 3, @Thisdate)) + DATEPART(week, DATEADD(week, 3, @Thisdate))
GROUP BY	d.y,
		d.w

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources