/*Find areas beneath data points linearly connected.
Calculate the area of the
rectangle and
triangle made by adjacent data points.
.(5,8)
/|
/ | 1/2(b)(h) = 2
(3,6).-- +
| |
(b)(h) = 12
| |
-- A = 14
By C. Eric
Cashon
*/
USE tempdb
CREATE TABLE SampleData1(
TableID int
PRIMARY KEY IDENTITY,
GroupID INTEGER,
Xvalue real,
Yvalue real)
INSERT INTO SampleData1 VALUES(1 ,3, 6)
INSERT INTO SampleData1 VALUES(2 ,5, 8)
INSERT INTO SampleData1 VALUES(3 ,7, 11)
INSERT INTO SampleData1 VALUES(2 ,9, 13)
INSERT INTO SampleData1 VALUES(3 ,11, 15)
INSERT INTO SampleData1 VALUES(2, 13, 13)
INSERT INTO SampleData1 VALUES(3, 15, 12)
INSERT INTO SampleData1 VALUES(1, 17, 8)
SELECT * FROM SampleData1
SELECT SUM(T3.SectorArea) AS Area FROM
(SELECT
(CASE WHEN
T1.Yvalue < T2.Yvalue THEN
(.5 *
(T2.Xvalue - T1.Xvalue) * ABS(T2.Yvalue - T1.Yvalue))
+ (T1.Yvalue *
(T2.Xvalue - T1.Xvalue))
ELSE
(.5 * (T2.Xvalue - T1.Xvalue) *
ABS(T2.Yvalue - T1.Yvalue))
+ (T2.Yvalue *
(T2.Xvalue - T1.Xvalue))
END) AS
SectorArea
FROM SampleData1
T1 INNER JOIN SampleData1 T2
ON T1.TableID =
T2.TableID - 1) AS T3
--Try Simpson's rule. X values spaced evenly.
--A = h/3(yo + 4y1 + 2y2 + 4y3 + 2y4 + ... + 2yn-2 + 4yn-1 +
yn)
SELECT ((2.0/3.0)*
((SELECT
(SUM(Yvalue)) FROM SampleData1
WHERE GroupID =
1
GROUP BY
GroupID) +
(SELECT
(4*SUM(Yvalue)) FROM SampleData1
WHERE GroupID =
2
GROUP BY
GroupID) +
(SELECT
(2*SUM(Yvalue)) FROM SampleData1
WHERE GroupID =
3
GROUP BY
GroupID))) AS Area
--Clean up
DROP TABLE SampleData1