Sum of SQL accumulated value

Question

I have a stock value and need to display the information of the stock movement based on invoice.

Table Drive

Cod | Tipo | NumeroNota | Quantidade | DataEmissao | Material
 1  |  S   |    12312   |    1       |  2019-05-16 |     1
 2  |  S   |    12313   |    4       |  2019-05-17 |     1
 3  |  E   |    12316   |    10      |  2019-05-17 |     1
 4  |  S   |    12317   |    9       |  2019-05-18 |     1
 5  |  S   |    12323   |    6       |  2019-05-20 |     1
 6  |  S   |    12325   |    12      |  2019-05-20 |     1
 7  |  E   |    12333   |    3       |  2019-05-24 |     1

The column Tipo refers to the entry and exit, represented by the initial letters..

Historical Stock Table

Cod | DataEstoque | Quantidade | Material
 1  |  2019-05-16 |     24     |    1         -> Antes da data tinha 25
 2  |  2019-05-17 |     30     |    1
 3  |  2019-05-18 |     21     |    1
 4  |  2019-05-19 |     21     |    1
 5  |  2019-05-20 |     3      |    1
...                                           -> repete a mesma quantidade para os dias sem movimentação
 9  |  2019-05-24 |     6      |    1

Having knowledge of the table above, what I imagine is to take the stock in the table HistoricoEstoque based on the final date (HistoricoEstoque.DataEstoque = @DataFim), so I have access to stock amount on order date. Join with table Movimentacao in order to find the amount moved between the interval @DataInicio and @DataFim making a stock rule observing the Type of Movement (Input (E) and Output (S)).

As the quantidade is from the data fim stock, resalto that for each drive Tipo = E should be subtracted(-) and to output(+). That is, because it is doing the opposite.

I want to know, the stock and the movement of the material in the interval of two dates.

Expected result: Filter: Start date = 2019-05-17 and end date = 2019-05-20

Material|DataEmissao|Quantidade|EstoqueAtual
    1  |2019-05-20 |   -12    |     3    -> tenha ser o estoque atual
    1  |2019-05-20 |   -6     |     15
    1  |2019-05-18 |   -9     |     21
    1  |2019-05-17 |   10     |     30
    1  |2019-05-17 |   -4     |     20
    1  |2019-05-16 |   -      |     24

What is possible to do? There is another suggestion?

Answer 1

You can recover the desired result with the following query:

DECLARE @inicio   DATE;
DECLARE @fim      DATE;
DECLARE @material INTEGER;

SET @inicio = '2019-05-17';
SET @fim = '2019-05-20';
SET @material = 1;

WITH cumulativo AS (
  SELECT TOP(1) 0 AS cod,
                e.dataestoque AS dataemissao,
                e.quantidade
    FROM estoque e
   WHERE e.dataestoque < @inicio
     AND e.material = @material
   ORDER BY e.dataestoque DESC
  UNION
  SELECT m.cod,
         m.dataemissao,
         CASE m.tipo WHEN 'E' THEN m.quantidade ELSE m.quantidade * -1 END
    FROM movimentacao m
   WHERE m.dataemissao BETWEEN @inicio AND @fim
     AND m.material = @material
)
SELECT c.dataemissao,
       c.quantidade,
       SUM(c2.quantidade) AS total
  FROM cumulativo c
  INNER JOIN cumulativo c2 ON c.dataemissao >= c2.dataemissao
                          AND c.cod >= c2.cod
  GROUP BY c.dataemissao,
           c.cod,
           c.quantidade
  ORDER BY c.dataemissao,
           c.cod

---

WITH

APPLIES TO: SQL Server (from 2008 onwards), Banco de Dados SQL do Azure, SQL Data Warehouse do Azure and Parallel Data Warehouse

Specifies a temporary named result set, known as a CTE (common table expression). It is derived from a simple query and defined in the execution scope of a single instruction SELECT, INSERT, UPDATE, DELETE or MERGE. This clause can also be used in a CREATE VIEW statement as part of the instruction SELECT that defines it. A common table expression can include references to itself. This is what we call the common recursive table expression.

Syntax

[ WITH <common_table_expression> [ ,...n ] ]  

<common_table_expression>::=  
expression_name [ ( column_name [ ,...n ] ) ]  
AS  
( CTE_query_definition )

Arguments

expression_name

Is a valid identifier for the common table expression. expression_name must be different from the name of any other common table expression defined in the same clause WITH <common_table_expression>, but expression_name may be the same as the name of a base table or display. Any reference to expression_name query uses the common table expression, not the base object.

column_name

Specifies a column name in the common table expression. Duplicate names are not allowed in a single definition of CTE. The number of column names specified shall correspond to the number of columns in the result set of the CTE_query_definition. The list of column names will be optional only if separate names are provided for all the resulting columns in the query definition.

CTE_query_definition

Specifies an instruction SELECT whose result set populates the common table expression. The statement SELECT of CTE_query_definition must meet the same requirements as for the creation of an exhibition, with the exception that a CTE cannot define another CTE. For more information, see the Comments and CREATE VIEW (Transact-SQL).

If more than one CTE_query_definition is defined, the definitions of consultation may be joined by one of these two sets of operators: UNION ALL, UNION, EXCEPT or INTERSECT.