SQL totalize/sum volumetric data over time

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)

SQL totalize/sum volumetric data over time

Author

Topic

jay_t
Starting Member

1 Post

Posted - 2009-10-22 : 13:47:49

I am trying to calculate the total volume of water irrigated over a section of land. What I have is the change of flow at an instant of time recorded into a SQL database. -this is measured in cubic meters per hour.


Date_Time	        Flow_Value
2009/10/22 04:00:00.0	0
2009/10/22 04:00:16.2	23
2009/10/22 04:00:20.6	34
2009/10/22 04:00:39.7	95
2009/10/22 04:00:41.7	97
2009/10/22 04:01:15.1	110
2009/10/22 04:03:17.0	95
2009/10/22 04:06:53.8	82
2009/10/22 04:26:50.7	77
2009/10/22 04:36:50.8	76
2009/10/22 04:46:51.7	72
2009/10/22 04:56:52.2	74
2009/10/22 05:16:52.7	72
2009/10/22 05:26:53.2	70
2009/10/22 05:36:22.1	84
2009/10/22 05:46:16.3	81
2009/10/22 05:56:16.2	75
2009/10/22 06:16:17.3	73
2009/10/22 06:26:16.9	75
2009/10/22 06:36:17.7	71
2009/10/22 06:57:38.7	57
2009/10/22 06:57:48.9	44
2009/10/22 06:57:53.4	28
2009/10/22 06:57:55.3	12
2009/10/22 07:07:55.1	0

Its simply not the case jut to sum up the values and assume that is the total volume of water irrigated.
what needs to be done is work out the time difference per time stamp and calculate the volume for that time duration, and then have it over the hour(s) the user has selected.
so for the above data, then the time difference would be (for the first hour)


time_diff	volume
00:00:04.4	101.20
00:00:19.1	649.40
00:00:02.0	190.00
00:00:33.5	3249.50
00:02:01.9	13409.00
00:03:36.8	20596.00
00:19:56.9	98145.80
00:10:00.1	46207.70
00:10:00.9	45668.40
00:10:00.5	43236.00
00:20:00.5	88837.00
00:10:00.5	13521.60

There for the total volume irrigated of that hour (from 4am to 5am) is : 373811.6 cubic meter's of water divided by 3600 = 103.8365556
The question is: How do I do this with SQL - I am totally lost, and do not know where to begin, any help would be appreciated

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-22 : 14:07:34

I'm not sure how you're calculating the Volume, but here's the time differences. The formual for the volume should be easy enough to calculate.

DECLARE @TABLE TABLE(Date_Time datetime,Flow_Value float)

INSERT INTO @Table
SELECT '2009/10/22 04:00:00.0', 0 UNION ALL
SELECT '2009/10/22 04:00:16.2', 23 UNION ALL
SELECT '2009/10/22 04:00:20.6', 34 UNION ALL
SELECT '2009/10/22 04:00:39.7', 95 UNION ALL
SELECT '2009/10/22 04:00:41.7', 97 UNION ALL
SELECT '2009/10/22 04:01:15.1', 110 UNION ALL
SELECT '2009/10/22 04:03:17.0', 95 UNION ALL
SELECT '2009/10/22 04:06:53.8', 82 UNION ALL
SELECT '2009/10/22 04:26:50.7', 77 UNION ALL
SELECT '2009/10/22 04:36:50.8', 76 UNION ALL
SELECT '2009/10/22 04:46:51.7', 72 UNION ALL
SELECT '2009/10/22 04:56:52.2', 74 UNION ALL
SELECT '2009/10/22 05:16:52.7', 72 UNION ALL
SELECT '2009/10/22 05:26:53.2', 70 UNION ALL
SELECT '2009/10/22 05:36:22.1', 84 UNION ALL
SELECT '2009/10/22 05:46:16.3', 81 UNION ALL
SELECT '2009/10/22 05:56:16.2', 75 UNION ALL
SELECT '2009/10/22 06:16:17.3', 73 UNION ALL
SELECT '2009/10/22 06:26:16.9', 75 UNION ALL
SELECT '2009/10/22 06:36:17.7', 71 UNION ALL
SELECT '2009/10/22 06:57:38.7', 57 UNION ALL
SELECT '2009/10/22 06:57:48.9', 44 UNION ALL
SELECT '2009/10/22 06:57:53.4', 28 UNION ALL
SELECT '2009/10/22 06:57:55.3', 12 UNION ALL
SELECT '2009/10/22 07:07:55.1', 0

;
WITH cte_Table
AS
(
SELECT RowNumber = Row_Number() OVER(order by date_time)
,Date_Time
,Flow_Value
FROM @table
)

SELECT
[Time_Diff] = DATEDIFF(ms,t1.date_time,t2.date_time)
FROM
cte_table t1
LEFT JOIN
cte_table t2
ON
t1.RowNumber = t2.RowNumber - 1

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources