I have a query that I use to return as an extract, accumulating the registry values to registry:
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 happens that "over" with "group by" only works from SQL2012.
How can I construct another query that returns the same result?
The balance column is accumulated record to record.
Dynamically calculate the balance according to the current line key. Be careful with the number of records as it can be slow if the amount of data is too large.
First summarize the months by company with GROUP BY and then select with 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
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
You can use% 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 in SQL Fiddle .
If you do not want to use WITH as a way of generating a sequence, you can convert the month to ROW_NUMBER and use it to organize the 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 in SQL Fiddle .
I used the following script to create and populate 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');