[SOLUCIONADO] Problemas con un Extracto: DEBE, HABER y SALDO

[1antares1]

Buenas tardes, amigos.

Les comento quien pueda aportar alguna sugerencia o ayuda breve a lo sucedido.
Para ser breve, a la hora de intentar realizar un Extracto contable con el DEBE y HABER, en SALDO contínuo.

Realmente quedé colgado en mi Consulta, y no consigo alguna solución posible para encontrar lo deseado.

Agradecido estaría de alguna pequeña colaboración por algunos de ustedes.

Estoy buscando algo así:

Código SQL:

FECHA                 CUENTA  DEBE  HABER      SALDO
2013-01-01 00:00:00 11200   0.00    1500.00 -1500.00
2013-01-01 00:00:00 11200   0.00    60.00   -1560.00
2013-01-01 00:00:00 11200   0.00    400.00  -1960.00
2013-01-01 00:00:00 11200   0.00    100.00  -2060.00
2013-01-01 00:00:00 11200   0.00    300.00  -2360.00
2013-01-01 00:00:00 11200   0.00    250.00  -2910.00
 

Realmente no requiero filtrarlo por tipo de cuenta esta vez, sino específicas.

La idea es que con mi Query aún no me da dicho resultado.

Pueden comprarlo creando una tabla provisional o temporal, con el mismo mostrado a continuación:

Código SQL:

INSERT INTO Accounting (AccDate,DebitCredit,Account,Amount) VALUES ('20110101','D',11200,1500)
INSERT INTO Accounting (AccDate,DebitCredit,Account,Amount) VALUES ('20110101','C',11200,60)
INSERT INTO Accounting (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','D',11200,400)
INSERT INTO Accounting (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','C',11200,100)
INSERT INTO Accounting (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','C',11200,300)
INSERT INTO Accounting (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','C',11200,250)
 
WITH CTE_FIRST AS
(
    SELECT ACCDATE,
        ACCOUNT,
        CASE WHEN DEBITCREDIT='D' THEN AMOUNT ELSE 0 END AS DEBIT,
        CASE WHEN DEBITCREDIT='C' THEN AMOUNT ELSE 0 END AS CREDIT,
        ROW_NUMBER()OVER(ORDER BY ACCOUNT,ACCDATE) RN
    FROM ACCOUNTING
    WHERE ACCDATE >='20130101'
    
)
,CTE_SECOND AS( 
    SELECT *,
          ISNULL((SELECT TOP 1 DEBIT FROM CTE_FIRST B WHERE B.ACCOUNT=A.ACCOUNT AND B.RN<A.RN ORDER BY RN DESC),0) COL1,
          ISNULL((SELECT TOP 1 CREDIT FROM CTE_FIRST B WHERE B.ACCOUNT=A.ACCOUNT AND B.RN<A.RN ORDER BY RN DESC),0) COL2
    FROM CTE_FIRST A
)
 
SELECT ACCDATE,ACCOUNT,DEBIT,CREDIT,
    CASE WHEN DEBIT=0 THEN 0-(CREDIT+COL2) ELSE DEBIT+COL1 END BALANCE
FROM CTE_SECOND
 

Algo me ocurre en la validación de suma o no me está tomando consultando de forma líneal....

Toda esta consulta, intenté ayudarme con material que conseguí en la web. Pero no me da la respuesta total.

Si además existe otra mejor manera, no habría problema.

Gracias por su atención.

Saludos.

[Libras]

Código SQL:

CREATE TABLE #temp
(
accdate datetime,
DebitCredit VARCHAR(20),
Account INT,
Amount INT
)
 
INSERT INTO #temp (AccDate,DebitCredit,Account,Amount) VALUES ('20110101','D',11200,1500)
INSERT INTO #temp (AccDate,DebitCredit,Account,Amount) VALUES ('20110101','C',11200,60)
INSERT INTO #temp (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','D',11200,400)
INSERT INTO #temp (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','C',11200,100)
INSERT INTO #temp (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','C',11200,300)
INSERT INTO #temp (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','C',11200,250)
 
 
 
SELECT t1.accdate,t1.account,t1.DebitCredit,t1.amount,SUM(t2.amount) suma
FROM(
SELECT ROW_NUMBER() OVER(partition BY account ORDER BY account) AS rn, * FROM #temp
) AS t1 INNER JOIN
(
SELECT ROW_NUMBER() OVER(partition BY account ORDER BY account) AS rn, * FROM #temp
) AS t2 ON (t1.rn>=t2.rn)
GROUP BY t1.accdate,t1.Account,t1.rn,t1.Amount,t1.DebitCredit
 

Prueba con esto, encontre la idea en este foro:

http://stackoverflow.com/questions/2...cumulative-sum

saludos!

[1antares1]

Buen día, Amigo Libras;

Disculpa la tardanza en responder a ver que tal fue tan factible tu solución, que efectivamente me funcionó también. Y agradecido estoy de ello.

Pero te comento, que la desesperación me hizo encontrar otra posible solución, que me ayudó (puede ser con antigua sentencia o de la vieja escuela ), pero por supuesto, aunque sea lenta la consulta, solo es cuestión de seleccionar de vez en cuando y no algo de rutina.

Te la comparto para que también puedas analizarla y saques conclusión de las diferencias: Muy poco común encontrar estas soluciones a nivel Hispano... O por lo que pude investigar...

Código SQL:

DECLARE @T TABLE(
FECHA DATETIME,
COMENTARIO NVARCHAR (MAX),
CUENTA VARCHAR(15),
DEBE NUMERIC(15,2),
HABER NUMERIC(15,2)
)
 
INSERT INTO @T
SELECT FECHA, COMENTARIO, CUENTA, DEBE, HABER
FROM DIARIOAPUNTES
WHERE FECHA BETWEEN @STARTDATE AND @ENDDATE;
 
/*
INSERT INTO @T VALUES ('001-0001','20130102',100,0);
INSERT INTO @T VALUES ('001-0001','20130102',0,200);
INSERT INTO @T VALUES ('001-0001','20130102',100,0);
INSERT INTO @T VALUES ('001-0001','20130103',100,0);
INSERT INTO @T VALUES ('001-0001','20130105',0,100);
INSERT INTO @T VALUES ('001-0002','20130105',100,0);
INSERT INTO @T VALUES ('001-0002','20130106',500,0);
*/
 
--DEBIT - CREDIT + BALANCE
 
WITH T_ROW AS
(
    SELECT (ROW_NUMBER() OVER(ORDER BY (T1_1.FECHA) ASC)) AS CONTADOR, FECHA, T1_1.CUENTA, T1_1.COMENTARIO, T1_1.DEBE, T1_1.HABER
    FROM @T T1_1
) 
 
SELECT TOP(100) T1.CONTADOR, T1.CUENTA, T1.COMENTARIO AS DESCRIPCION, CONVERT(CHAR(10), T1.FECHA, 103) AS FECHA, T1.DEBE, T1.HABER, ROUND(T1.DEBE-T1.HABER + COALESCE(T2.SALDO,0),2) AS SALDO
FROM T_ROW T1
      CROSS APPLY(
            SELECT ROUND(SUM(DEBE)-SUM(HABER),2) AS SALDO
            FROM T_ROW T2
            WHERE T2.CONTADOR < T1.CONTADOR AND T1.CUENTA = T2.CUENTA
      ) AS T2
      WHERE T1.FECHA BETWEEN @STARTDATE AND @ENDDATE
      ORDER BY T1.CUENTA, T1.FECHA
--WHERE T1.ACCOUNTNO = '001-0001'
 

Reitero el agradecimiento por tu ayuda.

El tema se encuentra solucionado. Lo cerraré cuando lo notes conveniente.

Saludos.

[Libras]

si te funciono perfecto, la logica es casi la misma solo que lo realizas con una CTE junto con varios queryes anidados y en la que te presento se realiza con 2 subqueries, ya depende de ti cual aplicas :)

saludos,
Libras

[1antares1]

Buen día, Amigo Libras....

Sabes que probé tu último método, pero al parecer no me está validando entre Crédito y Débito por que me suma toda la línea.

No sé en que parte se podría aplicar el WHEN para que dependiendo del caso, sume o reste en el final.

Mi consulta funciona, pero la noto demasiado lenta. Y no me parece del total operativa, debido que se queda consultando y consultando, y no es lo que busco, por que organizo por Cuentas y Fecha, y me ralentiza en total.

Creo que con el WITH o el Cross Apply me fuerza bastante.

Intentaré optimizarlo a ver que tal. Luego respondo.

Agradecido.

Saludos.

[Libras]

podrias usar la consulta que te puse tomando en cuenta si es C entonces que lo sume y si es D que lo reste....lo que tu pediste fue que se hiciera la suma y se fueran viendo los cambios que es lo que hace la consulta que te pase nunca dijiste que tomara en cuenta si era un caso que sumara y si fuera el otro lo restara...

saludos,
Libras

[1antares1]

Buen día; Amigo Libras nuevamente....

Te comento que a la final y notada más simplificada fue la adjuntada a continuación:

Código MySQL:

DECLARE @T TABLE(
CONTADOR INT,
FECHA SMALLDATETIME,
CUENTA VARCHAR(15),
COMENTARIO NVARCHAR (300),
DEBE DECIMAL(15,2),
HABER DECIMAL(15,2)
)
 
INSERT INTO @T
SELECT (ROW_NUMBER() OVER(ORDER BY (T1_1.CUENTA) ASC)) AS CONTADOR, FECHA, T1_1.CUENTA, T1_1.COMENTARIO, T1_1.DEBE, T1_1.HABER
FROM DIARIOAPUNTES T1_1
 
SELECT T1.CONTADOR, T1.CUENTA, T1.COMENTARIO AS DESCRIPCION, CONVERT(CHAR(10), T1.FECHA, 103) AS FECHA, T1.DEBE, T1.HABER, ROUND(T1.DEBE-T1.HABER + COALESCE(T2.SALDO,0),2) AS SALDO
FROM @T T1
      CROSS APPLY(
            SELECT ROUND(SUM(DEBE)-SUM(HABER),2) AS SALDO
            FROM @T T2
            WHERE T2.CONTADOR < T1.CONTADOR AND T1.CUENTA = T2.CUENTA
      ) AS T2
      ORDER BY T1.CUENTA, T1.FECHA
 

Por supuesto, me lanza 157 mil filas en 2 horas. Diría que aún es mucho tiempo, pero es lo más optimizado que he conseguido.

Ahora, lo que si me está aún complicando y estoy intentando realizar la simulación a una aplicación contable, es realizar mediante SQL, una fila final de cada cuenta con el total de la apertura, Debe, Haber y Saldo con su respectiva fórmula. No sé si en este caso podrías intentar darme algunos consejos o empujones de ideas.... Casi seguro esto tiende a ser más tipo programación, siendo un poco complicado en Base de Datos, pero soy del que intenta encontrar el jugo completo de una herramienta, y en caso de tener que usar alternativas, se usan. Pero nada mejor que realizarlo mediante SQL y usando Reporting Services para solo su interfaz y manejo de parámetros.

Te comento lo que intento conseguir, que de verdad es bastante complicado:



Estoy buscando la manera de controlarlo por un ROW_NUMBER/PARTITION a nivel de cuenta, y la misma, validarla para realizar la respectiva fórmula y crear una línea final en todos los resultados.

Por qué no todas las fórmulas en las cuentas sobre la suma y resta es semejante. Diría que las cuentas iniciales en 1xxxx es igual a las de las 5 en adelante. Y las 2,3,4 otra fórmula.

La idea que intento encontrar es particionarlo por un contador y poder intentar crear una línea final luego de su culminación de registros.

Cualquier cosa que consiga, lo publicaré. Si consigues algo antes, estaría bastante agradecido.

Sin más que agregar;

Cordial saludo.

[Libras]

157,000 en 2 horas, estas haciendo algo mal, ya revisaste el plan de ejecucion de tus querys? cuales son tus indices?

[1antares1]

Iniciado por Libras

157,000 en 2 horas, estas haciendo algo mal, ya revisaste el plan de ejecucion de tus querys? cuales son tus indices?

Es que ese es el Query en general.

Cojo la tabla completa del Diario de Apuntes completo. Todas las cuentas, todos los asientos de todo el año hasta ahora. Con su DEBE y HABER, y a partir de ello, aplico la sentencia comentada en la respuesta anterior, para sacar el extracto del saldo, con su misma descripción, cuenta y ordenado por cuenta y fecha.

Hay algo extraño en dicha fórmula, pero es la única que me ha funcionado y me da lo requerido.

Si deseas, has la prueba. Pero en ese CROSS APPLY me está ralentizando la consulta del extracto lineal. Supongo que usando cursores (que no estoy muy apto aún), podría conseguir una solución decente...

Sin más;

Cordial saludo.

[1antares1]

[Libras]

la parte donde tarda mas cual es? la insercion o la obtencion de registros? porque no creas un indice por el campo de ordenamiento de la tabla @t?

[1antares1]

Buenas tardes, Amigo Libras...

Realmente nunca he intentado realizar un índice para determinar o verificar el tiempo de consulta o inserción de alguna sentencia.

Lo que estoy casi seguro, es que el problema es al obtener los datos, debido que también realicé la prueba en solo insertar y es cuestión de segundos.

Otra cosa que mejoró bastante fue cuando empecé a contar desde la inserción y la misma condición debo llevarla a que la de abajo. Es decir, sea por fecha y cuenta, para que no realice inserción temporal por gusto.

Del resto.... Aún sigue estando lento, pero no como antes (40% menos), pero si perdura su 1hr~.

¿Cómo podría averiguarle usando el tema de los índices?

Sin más;

Agradecido.

Saludos cordiales.

[Libras]

nunca has usado un indice en tus tablas? los indices se usan para mejorar el rendimiento de un query, esto ya que se ordenan los datos por los campos que se necesitan y al presentar la informacion es mas rapido para el manejador, investiga un poco sobre indices en sql server :)

[iislas]

Ademas, nunca sera lo mismo un INSERT que un SELECT y mas, cuando intervienen mas tablas.

[1antares1]

Buenos días....

Pues no creo que el inconveniente sea con los Índices, ni tampoco el INSERT en la tabla temporal. Me lo realiza en cuestión de nanosegundos las 180 mil filas.

Creo que el detalle está en la consulta, tipo CROSS APPLY lineal. Algo no va bien ahí, que seguiré investigando.

Gracias por la información nuevamente.

Cordial saludo.

[iislas]

Utiliza Tunning Advisor, herramienta de SQL Server que te permite revisar tus query's y te da propuestas de mejora