¿Como calcular un campo basado en otro campo de otra fila (registro)?

[[AzidRain]]

Digamos un tabla asi:

Código SQL [-]
  Año------ Importe ---- Dif
2007------ 100       ---- 
2008------ 200       ----
2009-------400      ----
2010-------200      ---

En el campo Dif se desea colocar la diferencia entre el campo importe de la fila actual y el campo importe de la fila inmediata anterior (a excepción de la primera, cuyo valor seria 0.

La idea es obtener algo así:

Código SQL [-]
  Año------ Importe ---- Dif
2007------ 100       ---- 0
2008------ 200       ----100
2009-------400      ----200
2010-------200      --- -200

Mi pregunta es si es posible lograr el resultado únicamente con selects. He logrado hacerlo pero primero pasando la tabla a una en memoria y luego llenando los campos Dif manualmente, pero quizá haya forma de hacerlo directamente en el select.

[[oscarac]]

q base de datos estas usando?
coloca algo de lo que hayas avanzado

[marcoszorrilla]

Quizás este hilo te pueda servir de base para pergeñar tu cálculo.

Un Saludo.

[[AzidRain]]

Gracias por el aporte, de hecho la solución del hilo ya la había probado y en efecto funciona la pega es que consume demasiado tiempo ya que contiene subqueries, de las cuales no soy muy adepto por eso.

Les platico en que consiste la consulta que me pidieron.


Tenemos una tabla "talones" que contiene n campos, pero solo nos interesan 3: "sucursal","fecha","importe".

La consulta debe obtener la facturacion mensual de una o todas las sucursales durante un año determinado indicando el % de aumento de decremento de cada mes contra el mes anterior (excepto enero) y además el mismo % pero del mes contra el mes del año anterior. Debe aparece un año en cada fila, algo mas o menos así:

Código SQL [-]
                                            Enero                                                         Febrero
  Año -----  Facturación  % Cr. Mensual   % Cr. vs Año anterior   Facturación  % Cr. Mensual   % Cr. vs Año anterior   .... etc.

Los % de crecimiento mensuales no tienen problema se sacan con campos calculados y listo, el problema era comparar cada mes contra el mismo mes del año anterior o lo que es lo mismo, cada campo "facturacion" contra el mismo campo de la fila inmediata anterior.

Al final, lo resolví de la manera chapucera, pero que me dió mejor rendimiento en velocidad, cargo el query sin el % de aumento anual y posteriormente mediante un procedimiento, recorro los campos y calculo los valores, como si se tratara de una vil matriz.

Aquí mi query chapucero:

Código SQL [-]
select year(fecha) as anio, sucursales.NOMBRE as n_sucursal,
sum( if(month(talones.fecha)=1,if(:flete is null,subtotal,flete),0)) as enero,
0.00 as dif_a_enero,
sum( if(month(talones.fecha)=2,flete,0)) as febrero,
0.00 as dif_a_febrero,
sum( if(month(talones.fecha)=3,if(:flete is null,subtotal,flete),0)) as marzo,
0.00 as dif_a_marzo,
sum( if(month(talones.fecha)=4,if(:flete is null,subtotal,flete),0)) as abril,
0.00 as dif_a_abril,
sum( if(month(talones.fecha)=5,if(:flete is null,subtotal,flete),0)) as mayo,
0.00 as dif_a_mayo,
sum( if(month(talones.fecha)=6,if(:flete is null,subtotal,flete),0)) as junio,
0.00 as dif_a_junio,
sum( if(month(talones.fecha)=7,if(:flete is null,subtotal,flete),0)) as julio,
0.00 as dif_a_julio,
sum( if(month(talones.fecha)=8,if(:flete is null,subtotal,flete),0)) as agosto,
0.00 as dif_a_agosto,
sum( if(month(talones.fecha)=9,if(:flete is null,subtotal,flete),0)) as septiembre,
0.00 as dif_a_septiembre,
sum( if(month(talones.fecha)=10,if(:flete is null,subtotal,flete),0)) as octubre,
0.00 as dif_a_octubre,
sum( if(month(talones.fecha)=11,if(:flete is null,subtotal,flete),0)) as noviembre,
0.00 as dif_a_noviembre,
sum( if(month(talones.fecha)=12,if(:flete is null,subtotal,flete),0)) as diciembre,
0.00 as dif_a_diciembre,
sum(if(:flete is null,subtotal,flete)) as t_total,
0.00 as dif_a_total
from talones
join sucursales on (sucursales.SUCURSAL=talones.SUCURSAL)
where (talones.cancelado is null or talones.CANCELADO=0) and (talones.SUSTITUCION is null or talones.SUSTITUCION=0) //Algunas facturas no deben considerarse en la suma
and year(fecha)>2000 
and (talones.sucursal=:sucursal or :sucursal is null)  //Aquí indicamos la sucursal o bien null si queremos todas
group by anio

La chapuza que hice fue "crear" campos de la nada en el query con valores 0.00 que son los que posteriormente lleno a pie mediante código recorriendo cada columna correspondiente y haciendo el cálculo, al final si bien es mas trabajo, el resultado es muchísimo más rápido que la consulta con subconsultas. Afortunadamente Zeos permite modificar campos del query aun siendo compuestos como en este caso, por lo que me salió mejor que crear un Dataset en memoria, copiar del query y luego trabajar con él. No cabe duda que deja uno un rato Delphi y como que se oxida el cerebro.

[roman]

Restingiéndome a tu consulta inicial, y tratándose de MySQL, yo haría algo así:

Código SQL [-]
set @anterior=0;
select *, importe - @anterior, @anterior:=importe
from tabla

// Saludos

[[AzidRain]]

Y había pensado en eso Roman, pero como mencionaba uso Zeos, para poder correr un script de mas de una sentencia SQL tengo que usar TZSQLProcessor, el cual no me devuelve datos. Había pensando en ese enfoque pero utilizando una tabla temporal de MySQL para guardar el resultado pero por tiempos lo tuve que dejar en la chapuza que mostré, además de que ya de por sí la consulta es un poco compleja, quedará para la fase de optimización de códigos.

[roman]

En este momento no puedo probar pero casi estoy seguro que, mientras no cierres la conexión, puedes ejecutar ambas sentencias por separado y la segunda conservará el valor de la variable.

// Saludos

[[fjcg02]]

Has probado a hacer esta prueba ??
No sé si es una burrada, pero igual funciona.

month(talones.fecha)=1 and year(talones.fecha)=year(fecha) ... as enero -->> año actual
month(talones.fecha)=1 and year(talones.fecha)-1=year(fecha)-1 as enero_año_anterior -->> año anterior

con esto consigues tener en una fila añoactual y anterior, aunque tengo la duda de que trage o cruja con el group by.

Dices que el código que pones es un poco 'guarrete'. No pienso lo mismo. Es largo, un poco indigesto, pero creo que a nivel de rendimiento es el más eficiente.

Por otro lado, he visto en algún sistema, utilizar una tabla auxiliar de tiempo, con una estructura similar a la que pongo, para utilizarla como pivote para calculos temporales.

- fecha
- año
- dia
- mes
- dia semana
- semana
- trimestre

Cruzando tus datos con ésta, puedes crear el campo que necesites para pivotar, totalizando por el campo que quieras ( año, día, mes, semana, trimestre, ... ). Combinado con unos filtros convenientes, consigues resultados bastante buenos.

Espero que te sirva de ayuda.

Un saludo

[[AzidRain]]

fj, tu codigo funciona y es bien lógico pero en efecto al toparse con el group ya no funciona. Esta interesante lo que mencionas de la tabla auxiliar, ojala pudieras dar un ejemplo ya que sin duda a todos nos serviría.

Por cierto, el cliente que pidió obtener estos datos me comentaba cuando me explicó en una hoja de papel como quería que aparecieran, cito: "es bien sencillo, al cabo los datos (la tabla de facturas) ya están ahi", no supe si reirme o llorar jajaja. Y luego otra cosa que pidió es que se imprimiera en hojas tamaño legal apaisadas. Obviamente dado el tamaño de la tabla resultante no caben todos los meses y habrá que hacer el reporte a la "antigüita" ya que QuickReport no hace la magia de colocar en otra hoja lo que no quepa, pero esa ya es otra historia para otro post.

[[fjcg02]]

Hola,
os adjunto un ejemplo de lo que he comentado. Es conceptual. Esto se podría combinar con una ventana en la que se vaya haciendo zoom, ya que seguramente no entren todos los datos en una pantalla: de años a trimestres, de trimestres a meses, ...
Es importante valorar qué es lo que se quiere poner en filas y columnas, ya que habrá que acotar, no cabe todo en la hoja, aunque se ponga apaisada.
Lo que tu cliente puede necesitar es el TAM (tendencia anual móvil). Es el % de aumento o decremento del año con respecto al año del mes anterior. Es decir, cuanto más o menos he vendido/cobrado/soportado cada mes respecto del periodo de 12 meses anterior. Igual con esas le llevas a tu terreno, que esa información cabe en una sola hoja.

Código SQL [-]
select sucursales.NOMBRE as n_sucursal,
/* MESES */
sum( if(BI.MES=1,if(:flete is null,subtotal,flete),0)) as enero,
sum( if(BI.MES=2,if(:flete is null,subtotal,flete),0)) as febrero,
sum( if(BI.MES=3,if(:flete is null,subtotal,flete),0)) as marzo,
sum( if(BI.MES4,if(:flete is null,subtotal,flete),0)) as abril,
sum( if(BI.MES=5,if(:flete is null,subtotal,flete),0)) as mayo,
sum( if(BI.MES=6,if(:flete is null,subtotal,flete),0)) as junio,
sum( if(BI.MES=7,if(:flete is null,subtotal,flete),0)) as julio,
sum( if(BI.MES=8,if(:flete is null,subtotal,flete),0)) as agosto,
sum( if(BI.MES=9,if(:flete is null,subtotal,flete),0)) as septiembre,
sum( if(BI.MES=10,if(:flete is null,subtotal,flete),0)) as octubre,
sum( if(BI.MES=11,if(:flete is null,subtotal,flete),0)) as noviembre,
sum( if(BI.MES=12,if(:flete is null,subtotal,flete),0)) as diciembre,
sum(if(:flete is null,subtotal,flete)) as t_total,

/* TRIMESTRES */
sum( if(BI.TRIMESTRE=1,if(:flete is null,subtotal,flete),0)) as "TRIMESTRE 1",
sum( if(BI.TRIMESTRE=2,if(:flete is null,subtotal,flete),0)) as "TRIMESTRE 2",
sum( if(BI.TRIMESTRE=3,if(:flete is null,subtotal,flete),0)) as "TRIMESTRE 3",
sum( if(BI.TRIMESTRE=4,if(:flete is null,subtotal,flete),0)) as "TRIMESTRE 4",

/* TRIMESTRES AÑO ANTERIOR */
sum( if(BI.ANIOANTERIOR=YEAR(talones.Fecha)-1 AND BI.TRIMESTRE=1,if(:flete is null,subtotal,flete),0)) as "TRIMESTRE 1 AÑO ANTERIOR",
sum( if(BI.ANIOANTERIOR=YEAR(talones.Fecha)-1 AND BI.TRIMESTRE=2,if(:flete is null,subtotal,flete),0)) as "TRIMESTRE 2  AÑO ANTERIOR",
sum( if(BI.ANIOANTERIOR=YEAR(talones.Fecha)-1 AND BI.TRIMESTRE=3,if(:flete is null,subtotal,flete),0)) as "TRIMESTRE 3  AÑO ANTERIOR",
sum( if(BI.ANIOANTERIOR=YEAR(talones.Fecha)-1 AND BI.TRIMESTRE=4,if(:flete is null,subtotal,flete),0)) as "TRIMESTRE 4  AÑO ANTERIOR",

from talones
join sucursales on (sucursales.SUCURSAL=talones.SUCURSAL)
join BI on ( talones.fecha=BI.fecha)
where (talones.cancelado is null or talones.CANCELADO=0) and (talones.SUSTITUCION is null or talones.SUSTITUCION=0) //Algunas facturas no deben considerarse en la suma
and year(fecha)>2000 
and (talones.sucursal=:sucursal or :sucursal is null)  //Aquí indicamos la sucursal o bien null si queremos todas
group by sucursales.NOMBRE

Respecto del ejemplo, ten en cuenta lo siguiente:
- No es normal incluir meses, trimestres, ... pero es para ilustrar el ejemplo.
- he quitado el año de las filas. Lo normal es que se calcule un año con referencia del año anterior.
- para que funcione, tienes que tener la tabla BI, con un registro por fecha ( 365 registros por año ). Si no lo haces así, no saldrán los resultados, ya que no habrá encuentro de datos.
- Aunque en este ejemplo no se aprecia, cuando son cálculos complejos, se agradece bastante.

Espero que te sirva de ayuda y que te inspire algunas ideas.

Si tienes cualquier duda, me comentas e intentaré ayudarte.

Un saludo