{"id":12666,"date":"2021-11-11T15:59:23","date_gmt":"2021-11-11T18:59:23","guid":{"rendered":"http:\/\/kb.elipse.com.br\/en\/?p=12666"},"modified":"2021-11-11T15:59:23","modified_gmt":"2021-11-11T18:59:23","slug":"sum-total-filtered-by-date-with-overtransact-sql-clause","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/sum-total-filtered-by-date-with-overtransact-sql-clause\/","title":{"rendered":"Sum total filtered by date with OVER(Transact-sql) clause."},"content":{"rendered":"

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

According to Microsoft’s documentation<\/a>, 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<\/p>\n

(…) 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.<\/p><\/blockquote>\n

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.<\/p>\n

\"soma<\/p>\n

To get to this accumulated value, we will use the following SQL code in direct edition:<\/p>\n

SELECT Somatorio.E3TimeStamp, Somatorio.Campo, Sum(Somatorio.Campo) OVER(ORDER BY Somatorio.E3TimeStamp) AS Acumulado\r\nFROM Somatorio WHERE(Somatorio.E3TimeStamp >=#<%DataInicial%># AND Somatorio.E3TimeStamp <=#<%DataFinal%>#)\r\nORDER BY Somatorio.E3TimeStamp ASC<\/pre>\n
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<\/strong> button:<\/p>\n
\"sum<\/p>\n
Requirements:<\/h3>\n
–\u00a0 SQL Server database;
\n– Elipse E3 version 5.0.329 or higher.<\/p>\n
Related articles:<\/h3>\n
\n