CODEWITHSUNDEEP
mysqlsql
user2725105
user2725105

Reputation: 689

SQL Storing value, daily readings

Is there a possibility to store a value somehow and then if needed use it. Maybe you have any other solution to the following problem. I have hourly water readings and I want to present it as a day consumption but sometimes there is a possibility that some readings will be missing.

SELECT Readings.dte AS day,
       Ifnull(NextReadings.reading, (SELECT Max(consumption)
                                     FROM   meter_readings)) - Readings.reading AS Consumption
FROM   (SELECT Date(readdate)   AS dte,
               Min(consumption) AS Reading
        FROM   meter_readings
        GROUP  BY Date(readdate)) AS Readings
     LEFT JOIN 
       (SELECT Date(readdate)   AS dte,
               Min(consumption) AS Reading
        FROM   meter_readings
        GROUP  BY Date(readdate)) AS NextReadings
     ON NextReadings.dte = date_add(Readings.dte, INTERVAL +1 DAY)

And if there is

...
insert into meter_readings values ('2013-07-30 00:00:31',    143.860);
insert into meter_readings values ('2013-07-30 01:00:32',    143.870);
insert into meter_readings values ('2013-07-30 02:00:31',    143.870);
insert into meter_readings values ('2013-07-30 03:00:32',    143.870);
insert into meter_readings values ('2013-07-30 04:00:31',    143.870);

insert into meter_readings values ('2013-07-31 02:00:31',    143.890);

insert into meter_readings values ('2013-08-03 00:00:31',    143.900);
insert into meter_readings values ('2013-08-03 01:00:32',    143.920);
insert into meter_readings values ('2013-08-03 02:00:31',    143.920);

http://sqlfiddle.com/#!2/dd66cd/2 <--e.g

And I as you can see :

July, 30 2013   0.03
July, 31 2013   0.03 
August, 03 2013 0.02

the usage for 2013-07-31 is 0.03 and that is not true because of SELECT Max(consumption) FROM meter_readings) . It would be nice to store somehow the last correct value (143.870) and then use instead of this Max(consumption). And usage for 2013-07-31 should be: 143.890 - Variable where Variable is 143.870

Upvotes: 3

Views: 211

Answers (2)

user2725105
user2725105

Reputation: 689

Based on Wietze314's code I've found the solution. It works, but it is pretty heavy

SET @curRank1 = 0;
SET @curRank2 = 0;

SELECT
D1.ReadDate
,D1.Consumption AS D1Consumption
,D2.Consumption AS D2Consumption
,(D2.Consumption - D1.Consumption) AS UsageOfDay
FROM
(
  SELECT M1.*, @curRank1 := @curRank1 + 1 AS rank

FROM meter_readings M1
LEFT JOIN meter_readings M2
ON Date(M1.ReadDate) = Date(M2.ReadDate)
AND M1.ReadDate > M2.ReadDate
WHERE M2.ReadDate IS NULL
  ) D2
LEFT JOIN
(
  SELECT M1.*, @curRank2 := @curRank2 + 1 AS rank

FROM meter_readings M1
LEFT JOIN meter_readings M2
ON Date(M1.ReadDate) = Date(M2.ReadDate)
AND M1.ReadDate > M2.ReadDate
WHERE M2.ReadDate IS NULL
  ) D1
ON D2.rank = (D1.rank + 1)
WHERE D1.ReadDate IS NOT NULL
UNION ALL
(
SELECT DISTINCT
 Date(My.ReadDate),
  MIN(My.Consumption) AS D1Consumption,
  MAX(My.Consumption)AS D2Consumption,
  (MAX(My.Consumption) - MIN(My.Consumption)) AS UsageOfDay
  FROM
  meter_readings MY
GROUP  BY Date(My.ReadDate) DESC
LIMIT 1
  )

Upvotes: 0

Wietze314
Wietze314

Reputation: 6020

I think you want to have the last reading of every day. Use this syntax to get it:

SELECT M1.*

FROM meter_readings M1
LEFT JOIN meter_readings M2
ON Date(M1.ReadDate) = Date(M2.ReadDate)
AND M1.ReadDate < M2.ReadDate
WHERE M2.ReadDate IS NULL

Or to get the first reading of every day:

SELECT M1.*

FROM meter_readings M1
LEFT JOIN meter_readings M2
ON Date(M1.ReadDate) = Date(M2.ReadDate)
AND M1.ReadDate > M2.ReadDate
WHERE M2.ReadDate IS NULL

And to get the usage of the day. I don't use MySql much. So maybe the rank function could be better designed.

SET @curRank1 = 0;
SET @curRank2 = 0;

SELECT
D1.ReadDate
,D1.Consumption AS D1Consumption
,D2.Consumption AS D2Consumption
,(D2.Consumption - D1.Consumption) AS UsageOfDay
FROM
(
  SELECT M1.*, @curRank1 := @curRank1 + 1 AS rank

FROM meter_readings M1
LEFT JOIN meter_readings M2
ON Date(M1.ReadDate) = Date(M2.ReadDate)
AND M1.ReadDate < M2.ReadDate
WHERE M2.ReadDate IS NULL
  ) D2
LEFT JOIN
(
  SELECT M1.*, @curRank2 := @curRank2 + 1 AS rank

FROM meter_readings M1
LEFT JOIN meter_readings M2
ON Date(M1.ReadDate) = Date(M2.ReadDate)
AND M1.ReadDate < M2.ReadDate
WHERE M2.ReadDate IS NULL
  ) D1
ON D2.rank = (D1.rank + 1)

Upvotes: 1

Related Questions