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.
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:
Requirements:
– SQL Server database;
– Elipse E3 version 5.0.329 or higher.
Related articles:
- Tutorial for Microsoft® SQL Server® Express 2017 installation and configuration.
- Creating a cumulative sum with an SQL database using just one query.
- Using databases with E3: Chapter 2 – Connection with the database.
- SELECT – OVER clause (Transact-SQL)
Attachments:
