Iniciar sesión
Crear cuenta
Crear cuentaCrear cuenta
mysql> SELECT * FROM tabla;
+------+---------+------------+------------+---------+---------+
| id | kanexo | kcodexist | kfecdoc | kuniing | kuniret |
+------+---------+------------+------------+---------+---------+
| 24 | 0001001 | BAJ1020099 | 2016-01-01 | 52.00 | 0.00 |
| 25 | 0001001 | BAJ1020099 | 2016-01-05 | 0.00 | 3.00 |
| 26 | 0001001 | BAJ1020099 | 2016-01-06 | 0.00 | 2.00 |
| 27 | 0001001 | BAJ1020099 | 2016-01-09 | 0.00 | 3.00 |
| 28 | 0001001 | BAJ1020099 | 2016-01-13 | 0.00 | 20.00 |
+------+---------+------------+------------+---------+---------+
5 rows in set (0.00 sec)
mysql> SELECT-> T1.id,
-> T1.kanexo,
-> T1.kcodexist,
-> T1.kfecdoc,
-> T1.kuniing,
-> T1.kuniret,
-> ( SELECT SUM(kuniing) - SUM(kuniret)
-> FROM tabla T2
-> WHERE T2.kanexo = T1.kanexo
-> AND T2.kfecdoc <= T1.kfecdoc
-> GROUP BY T2.kanexo) total
-> FROM tabla T1;
+------+---------+------------+------------+---------+---------+-------+
| id | kanexo | kcodexist | kfecdoc | kuniing | kuniret | total |
+------+---------+------------+------------+---------+---------+-------+
| 24 | 0001001 | BAJ1020099 | 2016-01-01 | 52.00 | 0.00 | 52.00 |
| 25 | 0001001 | BAJ1020099 | 2016-01-05 | 0.00 | 3.00 | 49.00 |
| 26 | 0001001 | BAJ1020099 | 2016-01-06 | 0.00 | 2.00 | 47.00 |
| 27 | 0001001 | BAJ1020099 | 2016-01-09 | 0.00 | 3.00 | 44.00 |
| 28 | 0001001 | BAJ1020099 | 2016-01-13 | 0.00 | 20.00 | 24.00 |
+------+---------+------------+------------+---------+---------+-------+
5 rows in set (0.00 sec)
BEGIN
DECLARE @COUNT INT = 0,@SALDO INT = 0
,@TOT INT,@MINIMO INT = 0
,@CONTPRIMERSELECT INT,@COUNT2 INT = 1 --CORRELATIVO
,@NUM INT,@CANT INT--CON ESTO OBTENEMOS LA CANTIDAD DE REGISTROS AGRUPADOS
SET @CONTPRIMERSELECT = (SELECT COUNT(*) FROM (SELECT MIN(ID) AS ID
FROM #TMP_1 Z GROUP BY Z.KANEXO,Z.KCODEXIST) AS TAB)
WHILE @COUNT2 <= @CONTPRIMERSELECT
BEGIN
--MINIMO POR GRUPO
SET @MINIMO = ( SELECT IDX AS MINIMO FROM (SELECT ROW_NUMBER() OVER(ORDER BY IDX ASC) AS XX, IDX FROM
(SELECT MIN(Z.ID) IDX FROM #TMP_1 Z
GROUP BY Z.KANEXO,Z.KCODEXIST) AS TAB ) AS AMANECER WHERE XX = @COUNT2)
SET @CANT = (SELECT C FROM (SELECT ROW_NUMBER() OVER(ORDER BY IDX ASC) AS XX,C
FROM (SELECT MIN(Z.ID) IDX,COUNT(*) C FROM #TMP_1 Z GROUP BY Z.KANEXO,Z.KCODEXIST) AS T1)
AS T2 WHERE XX=@COUNT2)
---------------------------------------------------------------------------------------------------------
WHILE @COUNT < @CANTBEGIN
SELECT @SALDO = ( @SALDO +KUNIING - KUNIRET)
FROM #TMP_1
WHERE ID = @MINIMO + @COUNT
SELECT A.KUNIING,A.KUNIRET,@SALDO FROM #TMP_1 A
WHERE A.ID = @MINIMO + @COUNT
SET @COUNT = @COUNT +1
END
SET @SALDO = 0---------------------------------------------------------------------------------------------------------
SET @COUNT = 0SET @COUNT2 = @COUNT2 + 1
END
END