[RESUELTO] SELECT que calcule datos con registro anterior

[Fdv]

Un saludo

Pues eso... estoy liado con un documento de OpenOffice Base (Mantenimiento de vehículo), en el que quiero que calcule datos con respecto al registro anterior, me explico y resumo la Tabla a lo básico:

Tabla Repostajes: Campos: id, Fecha, kilometros

Yo quisiera un Select que diera este resultado:

- Fecha, kilometros, (kilometros-(kilometros fecha anterior)) AS recorrido

Estoy como loco y no consigo dar con la tecla, lo más que me he acercado es haciendo esto

Código: Seleccionar todo

SELECT "R"."fecha" AS "Fecha", "R"."kms" AS "Kilometros",  "R"."kms" - "Rant"."kms" 
FROM "Repostajes" AS "R"
LEFT JOIN "Repostajes" AS "Rant" ON "R"."fecha" > "Rant"."fecha" 

Pero, me duplica datos... imaginar estos registros:

fecha kms
01/01/2015 ..... 150
02/01/2015 .... 200
03/01/2015 .... 300

dá

Fecha Kms Diferencia
01/01/15 ...150... 0
02/01/15 ...200... 50
03/01/15 ...300... 150
03/01/15 ...300... 100

He intentado buscar si hay forma de insertar en un campo virtual el registro actual. algo así como ROW_COUNT() , de esta forma sería fácil... pero no he visto como hacerlo, y la consulta tira error. También si se pudiera crear virtualmente un campo con variable autoincrementable, serviría...

No me quiero basar en el id (autoincrementado) de la tabla puesto que éste puede cambiar, o el usuario puede introducir los datos en fechas salteadas, sin orden.

Yo estoy que ya no puedo más ¿ Alguien me ayuda?

[RMG]

Hola,

David por darte una idea. Si los datos los obtienes a través de un formulario y las fecha son correlativas, puedes obtener el id del registro actual del formulario y ejecutar una SQL que obtenga los km del id actual -1.

Saludos

[Fdv]

Hola,

David por darte una idea. Si los datos los obtienes a través de un formulario y las fecha son correlativas, puedes obtener el id del registro actual del formulario y ejecutar una SQL que obtenga los km del id actual -1.

Saludos

Con programación, es buena idea esa, acceder a los registros del formulario y mediante bucle realizar las operaciones y actualización.

... pero es que lo quiero para una consulta (o vista) y luego crear el informe.

Gracias y un saludo.

[FJCC-ES]

Si la tabla repostajes contiene

Tabla.PNG (20.61 KiB) Visto 33091 veces

esta consulta

Código: Seleccionar todo

SELECT 
   "A"."Fecha1" AS "Fecha", 
   "A"."kilometros", 
   "A"."Recorrido" 
FROM
   (
    SELECT 
         "R"."Fecha" AS "Fecha1", 
         "R"."kilometros", 
         "R"."kilometros" - "R2"."kilometros" AS "Recorrido",
         DATEDIFF('dd', "R2"."Fecha","R"."Fecha") AS "Dif" 
    FROM 
        "Repostajes" AS "R" LEFT JOIN "Repostajes" AS "R2" ON "R"."Fecha" > "R2"."Fecha"
   ) AS "A"
INNER JOIN
   (   
   SELECT 
        "R"."Fecha" , 
        min(DATEDIFF('dd', "R2"."Fecha","R"."Fecha")) AS "MinDif" 
   FROM 
        "Repostajes" AS "R" LEFT JOIN "Repostajes" AS "R2" ON "R"."Fecha" > "R2"."Fecha" 
   GROUP BY "R"."Fecha"
   ) AS "B" 
ON "A"."Fecha1" = "B"."Fecha" 
WHERE "A"."Dif" = "B"."MinDif"

devuelve

Consulta.PNG (4.6 KiB) Visto 33091 veces

[Fdv]

Muchas gracias, comprobado y funciona, pero la consulta debo marcar ejecutar directamente el comando SQL.

Ahora tengo que adaptarla a mí consulta, que quiero que muestre más datos...

Eso sí, me hubiese gustado que mostrase el primer registro, con los kilómetros recorridos en vació (o todos los realizados desde 0), lo he conseguido con UNION, pero no sé si será una burrada y se pueda hacer de otra forma más elegante:

Código: Seleccionar todo

SELECT 
   "F"."fecha" AS "Fecha",
   "F"."kms" AS "Kilometros",
   NULL
FROM
   "Repostajes" AS "F"
ORDER BY "Fecha" ASC
LIMIT 1

UNION   


SELECT
   "A"."Fecha1" AS "Fecha",
   "A"."Kilometros",
   "A"."Recorrido"
FROM
   (
    SELECT
         "R"."fecha" AS "Fecha1",
         "R"."kms" AS "Kilometros",
         "R"."kms" - "R2"."kms" AS "Recorrido",
         DATEDIFF('dd', "R2"."fecha","R"."fecha") AS "Dif"
    FROM
        "Repostajes" AS "R" LEFT JOIN "Repostajes" AS "R2" ON "R"."fecha" > "R2"."fecha"
   ) AS "A"

INNER JOIN
   (   
   SELECT
        "R"."fecha" ,
        min(DATEDIFF('dd', "R2"."fecha","R"."fecha")) AS "MinDif"
   FROM
        "Repostajes" AS "R" LEFT JOIN "Repostajes" AS "R2" ON "R"."fecha" > "R2"."fecha"
   GROUP BY "R"."fecha"
   ) AS "B"
ON "A"."Fecha1" = "B"."fecha"
WHERE "A"."Dif" = "B"."MinDif"

[Fdv]

Ya la he adaptado a mis base de datos, voy a preparar algún que otro informe, completar la ayuda con las nuevas funciones, etc.... en cuanto la tenga lista, actualizo la versión anterior de Mantenimiento de Vehículo que tengo en proyectos completos para que la podáis ver.

Una cosa... y volviendo al mensaje anterior... si alguien conoce una fórmula más elegante para que aparezcan los registros iniciales en la consulta para mostrar kilometros realizados y Recorridos, me lo comenta, por que al final, tras haber sido adaptada a mi base de datos, la consulta (que funciona perfectamente), queda como el siguiente tocho :

Código: Seleccionar todo

SELECT 
	"V"."marca" || ' ' || "V"."modelo" || ' - ' || "V"."matricula" AS "Vehiculo",
	"FINAL"."idVehiculo",
	"FINAL"."Fecha",
	"FINAL"."Kilometros",
	"FINAL"."Litros",
	"FINAL"."Costo",
	"FINAL"."Precio",
	"FINAL"."Recorrido",
	"FINAL"."Llenado",
	"FINAL"."Lleno Anterior",
	"FINAL"."Consumo" 
	FROM
	(
SELECT
   "F"."idVehiculo",   
   "F"."fecha" AS "Fecha",
   "F"."kms" AS "Kilometros",
   "F"."litros" AS "Litros",
   "F"."costo" AS "Costo",
   "F"."costo"/"F"."litros"+' €/l' as "Precio",
   CAST (null as integer) AS "Recorrido",
   "F"."lleno" AS "Llenado",
   false AS "Lleno Anterior",
   '' AS "Consumo"
FROM
   "Repostajes" AS "F"
INNER JOIN 
	(
	SELECT 
		"T"."idVehiculo",
		MIN("T"."fecha") as "MINFECHA"
	FROM "Repostajes" as "T"
	GROUP BY "T"."idVehiculo") as "F2"
	ON "F"."idVehiculo"="F2"."idVehiculo" AND "F"."fecha"="F2"."MINFECHA"

UNION

SELECT   
   "A"."idVehiculo",   
   "A"."Fecha1" AS "Fecha",
   "A"."Kilometros",
   "A"."Litros",
   "A"."Costo",
   "A"."Costo"/"A"."Litros"+' €/l' as "Precio",
   "A"."Recorrido",
   "A"."Llenado",
   "A"."Lleno Anterior",
   CASEWHEN ("A"."Llenado" AND "A"."Lleno Anterior" AND "A"."Litros">0,100*"A"."Litros"/"A"."Recorrido"+' l/100km',null) AS "Consumo"
FROM
   (
    SELECT  	     
         "R"."idVehiculo",
         "R"."fecha" AS "Fecha1",
         "R"."kms" AS "Kilometros",
		 "R"."litros" AS "Litros",
	     "R"."costo" AS "Costo",
		 "R"."lleno" AS "Llenado",
         "R"."kms" - "R2"."kms" AS "Recorrido",
		 "R2"."lleno" AS "Lleno Anterior",		 
         DATEDIFF('dd', "R2"."fecha","R"."fecha") AS "Dif"
    FROM
        "Repostajes" AS "R" LEFT JOIN "Repostajes" AS "R2" ON "R"."fecha" > "R2"."fecha" AND "R"."idVehiculo"="R2"."idVehiculo"
   ) AS "A"

INNER JOIN
   (   
   SELECT
        "R"."idVehiculo",
        "R"."fecha" ,
        min(DATEDIFF('dd', "R2"."fecha","R"."fecha")) AS "MinDif"
   FROM
        "Repostajes" AS "R" LEFT JOIN "Repostajes" AS "R2" ON "R"."fecha" > "R2"."fecha" AND "R"."idVehiculo"="R2"."idVehiculo"
   GROUP BY "R"."idVehiculo","R"."fecha"
   ) AS "B"

ON "A"."Fecha1" = "B"."fecha" AND "A"."idVehiculo"="B"."idVehiculo"

WHERE "A"."Dif" = "B"."MinDif"
	) AS "FINAL"

LEFT JOIN "vehiculos" AS "V" ON "FINAL"."idVehiculo" = "V"."id"

WHERE "V"."deBaja" = FALSE
ORDER BY "FINAL"."idVehiculo" ASC,"FINAL"."Fecha" ASC

Un Saludo

[mauricio]

Una opción es agregar el campo respectivo a la base datos, y cada vez que agregues un nuevo registro, se calcula el valor que queda ya guardado solo para consulta...

Saludos

[cisko]

esta es la forma como yo lo hice
se trata de numerar dos consultas por vehiculo y luego juntar el renglon 1 con el 2 y hacer la resta de km

y tambien estoy buscando una forma mas sencilla

Código: Seleccionar todo

declare @tabla table (
  vehiculo    varchar(10),
  fecha       datetime,
  KmAlSurtir  int,
  GasSurtida  float
)  
             
               
select t2.renglon, t2.fecha, t2.kmAlSurtir  , t2.kmAlSurtir -t1.kmAlSurtir as kmsRecorridos  
from      (select ROW_NUMBER() OVER (PARTITION BY vehiculo ORDER BY fecha ) AS Renglon ,* from @tabla) as t2
left join (select ROW_NUMBER() OVER (PARTITION BY vehiculo ORDER BY fecha ) AS Renglon ,* from @tabla) as t1 on t2.vehiculo=t1.vehiculo and t2.renglon=t1.renglon+1