Page 1 of 1

Calculated field

Posted: 2020-01-09 00:48
by petrescucld
Hi,
I have :
cars.id,
card.registration

diesel.id
diesel.car(lookup to cars.id)
diesel.date
diesel.km
difference - calculated field.

How can i calculate difference if i want it to show the difference between the last 2 inputs for the same car?
Basically when a user adds diesel to car he inputs the km number every time and i want to calculate the difference between the last 2 inputs automatically.

Thank you!

Re: Calculated field

Posted: 2020-01-09 21:44
by pbottcher
Hi,

you could try:

Code: Select all

select `diesel`.`km` - IFNULL((SELECT `diesel2`.`km` FROM `diesel` `diesel1` left join `diesel` as `diesel2` on `diesel2`.`car` = `diesel1`.`car` where `diesel1`.`id`=%ID% and `diesel2`.`id`<>%ID% order by `diesel2`.`date` DESC limit 1 ),0) from `diesel` where `diesel`.`id`=%ID%

Re: Calculated field

Posted: 2020-01-10 16:01
by petrescucld
Thanks for the help!

This worked:

Code: Select all

SELECT diesel.km - IFNULL((SELECT diesel.km FROM diesel WHERE diesel.car=(select diesel.car from diesel where diesel.id = %ID%) AND diesel.id < %ID% order by diesel.data DESC limit 1),0) from diesel where diesel.id=%ID%

Re: Calculated field

Posted: 2020-01-10 20:19
by pbottcher
Great, this assumes that you enter the data in a direct time/date dependent order which helps.