comparazione con data precedente

[max1974]

Salve a tutti mi aiutate per favore con Mysql
sto selezionando i rifornimenti fatti con un automezzo id_auto n° 1:

SELECT id,REPLACE(DATE_FORMAT(dt,GET_FORMAT(DATE,'EUR')),'.','/') as gg, km, lt, prezzo FROM tb_auto_rifornimenti
                WHERE tb_auto_rifornimenti.id_auto='1'";

adesso vorrei sapere in riferimento al rifornimento precedente quanti km sono stati percorsi
es:

Data	KM	Litri	Euro
03/01/2020	9520	25.39	48.89
11/01/2020	9968	61.23	77.85

quando inserisco il rifornimento del 11/01/2020 vorrei sapere che ha percorso 448 km dato dai km attuali meno i km del rifornimento precedente (9968-9520) e sapere il consumo medio dato dai km Percorsi diviso i litri di rifornimento.

volevo farlo con la query..... e stavo provando cosi, ma mi da errore, credo che il problema sia se non esiste il record precedente...

SELECT qr.*, (qr.km-qrp.km) as KM_Percorsi FROM(
SELECT id,REPLACE(DATE_FORMAT(dt,GET_FORMAT(DATE,'EUR')),'.','/') as gg,km,lt,prezzo
FROM tb_auto_rifornimenti
WHERE tb_auto_rifornimenti.id_auto='1'
ORDER BY dt)qr
UNION
SELECT qrp.* FROM(
SELECT id,REPLACE(DATE_FORMAT(dt,GET_FORMAT(DATE,'EUR')),'.','/') as gg,km,lt,prezzo
FROM tb_auto_rifornimenti
WHERE tb_auto_rifornimenti.id_auto='1'
ORDER BY dt DESC LIMIT 1)qrp

 

[marino51]

non so se ho capito, in ogni caso ti posto una query e il suo risultato,
é una bozza ma contiene tutto quello che serve e puoi adattarla come vuoi

SELECT
  t1.ID
, t1.Data
, t1.KM
, t1.Litri
, t1.Euro
, max(t2.Data)  as Pdata
, max(t2.KM)    as Pkm
, sum(t2.Litri) as Plitri
, sum(t2.Euro)  as Peuro
, t1.KM - max(t2.KM) as KMpercorsi
FROM tb_auto_rifornimenti t1
LEFT JOIN tb_auto_rifornimenti t2 ON t1.ID = t2.ID
AND t2.Data < t1.Data
WHERE t1.ID = 1
GROUP BY
  t1.ID
, t1.Data
, t1.KM
, t1.Litri
, t1.Euro
ORDER BY
  t1.ID
, t1.Data

RISULTATO

ps, tolto le chiocciole che avevo lasciato davanti ai nomi della tabella

 

[max1974]

non so se ho capito, in ogni caso ti posto una query e il suo risultato,
é una bozza ma contiene tutto quello che serve e puoi adattarla come vuoi

SELECT
  t1.ID
, t1.Data
, t1.KM
, t1.Litri
, t1.Euro
, max(t2.Data)  as Pdata
, max(t2.KM)    as Pkm
, sum(t2.Litri) as Plitri
, sum(t2.Euro)  as Peuro
, t1.KM - max(t2.KM) as KMpercorsi
FROM tb_auto_rifornimenti t1
LEFT JOIN tb_auto_rifornimenti t2 ON t1.ID = t2.ID
AND t2.Data < t1.Data
WHERE t1.ID = 1
GROUP BY
  t1.ID
, t1.Data
, t1.KM
, t1.Litri
, t1.Euro
ORDER BY
  t1.ID
, t1.Data

RISULTATO

Vedi l'allegato 6917

ps, tolto le chiocciole che avevo lasciato davanti ai nomi della tabella

Grazie 1000 Marino51 ho modificato la tua query con i miei campi etc , ma credo di aver fatto qualche errore o ci sia qualche problema
Ecco come l'ho modificata

SELECT t1.id,
    REPLACE(DATE_FORMAT(t1.dt,GET_FORMAT(DATE,'EUR')),'.','/') as gg,
    t1.km,
    t1.lt,
    t1.prezzo,
    REPLACE(DATE_FORMAT(t2.dt,GET_FORMAT(DATE,'EUR')),'.','/') as Pgg,
    max(t2.km) as Pkm,
    sum(t2.lt) as Plitri,
    sum(t2.prezzo) as Pprezzo,
    (t1.km-max(t2.km)) as KMpercorsi
    FROM tb_auto_rifornimenti t1
    LEFT JOIN tb_auto_rifornimenti t2 ON t1.id = t2.id AND t2.dt < t1.dt
    WHERE t1.id_auto = 1
    GROUP BY t1.id, t1.dt, t1.km, t1.lt, t1.prezzo
    ORDER BY t1.id, t1.dt

ecco il risultato.....

ti mando intera tabella per controllare...

CREATE TABLE `tb_auto_rifornimenti` (
  `id` int(11) NOT NULL,
  `dt` date NOT NULL,
  `km` int(11) DEFAULT NULL,
  `lt` decimal(10,2) DEFAULT '0.00',
  `prezzo` decimal(10,2) DEFAULT '0.00',
  `id_auto` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `tb_auto_rifornimenti` (`id`, `dt`, `km`, `lt`, `prezzo`, `id_auto`) VALUES
(0, '2020-01-22', 10330, '41.23', '57.85', 1),
(1, '2020-01-03', 9520, '25.39', '48.89', 1),
(2, '2020-01-11', 9968, '61.23', '77.85', 1);

credo anche quel ORDER BY t1.id non sia corretto, in quanto potrei inserire un record successivo o precedente indipendentemente.
GRAZIE COME SEMPRE TROPPO GENTILE!!!

 

[max1974]

aspe forse il problema è nella join

SELECT t1.id,
REPLACE(DATE_FORMAT(t1.dt,GET_FORMAT(DATE,'EUR')),'.','/') as gg,
t1.km,
t1.lt,
t1.prezzo,
REPLACE(DATE_FORMAT(t2.dt,GET_FORMAT(DATE,'EUR')),'.','/') as Pgg,
max(t2.km) as Pkm,
sum(t2.lt) as Plitri,
sum(t2.prezzo) as Pprezzo,
(t1.km-max(t2.km)) as KMpercorsi
FROM tb_auto_rifornimenti t1
LEFT JOIN tb_auto_rifornimenti t2 ON t1.id_auto = t2.id_auto AND t2.dt < t1.dt
WHERE t1.id_auto = 1
GROUP BY t1.id, t1.dt, t1.km, t1.lt, t1.prezzo
ORDER BY t1.dt

 

[Max 1]

@max1974

Quando posti codice PHP devi usare i tag [PHP]........[/PHP]non il tag [CODE].......[/CODE]

 

[max1974]

@max1974

Quando posti codice PHP devi usare i tag [PHP]........[/PHP]non il tag [CODE].......[/CODE]

Ok scusa

 

[marino51]

si il problema é nella join, l'ID é da intendersi IDauto non l'ID del record,

LEFT JOIN tb_auto_rifornimenti t2 ON t1.id_auto = t2.id_auto AND t2.dt < t1.dt

credo anche quel ORDER BY t1.id non sia corretto

anche in questo caso andrebbe ordinato per IDauto e data nel caso tu selezionassi più auto contemporaneamente

in quanto potrei inserire un record successivo o precedente

ma tu stai trattando 1 record anche se composto (JOIN) con record diversi, devi sempre fare riferimento a lui, sia che leghi valori precedenti e/o successivi

 

[max1974]

Si Marino la tua osservazione è giustissima ma dato il caso che ho una

WHERE t1.id_auto = 1

Significa che seleziono un auto alla volta ed in questo caso i vari

GROUP BY t1.id_auto
ORDER BY t1.id_auto

secondo me nn hanno scopo di esistere...
Forse sto prendendo un abbaglio!!!!

 

[marino51]

secondo me nn hanno scopo di esistere...
GROUP BY t1.id_auto

tutte le volte che ci sono funzioni max. sum e simili, il group by é necessario per stabilire il livello a cui raggruppare quei valori,
altri motori di database, esempio ms sql server, oracle, vogliono il set completo dei valori non raggruppati
mysql é più lasco in questo senso, io preferisco sempre indicarli

ORDER BY t1.id_auto
non costa molto e la query é già pronta per altri impieghi (vedi ad esempio una lista mensile di controllo)

ORDER BY t1.id_auto, data
potrebbe essere necessario perché non é garantito che il db restituisca in ordine di data …..

ma poi vale il buon senso