Over with Group by

Question

I have a query that I use to return me as an extract, accumulating the values record by record:

SELECT  TOP (100) PERCENT Mes, Credito, Debito, Sum(Credito - Debito)  
over (ORDER BY Emp_id, Mes) AS Saldo , Emp_id
FROM     viewFluxo
ORDER BY Emp_id, Mes

It turns out that the "over" with "group by" only works from SQL2012.

How can I build another query that returns the same result?

The balance column is accumulated record by record.

Answer 1

Dynamically calculate the balance according to the current line key. Beware of the number of records as it may slow down if the amount of data is too large.

Summarize first the months per company with GROUP BY and then select with the dynamic calculation.

WITH TB_Sumarizado as (
SELECT  Mes, Sum(Credito) as Credito, Sum(Debito) as Debito , Emp_id  
    FROM     viewFluxo 
    GROUP BY Mes , Emp_id
    ) 

Select TB_Sumarizado.Mes, TB_Sumarizado.Credito, TB_Sumarizado.Debito,
(Select Sum(b.Credito - b.Debito) from TB_Sumarizado b where b.Mes <= TB_Sumarizado.Mes AND b.Emp_id = TB_Sumarizado.Emp_id ) AS Saldo 
FROM TB_Sumarizado

Answer 2

Through the Tips I solved this way, in the tests seems correct result:

SELECT c.mes,
       c.credito,
       c.debito,
       d.saldo,
       c.emp_id
  FROM viewfluxo AS c
       INNER JOIN (SELECT a.mes,
                          SUM(b.credito - b.debito) AS saldo,
                          a.emp_id
                     FROM viewfluxo AS a
                          INNER JOIN viewfluxo AS b ON a.mes >= b.mes
                    GROUP BY a.emp_id,
                             a.mes) AS d ON c.mes = d.mes
                                        AND c.emp_id = d.emp_id 

Answer 3

You can use WITH nested to get the order and calculate the values:

WITH sequencia AS (
  SELECT ROW_NUMBER() OVER(PARTITION BY r.emp_id ORDER BY r.mes) AS sequencia,
         r.mes,
         r.credito,
         r.debito,
         r.emp_id
    FROM viewFluxo r
),
saldo AS (
  SELECT s.sequencia,
         s.mes,
         s.credito,
         s.debito,
         s.emp_id,
         CAST((s.credito - s.debito) AS NUMERIC(15, 2)) AS saldo
    FROM sequencia s
   WHERE s.sequencia = 1
  UNION ALL
  SELECT s.sequencia,
         s.mes,
         s.credito,
         s.debito,
         s.emp_id,
         CAST((sal.saldo + (s.credito - s.debito)) AS NUMERIC(15, 2))
    FROM saldo sal
         INNER JOIN sequencia s ON s.sequencia = sal.sequencia + 1
                               AND s.emp_id = sal.emp_id
)
SELECT s.emp_id,
       s.mes,
       s.credito,
       s.debito,
       s.saldo
  FROM saldo s
 ORDER BY s.emp_id,
          s.mes
OPTION(MAXRECURSION 0)

---

See working on SQL Fiddle.

---

If you do not want to use ROW_NUMBER as a way to generate a sequence, you can convert the month into DATE and use it to organize values:

WITH saldo AS (
  SELECT vf.mes,
         vf.credito,
         vf.debito,
         vf.emp_id,
         CAST((vf.credito - vf.debito) AS NUMERIC(15, 2)) AS saldo
    FROM viewFluxo vf
   WHERE NOT EXISTS(SELECT 1
                      FROM viewFluxo vf2
                     WHERE vf2.emp_id = vf.emp_id
                       AND vf2.mes < vf.mes)
  UNION ALL
  SELECT vf.mes,
         vf.credito,
         vf.debito,
         vf.emp_id,
         CAST((sal.saldo + (vf.credito - vf.debito)) AS NUMERIC(15, 2))
    FROM saldo sal
         INNER JOIN viewFluxo vf ON CONVERT(DATE, vf.mes + '01', 120) = DATEADD(MONTH, 1, CONVERT(DATE, sal.mes + '01', 120))
                                AND vf.emp_id = sal.emp_id
)
SELECT s.emp_id,
       s.mes,
       s.credito,
       s.debito,
       s.saldo
  FROM saldo s
 ORDER BY s.emp_id,
          s.mes
OPTION(MAXRECURSION 0)

---

See working on SQL Fiddle.

---

I used the following script to create and popular the table:

CREATE TABLE viewFluxo(mes       VARCHAR(6),
                       credito   NUMERIC(15, 2),
                       debito    NUMERIC(15, 2),
                       emp_id    VARCHAR(4));

INSERT INTO viewFluxo(mes, credito, debito, emp_id)
               VALUES('201708', 0,      5000,  '0001'),
                     ('201709', 123.25, 5000,  '0001'),
                     ('201710', 0,      10000, '0001'),
                     ('201711', 0,      10000, '0001'),
                     ('201712', 0,      10000, '0001'),
                     ('201801', 0,      10000, '0001'),
                     ('201802', 0,      10000, '0001'),
                     ('201803', 0,      10000, '0001'),
                     --
                     ('201708', 3600, 900,     '0002'),
                     ('201709', 3600, 1350.75, '0002'),
                     ('201710', 3600, 900,     '0002'),
                     ('201711', 3600, 2000,    '0002'),
                     ('201712', 3600, 750.25,  '0002'),
                     ('201801', 3600, 1009.9,  '0002'),
                     ('201802', 3600, 900,     '0002'),
                     ('201803', 3600, 900,     '0002'),
                     ('201804', 3600, 900,     '0002');