How to calculate the value base on the formula

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)

How to calculate the value base on the formula

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2009-07-27 : 06:26:58

Hi,

I have a table tblA (Rec_No,ColA,ColB,ColLength). How to apply the formula base on the ColLength set to it?

Rec_No 1 2 3 4
Value A1,B1 A2,B2 A3,B3 A4,B4
Length 1 2 3 3

Formula
Rec_No 1 : (A1+B1)/2
Rec_No 2 : (A1+B1+A2+B2)/4
Rec_No 3 : (A1+B1+A2+B2+A3+B3)/6
Rec_No 4 : (A2+B2+A3+B3+A4+B4)/6

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 06:33:39

How do you want the result to looks like ?

Please post sample data and expected result.

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

juicyapple
Posting Yak Master

176 Posts

Posted - 2009-07-27 : 20:18:53

For example, I have below data. If ColLength=3, I will sum up the ColA and ColB for last 3 Rec_No started from itself.

Rec_No ColA ColB ColLength
------ ----- ----- ---------
1 0.2 0.4 1
2 0.1 0.5 2
3 0.5 0.1 3
4 0.2 0.2 3
5 0.4 0.4 1

Rec_No Result
------ ------
1 (0.2+0.4)/2 = 0.3
2 (0.2+0.4+0.1+0.5)/4=0.3
3 (0.2+0.4+0.1+0.5+0.5+0.1)/6=0.3
4 (0.1+0.5+0.5+0.1+0.2+0.2)/6=0.267
5 (0.4+0.4)/2=0.4

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 21:06:07

for SQL Server 2005/2008


DECLARE @sample TABLE
(
	Rec_No 		int,
	ColA 		decimal(5,1),
	ColB 		decimal(5,1),
	ColLength	int
)
INSERT INTO @sample
SELECT	1, 0.2, 0.4, 1 UNION ALL
SELECT	2, 0.1, 0.5, 2 UNION ALL
SELECT	3, 0.5, 0.1, 3 UNION ALL
SELECT	4, 0.2, 0.2, 3 UNION ALL
SELECT	5, 0.4, 0.4, 1 

SELECT	*
FROM	@sample s
	CROSS APPLY
	(
 		SELECT	result = SUM(x.ColA + x.ColB) / (s.ColLength * 2)
		FROM	@sample x
		WHERE	x.Rec_No	>= s.Rec_No - s.ColLength + 1
		AND	x.Rec_No	<=  s.Rec_No
	) r

/*
Rec_No      ColA    ColB    ColLength   result                                   
----------- ------- ------- ----------- ---------------------------------------- 
1           .2      .4      1           .300000
2           .1      .5      2           .300000
3           .5      .1      3           .300000
4           .2      .2      3           .266666
5           .4      .4      1           .400000

(5 row(s) affected)
*/

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

juicyapple
Posting Yak Master

176 Posts

Posted - 2009-07-27 : 21:25:12

I get this...

Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'CROSS'.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near 'r'.

I am using SQL2K, will this cause problem?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 21:29:26

yes. CROSS APPLY only available for SQL 2005/2008. You have posted in a SQL 2005 forum, naturally i assume you are using SQL 2005.

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 21:30:47

for SQL 2000, try


SELECT	*, 
	result = (	
			SELECT	result = SUM(x.ColA + x.ColB) / (s.ColLength * 2)
			FROM	@sample x
			WHERE	x.Rec_No	>= s.Rec_No - s.ColLength + 1
			AND	x.Rec_No	<=  s.Rec_No
		)
FROM	@sample s

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

juicyapple
Posting Yak Master

176 Posts

Posted - 2009-07-28 : 01:17:25

Thanks for the reply. Please allow me to have one more question. If I change the Rec_No to Rec_Date_Tm, how to get n records for calculation?

quote:

Originally posted by khtan

for SQL 2000, try


SELECT	*, 
	result = (	
			SELECT	result = SUM(x.ColA + x.ColB) / (s.ColLength * 2)
			FROM	@sample x
			WHERE	x.Rec_No	>= s.Rec_No - s.ColLength + 1
			AND	x.Rec_No	<=  s.Rec_No
		)
FROM	@sample s

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-28 : 01:25:08

maybe go via a temp table. Create a temp table with identity to generate the running no and perform the calculation from the temp table

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

juicyapple
Posting Yak Master

176 Posts

Posted - 2009-07-28 : 01:50:51

OK, I get the idea now. Thanks.

quote:
Originally posted by khtan

maybe go via a temp table. Create a temp table with identity to generate the running no and perform the calculation from the temp table

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources