Sum total filtered by date with OVER(Transact-sql) clause.

This article shows how to retrieve the sum total of an estimated time period by using OVER clause alongside WHERE clause in Elipse E3.

According to Microsoft’s documentation, the OVER clause is used with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results. Therefore, it

(…) determines the partitioning and ordering of a rowset before applying the associated window function. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window.

Next, we will show you in the example below how to retrieve the sum total of an estimated time period by using the OVER clause alongside the WHERE clause.

soma acumulada com cláusula OVER

To get to this accumulated value, we will use the following SQL code in direct edition:

SELECT Somatorio.E3TimeStamp, Somatorio.Campo, Sum(Somatorio.Campo) OVER(ORDER BY Somatorio.E3TimeStamp) AS Acumulado
FROM Somatorio WHERE(Somatorio.E3TimeStamp >=#<%DataInicial%># AND Somatorio.E3TimeStamp <=#<%DataFinal%>#)
ORDER BY Somatorio.E3TimeStamp ASC

To use the demo application below, you must set up the Elipse E3 database to point to a database created in SQL Server Management Studio; then, when executing the application, a few records will be added via Record Register button:

sum total OVER clause

Requirements:

–  SQL Server database;
– Elipse E3 version 5.0.329 or higher.

Related articles:


Attachments:

ProjetoSomatorio.zip

Este artigo foi útil? Was this post helpful?
Yes0
No0

Leave a Reply

Your email address will not be published.Required fields are marked *