SQL function for summing values ​​/ summing volumes over specific time periods

Question

I am trying to calculate the total amount of water irrigated over a piece of land. I have a thread change at a point in time written to an 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 

It is simply not the case to summarize the values ​​and assume that this is the total amount of water irrigated.

what needs to be done is to work out the time difference for the time stamp and calculate the volume for this time duration and then use it for the hour (s) selected by the user.

therefore, for the above data, the time difference will be (within 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 that hour (from 4 to 5 in the morning), it is: 373811.6 cubic meters of water divided by 3600 = 103.8365556

The question arises: how can I do this with SQL - I'm completely lost and do not know where to start, any help will be appreciated

Answer 1

This answer assumes you are using SQL Server. Your “within the first hour” sample actually includes more than the first hour; it should stop after line 00: 10: 00.1, which I think of.

You can find the previous line for each line by appending it to yourself, then appending a different time, and then there can be nothing between the first two lines:

 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 amount for the period. To calculate the total number of hours, you must break the records crossing the hour boundary. You can do this by adding an entry at the end of each hour, for example:

 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 with the WITH statement to calculate the amount 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) 

Result:

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

Here is a sample dataset that 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 2

 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 

Answer 3

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 an interval of one line and a 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 that, you can use it as a subquery and do some multiplication and sums.

Of course, this is a simplified example.