I have a table in SQL Server database, datetime x value, where the data is store each 1 minute. But there are some situations where the data is not always stored every minute. My goal is to calculate the area below the graph, for example using the trapezoidal method.

However, I was unable to translate this need into a query in SQL Server. Could someone help me?

Example:

02/09/2024 05:41:47 1.730000019
02/09/2024 05:42:47 2.299999952
02/09/2024 05:43:47 2.880000114
02/09/2024 05:44:47 3.00999999
02/09/2024 05:45:47 3.480000019
02/09/2024 05:46:47 3.849999905
02/09/2024 05:47:47 4.369999886
02/09/2024 05:48:47 2.720000029
02/09/2024 05:49:47 2.910000086
02/09/2024 05:50:47 3.839999914
02/09/2024 05:51:47 5.03000021
02/09/2024 05:52:47 5.239999771
02/09/2024 05:53:47 5.210000038
02/09/2024 05:54:47 6.78000021
02/09/2024 05:55:47 8.119999886
02/09/2024 05:56:47 9.170000076
02/09/2024 05:57:47 10.10000038
02/09/2024 05:58:47 11.28999996

The result of area below of this data is 2.26 using trapeizodal method (the base unit is hour).

I have a table in SQL Server database, datetime x value, where the data is store each 1 minute. But there are some situations where the data is not always stored every minute. My goal is to calculate the area below the graph, for example using the trapezoidal method.

However, I was unable to translate this need into a query in SQL Server. Could someone help me?

Example:

02/09/2024 05:41:47 1.730000019
02/09/2024 05:42:47 2.299999952
02/09/2024 05:43:47 2.880000114
02/09/2024 05:44:47 3.00999999
02/09/2024 05:45:47 3.480000019
02/09/2024 05:46:47 3.849999905
02/09/2024 05:47:47 4.369999886
02/09/2024 05:48:47 2.720000029
02/09/2024 05:49:47 2.910000086
02/09/2024 05:50:47 3.839999914
02/09/2024 05:51:47 5.03000021
02/09/2024 05:52:47 5.239999771
02/09/2024 05:53:47 5.210000038
02/09/2024 05:54:47 6.78000021
02/09/2024 05:55:47 8.119999886
02/09/2024 05:56:47 9.170000076
02/09/2024 05:57:47 10.10000038
02/09/2024 05:58:47 11.28999996

The result of area below of this data is 2.26 using trapeizodal method (the base unit is hour).

• sql
• sql-server

• 2 Be great to see what you tried that didn't work – Dale K Commented Mar 31 at 18:40
• 2 Please explain how you get the value 2.26 from the data you provided. – Bart McEndree Commented Mar 31 at 19:00
• Does this help? sqlservercentral/blogs/… – Bart McEndree Commented Mar 31 at 19:45
• i'm guessing the idea is to calculate area of a jagged polygon if one would plot the above values in a graph where dates acts like x-axis and values like y-axis. It should be possible if one summarize all the "trapezes" of each steps – siggemannen Commented Apr 1 at 7:15
• Could you explain the 2.26 result? The data looks like a bit under a linear that would go from 2 to 10 (so let's give it an average of 6) during 17 minutes (thus 1 / 4 of hour), I would be expecting something along 6 * 1 / 4 = 1.5 – Guillaume Outters Commented Apr 1 at 10:35

1 Answer 1

A simple trapezes area can be computed using window functions to get each point along its predecessor;
note that the first point has no predecessor, so either use first_value() over (… between 1 precededing …) (which will return the point itself, thus giving its virtual preceding trapeze a width of 0),
or use lag() which will return a null that you later filter out… or just let sum ignore nulls.

Thus:

with trapezes as
(
    select
        datediff(s, lag(t) over (order by t), t) / 3600.0 width,
        (v + lag(v) over (order by t)) / 2.0 height
    from t
)
select sum(width * height) area from trapezes;

Note however that this returns 1.43, not 2.26 (and it can be correlated to a simple "average * width" method which returns 1.45):
is the dataset presented in the question incomplete?

See it running in a fiddle.