Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » SQL Server »

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

Estas en el tema de Problemas con un Extracto: DEBE, HABER y SALDO en el foro de SQL Server en Foros del Web. 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 ...
  #1 (permalink)  
Antiguo 05/08/2013, 13:11
 
Fecha de Ingreso: marzo-2006
Mensajes: 43
Antigüedad: 18 años, 8 meses
Puntos: 0
Pregunta Problemas con un Extracto: DEBE, HABER y SALDO

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:
Ver original
  1. FECHA                 CUENTA  DEBE  HABER      SALDO
  2. 2013-01-01 00:00:00 11200   0.00    1500.00 -1500.00
  3. 2013-01-01 00:00:00 11200   0.00    60.00   -1560.00
  4. 2013-01-01 00:00:00 11200   0.00    400.00  -1960.00
  5. 2013-01-01 00:00:00 11200   0.00    100.00  -2060.00
  6. 2013-01-01 00:00:00 11200   0.00    300.00  -2360.00
  7. 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:
Ver original
  1. INSERT INTO Accounting (AccDate,DebitCredit,Account,Amount) VALUES ('20110101','D',11200,1500)
  2. INSERT INTO Accounting (AccDate,DebitCredit,Account,Amount) VALUES ('20110101','C',11200,60)
  3. INSERT INTO Accounting (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','D',11200,400)
  4. INSERT INTO Accounting (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','C',11200,100)
  5. INSERT INTO Accounting (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','C',11200,300)
  6. INSERT INTO Accounting (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','C',11200,250)
  7.  
  8. WITH CTE_FIRST AS
  9. (
  10.     SELECT ACCDATE,
  11.         ACCOUNT,
  12.         CASE WHEN DEBITCREDIT='D' THEN AMOUNT ELSE 0 END AS DEBIT,
  13.         CASE WHEN DEBITCREDIT='C' THEN AMOUNT ELSE 0 END AS CREDIT,
  14.         ROW_NUMBER()OVER(ORDER BY ACCOUNT,ACCDATE) RN
  15.     FROM ACCOUNTING
  16.     WHERE ACCDATE >='20130101'
  17.    
  18. )
  19. ,CTE_SECOND AS(
  20.     SELECT *,
  21.           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,
  22.           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
  23.     FROM CTE_FIRST A
  24. )
  25.  
  26. SELECT ACCDATE,ACCOUNT,DEBIT,CREDIT,
  27.     CASE WHEN DEBIT=0 THEN 0-(CREDIT+COL2) ELSE DEBIT+COL1 END BALANCE
  28. 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.
__________________
:si: ANTARES:si:
  #2 (permalink)  
Antiguo 05/08/2013, 13:43
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 3 meses
Puntos: 774
Respuesta: Problemas con un Extracto: DEBE, HABER y SALDO

Código SQL:
Ver original
  1. CREATE TABLE #temp
  2. (
  3. accdate datetime,
  4. DebitCredit VARCHAR(20),
  5. Account INT,
  6. Amount INT
  7. )
  8.  
  9. INSERT INTO #temp (AccDate,DebitCredit,Account,Amount) VALUES ('20110101','D',11200,1500)
  10. INSERT INTO #temp (AccDate,DebitCredit,Account,Amount) VALUES ('20110101','C',11200,60)
  11. INSERT INTO #temp (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','D',11200,400)
  12. INSERT INTO #temp (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','C',11200,100)
  13. INSERT INTO #temp (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','C',11200,300)
  14. INSERT INTO #temp (AccDate,DebitCredit,Account,Amount) VALUES ('20110102','C',11200,250)
  15.  
  16.  
  17.  
  18. SELECT t1.accdate,t1.account,t1.DebitCredit,t1.amount,SUM(t2.amount) suma
  19. FROM(
  20. SELECT ROW_NUMBER() OVER(partition BY account ORDER BY account) AS rn, * FROM #temp
  21. ) AS t1 INNER JOIN
  22. (
  23. SELECT ROW_NUMBER() OVER(partition BY account ORDER BY account) AS rn, * FROM #temp
  24. ) AS t2 ON (t1.rn>=t2.rn)
  25. 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!
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #3 (permalink)  
Antiguo 12/08/2013, 07:30
 
Fecha de Ingreso: marzo-2006
Mensajes: 43
Antigüedad: 18 años, 8 meses
Puntos: 0
De acuerdo Respuesta: Problemas con un Extracto: DEBE, HABER y SALDO

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:
Ver original
  1. DECLARE @T TABLE(
  2. FECHA DATETIME,
  3. COMENTARIO NVARCHAR (MAX),
  4. CUENTA VARCHAR(15),
  5. DEBE NUMERIC(15,2),
  6. HABER NUMERIC(15,2)
  7. )
  8.  
  9. INSERT INTO @T
  10. SELECT FECHA, COMENTARIO, CUENTA, DEBE, HABER
  11. FROM DIARIOAPUNTES
  12. WHERE FECHA BETWEEN @STARTDATE AND @ENDDATE;
  13.  
  14. /*
  15. INSERT INTO @T VALUES ('001-0001','20130102',100,0);
  16. INSERT INTO @T VALUES ('001-0001','20130102',0,200);
  17. INSERT INTO @T VALUES ('001-0001','20130102',100,0);
  18. INSERT INTO @T VALUES ('001-0001','20130103',100,0);
  19. INSERT INTO @T VALUES ('001-0001','20130105',0,100);
  20. INSERT INTO @T VALUES ('001-0002','20130105',100,0);
  21. INSERT INTO @T VALUES ('001-0002','20130106',500,0);
  22. */
  23.  
  24. --DEBIT - CREDIT + BALANCE
  25.  
  26. WITH T_ROW AS
  27. (
  28.     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
  29.     FROM @T T1_1
  30. )
  31.  
  32. 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
  33. FROM T_ROW T1
  34.       CROSS APPLY(
  35.             SELECT ROUND(SUM(DEBE)-SUM(HABER),2) AS SALDO
  36.             FROM T_ROW T2
  37.             WHERE T2.CONTADOR < T1.CONTADOR AND T1.CUENTA = T2.CUENTA
  38.       ) AS T2
  39.       WHERE T1.FECHA BETWEEN @STARTDATE AND @ENDDATE
  40.       ORDER BY T1.CUENTA, T1.FECHA
  41. --WHERE T1.ACCOUNTNO = '001-0001'

Reitero el agradecimiento por tu ayuda.

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

Saludos.
__________________
:si: ANTARES:si:
  #4 (permalink)  
Antiguo 12/08/2013, 07:56
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 3 meses
Puntos: 774
Respuesta: Problemas con un Extracto: DEBE, HABER y SALDO

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
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #5 (permalink)  
Antiguo 13/08/2013, 07:00
 
Fecha de Ingreso: marzo-2006
Mensajes: 43
Antigüedad: 18 años, 8 meses
Puntos: 0
Pregunta Respuesta: Problemas con un Extracto: DEBE, HABER y SALDO

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.
__________________
:si: ANTARES:si:
  #6 (permalink)  
Antiguo 13/08/2013, 07:21
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 3 meses
Puntos: 774
Respuesta: Problemas con un Extracto: DEBE, HABER y SALDO

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
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #7 (permalink)  
Antiguo 14/08/2013, 09:03
 
Fecha de Ingreso: marzo-2006
Mensajes: 43
Antigüedad: 18 años, 8 meses
Puntos: 0
Pregunta Respuesta: Problemas con un Extracto: DEBE, HABER y SALDO

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:
Ver original
  1. CONTADOR INT,
  2. FECHA SMALLDATETIME,
  3. CUENTA VARCHAR(15),
  4. COMENTARIO NVARCHAR (300),
  5. DEBE DECIMAL(15,2),
  6. HABER DECIMAL(15,2)
  7. )
  8.  
  9. 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
  10. FROM DIARIOAPUNTES T1_1
  11.  
  12. 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
  13. FROM @T T1
  14.       CROSS APPLY(
  15.             SELECT ROUND(SUM(DEBE)-SUM(HABER),2) AS SALDO
  16.             FROM @T T2
  17.             WHERE T2.CONTADOR < T1.CONTADOR AND T1.CUENTA = T2.CUENTA
  18.       ) AS T2
  19.       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.
__________________
:si: ANTARES:si:
  #8 (permalink)  
Antiguo 14/08/2013, 09:20
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 3 meses
Puntos: 774
Respuesta: Problemas con un Extracto: DEBE, HABER y SALDO

157,000 en 2 horas, estas haciendo algo mal, ya revisaste el plan de ejecucion de tus querys? cuales son tus indices?
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #9 (permalink)  
Antiguo 14/08/2013, 09:27
 
Fecha de Ingreso: marzo-2006
Mensajes: 43
Antigüedad: 18 años, 8 meses
Puntos: 0
Respuesta: Problemas con un Extracto: DEBE, HABER y SALDO

Cita:
Iniciado por Libras Ver Mensaje
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.
__________________
:si: ANTARES:si:
  #10 (permalink)  
Antiguo 14/08/2013, 09:29
 
Fecha de Ingreso: marzo-2006
Mensajes: 43
Antigüedad: 18 años, 8 meses
Puntos: 0
Respuesta: Problemas con un Extracto: DEBE, HABER y SALDO

__________________
:si: ANTARES:si:
  #11 (permalink)  
Antiguo 14/08/2013, 10:29
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 3 meses
Puntos: 774
Respuesta: Problemas con un Extracto: DEBE, HABER y SALDO

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?
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #12 (permalink)  
Antiguo 14/08/2013, 11:41
 
Fecha de Ingreso: marzo-2006
Mensajes: 43
Antigüedad: 18 años, 8 meses
Puntos: 0
Respuesta: Problemas con un Extracto: DEBE, HABER y SALDO

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.
__________________
:si: ANTARES:si:
  #13 (permalink)  
Antiguo 14/08/2013, 12:04
Avatar de Libras
Colaborador
 
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 3 meses
Puntos: 774
Respuesta: Problemas con un Extracto: DEBE, HABER y SALDO

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 :)
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #14 (permalink)  
Antiguo 14/08/2013, 15:20
Avatar de iislas
Colaborador
 
Fecha de Ingreso: julio-2007
Ubicación: Mexico, D.F.
Mensajes: 6.482
Antigüedad: 17 años, 4 meses
Puntos: 180
Respuesta: Problemas con un Extracto: DEBE, HABER y SALDO

Ademas, nunca sera lo mismo un INSERT que un SELECT y mas, cuando intervienen mas tablas.
__________________
MCTS Isaias Islas
  #15 (permalink)  
Antiguo 19/08/2013, 06:25
 
Fecha de Ingreso: marzo-2006
Mensajes: 43
Antigüedad: 18 años, 8 meses
Puntos: 0
Pregunta Respuesta: Problemas con un Extracto: DEBE, HABER y SALDO

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.
__________________
:si: ANTARES:si:
  #16 (permalink)  
Antiguo 19/08/2013, 16:04
Avatar de iislas
Colaborador
 
Fecha de Ingreso: julio-2007
Ubicación: Mexico, D.F.
Mensajes: 6.482
Antigüedad: 17 años, 4 meses
Puntos: 180
Respuesta: Problemas con un Extracto: DEBE, HABER y SALDO

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

Etiquetas: contabilidad, saldo
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 10:25.