Originally Posted by geebra
...between may 2012 and may 2013.
my bad, i read those two dates as being the same
Code:
SELECT dbo.meterDefineDPView.meterName
, maxes.[Max Pressure]
, dbo.dailyMeterReading.contractTime
FROM dbo.meterDefineDPView.meterName
INNER
JOIN ( SELECT meterId
, MAX(pressure) AS [Max Pressure]
FROM dbo.dailyMeterReading
WHERE dataQuality IN ('10','11')
AND contractTime
BETWEEN CONVERT(DATETIME, '2012-May-14', 102)
AND CONVERT(DATETIME, '2013-May-14', 102))
GROUP
BY meterId ) AS maxes
ON maxes.meterId = dbo.meterDefineDPView.meterId
INNER
JOIN dbo.dailyMeterReading
ON dbo.dailyMeterReading.meterId = dbo.meterDefineDPView.meterId
AND dbo.dailyMeterReading.dataQuality IN ('10','11')
AND dbo.dailyMeterReading.pressure = maxes.[Max Pressure]
WHERE dbo.meterDefineDPView.meterName IN
('001640', '008776', '002536', '008603', '002526', '005071'
,'006240', '008620', '006305', '004720', '005064', '002703')