Home / Questions / 249679
Accepted
Yoel Rodriguez
Asked: 2020-03-29 09:11:19 +0800 CST 2020-03-29 09:11:19 +0800 CST

How to get the previous record of the same date without using the Id

  • 772

I have the following situation: I have a table, where I keep the daily records of a water meter. While I was taking a daily reading and capturing it in the system, I had no problem since it reflected my daily consumption well. The problem occurs to me when I do more than one reading per day since they have the same registration date. When I make two captures of the meter reading, on the same day at different times, instead of obtaining the previous record (1), I go to the one with the previous date (2) . and needing the record of the example (1) to be able to calculate consumption during the day.

Ex:

1 -The previous record of the date of 03-26-2019:

2019-03-26 06:53:37 2019-03-26 339304.00 Mar 2.02

2 -The record that the SQL query returns:

2019-03-25 09:09:57 2019-03-25 339102.00 Mon 5.95

The capture of the reading can be done all the same day, so I cannot use the id , since the system operator can load the readings in a disorderly way.

(Ex: first you can load me the 2019-03-26 and then load the 2019-03-25.)

Then I leave the query and the screen where the error is appearing to see if you can help me.

Thanks.

Query to get the old record: 

SELECT medidor_dias.* FROM (`medidor_dias`) WHERE `fecha_medida` <  '2019-03-26'   AND `id_tipo_medidor` = '4'  AND `medidor_dias`.`fecha_medida` >= '2019-02-26' AND `medidor_dias`.`fecha_medida` <= '2019-03-28'  ORDER BY `id_tipo_medidor` ASC, `fecha_medida` DESC , medidor_dias.hora_ejecutado DESC LIMIT 1

Image of the Error on 03-26-2019:

In the image you can see that the day: 03-26-2019 was captured twice, so it shows two records. In the second reading of that day it is the total accumulated of the two readings, with respect to the day 03-25-2019. The value that should go in the consumption of the second reading is 2.47 and not 4.49 , which is what it has. The formula that I am using for the calculation is the following (Last record - Previous record = consumption)

enter image description here

mysql

1 Answers

  1. Best Answer

    Here I leave the solution I gave to my question, in case someone else needs it. In this solution I had to do a rearrangement of my table id . This can be done because this id is not part of any foreign key and I only use it to edit and delete the record, if required. Thanks everyone for your help.

    SET @a = 0;
ALTER TABLE medidor_dias DROP id;
ALTER TABLE medidor_dias ADD id int NOT NULL ;
UPDATE medidor_dias  SET id = @a:=@a+1    ORDER BY `fecha_medida` ASC , medidor_dias.hora_ejecutado ASC ;
ALTER TABLE `medidor_dias` ADD INDEX(`id`);
ALTER TABLE `medidor_dias` CHANGE `id` `id` INT(15) NOT NULL AUTO_INCREMENT PRIMARY KEY

    ;

    • 0