I have 2 tables, one containing meter IDs, and another containing measurements for some of the meters in the first table. This is the table structure:
- MeterID (int)
- MeterNumber (char16)
- Type (char25)
- MeterID (int)
- Date (datetime)
- Value (numeric(18,6))
I need to get the last reading (and its date) from a given period for each meter, as well as the meter number. I managed to do this in T-SQL, although I’m not particularly pleased with the way I did it using this query:
select distinct cfg.MeterNumber, (select top 1 r.Date from Readings as r where r.Date between @startdate and @endDate and r.MeterID = cfg.MeterID order by r.Date desc) as Date, (select top 1 r.Value from Readings as r where r.Date between @startdate and @endDate and r.MeterID = cfg.MeterID order by r.Date desc) as Value from MeterConfig cfg, Readings r1 where cfg.MeterID = r1.MeterID and r1.Date between @startdate and @endDate;
How can I do this more efficiently?
WITH CTE AS ( SELECT mc.MeterID, Date, Value, ROW_NUMBER() OVER (PARTITION BY mc.MeterID ORDER BY Date DESC) as Rank FROM MeterConfig mc INNER JOIN Readings rd ON mc.MeterID = rd.MeterID WHERE rd.Date BETWEEN @startdate AND @endDate) SELECT * FROM CTE WHERE Rank = 1
Assuming the dates in Readings are unique (ic include a timestamp), following should be equivalent to your query.
SELECT DISTINCT cfg.MeterNumber , r1.Date , r1.Value FROM MeterConfig cfg INNER JOIN Readings r1 ON cfg.MeterID = r1.MeterID INNER JOIN ( SELECT date = MAX(r.Date) FROM Readings r WHERE r.Date BETWEEN @StartDate AND @EndDate ) r2 On r2.date = r1.date