SQL function to sum values/totalize volumes over specific time periods

Question

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

Answer 1

You can start with this:

declare @table table (_time datetime, flow int)

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

select t1._time time_start, t2._time time_finish, t1.flow
from @table t1, @table t2
where t2._time = (select min(_time) from @table where _time > t1._time)

This will return you the interval in one row and the value:

time_start  time_finish flow
04:00:00.000    04:00:16.200 0
04:00:16.200    04:00:20.600 23
04:00:20.600    04:00:39.700 34
04:00:39.700    04:00:41.700 95
04:00:41.700    04:01:15.100 97
04:01:15.100    04:03:17.000 110
04:03:17.000    04:06:53.800 95
04:06:53.800    04:26:50.700 82
04:26:50.700    04:36:50.800 77
04:36:50.800    04:46:51.700 76
04:46:51.700    04:56:52.200 72
04:56:52.200    05:16:52.700 74
05:16:52.700    05:26:53.200 72
05:26:53.200    05:36:22.100 70
05:36:22.100    05:46:16.300 84
05:46:16.300    05:56:16.200 81
05:56:16.200    06:16:17.300 75
06:16:17.300    06:26:16.900 73
06:26:16.900    06:36:17.700 75
06:36:17.700    06:57:38.700 71
06:57:38.700    06:57:48.900 57
06:57:48.900    06:57:53.400 44
06:57:53.400    06:57:55.300 28
06:57:55.300    07:07:55.100 12

After this you can use it like subquery and make some multiplications and sum.

Of course it's a simplified example.

Answer 2

This answer assumes you're using SQL Server. Your sample "for the first hour" actually includes more than the first hour; it should stop after the 00:10:00.1 row I'd think.

You can find the previous row for each row by joining the table on itself, then joining another time, and then saying nothing can be between the first two rows:

select 
 StartDate = prev.date
, EndDate = cur.date
, Milliseconds = datediff(ms,prev.date,cur.date)
, Volume = datediff(ms,prev.date,cur.date) / 1000.0 * prev.flow
from @flow cur
inner join @flow prev
 on prev.date < cur.date
left join @flow inbetween
 on prev.date < inbetween.date
 and inbetween.date < cur.date
where inbetween.date is null

This gives you the sum per period. Calculating the hour total requires you to split entries that cross an hour boundary. You can do that by adding an entry for the end of each hour, like:

select date, flow
from @flow
union
-- Add end of hour
select DATEADD(Hour, DATEDIFF(Hour, 0, date)+1, 0), flow
from @flow 
where date in (select max(date) from @flow group by datepart(hh,date))

You can combine both queries using the WITH statement to calculate the sum per hour:

;with FlowWithHourBounds as (
    select date, flow
    from @flow
    union
    -- Add end of hour
    select DATEADD(Hour, DATEDIFF(Hour, 0, date)+1, 0), flow
    from @flow 
    where date in (
        select max(date) from @flow group by datepart(hh,date))
)
,  FlowPerPeriod as (
    select 
     StartDate = prev.date
    , EndDate = cur.date
    , Milliseconds = datediff(ms,prev.date,cur.date)
    , Volume = datediff(ms,prev.date,cur.date) / 1000.0 * prev.flow
    from FlowWithHourBounds cur
    inner join FlowWithHourBounds prev
     on prev.date < cur.date
    left join FlowWithHourBounds inbetween
     on prev.date < inbetween.date
     and inbetween.date < cur.date
    where inbetween.date is null
)
select datepart(hh,StartDate), sum(Volume)
from FlowPerPeriod
group by datepart(hh,StartDate)

The result is:

hour volume
4    285340,5
5    273288,5
6    255408,3
7    5701,2

Here's the sample dataset I created from your post:

declare @flow table ([date] datetime, flow float)
insert into @flow values ('2009/10/22 04:00:00.0', 0  )
insert into @flow values ('2009/10/22 04:00:16.2', 23 )
insert into @flow values ('2009/10/22 04:00:20.6', 34 )
insert into @flow values ('2009/10/22 04:00:39.7', 95 )
insert into @flow values ('2009/10/22 04:00:41.7', 97 )
insert into @flow values ('2009/10/22 04:01:15.1', 110)
insert into @flow values ('2009/10/22 04:03:17.0', 95 )
insert into @flow values ('2009/10/22 04:06:53.8', 82 )
insert into @flow values ('2009/10/22 04:26:50.7', 77 )
insert into @flow values ('2009/10/22 04:36:50.8', 76 )
insert into @flow values ('2009/10/22 04:46:51.7', 72 )
insert into @flow values ('2009/10/22 04:56:52.2', 74 )
insert into @flow values ('2009/10/22 05:16:52.7', 72 )
insert into @flow values ('2009/10/22 05:26:53.2', 70 )
insert into @flow values ('2009/10/22 05:36:22.1', 84 )
insert into @flow values ('2009/10/22 05:46:16.3', 81 )
insert into @flow values ('2009/10/22 05:56:16.2', 75 )
insert into @flow values ('2009/10/22 06:16:17.3', 73 )
insert into @flow values ('2009/10/22 06:26:16.9', 75 )
insert into @flow values ('2009/10/22 06:36:17.7', 71 )
insert into @flow values ('2009/10/22 06:57:38.7', 57 )
insert into @flow values ('2009/10/22 06:57:48.9', 44 )
insert into @flow values ('2009/10/22 06:57:53.4', 28 )
insert into @flow values ('2009/10/22 06:57:55.3', 12 )
insert into @flow values ('2009/10/22 07:07:55.1', 0  )

Answer 3

WITH    differences
          AS (
              SELECT    s.dt AS dt_start
                       ,MIN(e.dt) AS dt_end
                       ,DATEDIFF(ms, s.dt, MIN(e.dt)) / 1000.0 AS seconds
              FROM      so1608779 AS s
              INNER JOIN so1608779 AS e
                        ON e.dt > s.dt
              GROUP BY  s.dt
             ),
        results1
          AS (
              SELECT    differences.*
                       ,so1608779.flow
                       ,so1608779.flow * differences.seconds AS volume
                       ,ROW_NUMBER() OVER (ORDER BY differences.dt_start) AS row
              FROM      differences
              INNER JOIN so1608779
                        ON so1608779.dt = differences.dt_start
             )
    SELECT  *
           ,(
             SELECT SUM(volume)
             FROM   results1 AS x
             WHERE  x.row <= results1.row
            ) AS running_total
    FROM    results1