SELECT to get two entries, from same table, differentiated by date, in one row

Question

I have a table in which i keep different meters (water meter, electricity meter) and in another table i keep the readings for each meter. The table structure is like this : The meter table

MeterID | MeterType | MeterName

The readings Table:

ReadingID | MeterID | Index | DateOfReading

The readings for a meter are read monthly. The thing I am trying to do now is to get the Meter information, the current reading and the previous reading in just one row. So if i would have a query, the following row would result:

MeterID | MeterType | MeterName | CurrnetIndex | LastIndex

I have the following query so far :

SELECT Meter.MeterID, Meter.MeterType, Meter.MeterName, CurrentReading.Index, PreviousReading.Index
FROM Meters AS Meter
LEFT OUTER JOIN Readings AS CurrentReading ON Meter.MeterID = CurrentReading.MeterID
LEFT OUTER JOIN Readings AS PreviousReading ON Meter.MeterID = PreviouseReading.MeterID
WHERE CurrentReading.ReadingID != PreviousReading.ReadingID AND DIMESTAMPDIFF(MONTH, CurrentReading.DateOfReading, PreviousReding.DateOfReading)=-1

The problem is that I may not have the current reading or the previous, or both, but I would still need to have the meter information retrieved. It is perfectly acceptable for me to get NULL columns, but i still need a row :)

Answer 1

well, sql philosophy is to store what you know. if you don't know it, then there isn't any row for it. if you do a filter on the record set that you search for, and find nothing, then there isn't any month reading for it. Or that i didnt understand the question

Answer 2

You could use a subquery to grab the value from a month ago:

select  *
,       (
        select  Index
        from    Readings r2
        where   r2.MeterID = m.MeterID
                and DIMESTAMPDIFF(MONTH, r1.DateOfReading, 
                                  r2.DateOfReading) = -1
        ) as LastIndex
from    Meter m
left join
        Readings r1
on      r1.MeterID = m.MeterID

Another solution is to allow the second left join to fail. You can do that by just changing your where clause to:

WHERE  PreviousReading.ReadingID is null
       or 
       (
           CurrentReading.ReadingID != PreviousReading.ReadingID 
           and 
           DIMESTAMPDIFF(MONTH, CurrentReading.DateOfReading,
                         PreviousReding.DateOfReading) = -1
       )

Answer 3

Use:

   SELECT m.meterid,
          m.metertype,
          m.metername,
          current.index,
          previous.index
     FROM METER m
LEFT JOIN READING current ON current.meterid = m.meterid
                         AND MONTH(current.dateofreading) = MONTH(NOW())
LEFT JOIN READING previous ON previous.meterid = m.meterid
                          AND MONTH(current.dateofreading) = MONTH(NOW())-1

Being an OUTER JOIN - if the MONTH filtration is done in the WHERE clause, it can produce different results than being done in the ON clause.