-- Missed Reads
select 
WorkSet.WorkSetID as RouteNo,
Meter.UtilityMeterSequenceNumber as SequenceNo,
substring(AccountNumber,1, 15) as AccountNo,
Meter.MeterNumber,
FullStreetAddress,
FullName,
Meter.MeterCategory,
case  Meter.MeterCategory
when 0 then 'Electric'
when 4 then 'Water'
when 1 then 'Gas'
end as 'MeterType',
case  MeterSessionInput.ReadMethod
when 0 then 'Keyed'
when 3 then 'RF'
end as 'ReadMethod',
MeterSessionInputRF.EndpointID as EndpointID, 
MeterSessionInput.PreviousRead,
MeterSessionInput.NumberDials as NumberDials,
MeterSessionInput.NumberDecimals as NumberDecimals,
MeterSessionInput.ReadTypeCode as ReadTypeCode,
GPSInformation.Latitude as Latitude,
GPSInformation.Longitude as Longitude
from MeterSessionInput
left join Meter on MeterSessionInput.MeterKey = Meter.MeterKey
left join Customer on Meter.CustomerKey = Customer.CustomerKey
left join MeterSessionInputRF on MeterSessionInput.MeterSessionInputKey  = MeterSessionInputRF.MeterSessionInputKey 
left join WorkSet on Customer.WorkSetKey = WorkSet.WorkSetKey
left join GPSInformation on Meter.MeterKey = GPSInformation.MeterKey
where MeterSessionInput.MeterSessionInputKey
not in
(select MetersessionInputKey from MeterSessionOutput where ReadCode <> 0)
order by
WorkSet.WorkSetID,
Meter.UtilityMeterSequenceNumber


-- Work Set

select * from WorkSet;

-- Readings
select "Read", Read2, ReadFormatted, Read2Formatted, ReadCode, ReadCondition from MeterSessionOutput where ReadCode <> 0

select 
WorkSet.WorkSetID as RouteNo,
Meter.UtilityMeterSequenceNumber as SequenceNo,
substring(AccountNumber,1, 15) as AccountNo,
Meter.MeterNumber,
FullStreetAddress,
FullName,
Meter.UtilityMeterSequenceNumber,
Meter.MeterCategory,
case  Meter.MeterCategory
when 0 then 'Electric'
when 4 then 'Water'
when 1 then 'Gas'
end as 'MeterType',
case  MeterSessionInput.ReadMethod
when 0 then 'Keyed'
when 3 then 'RF'
end as 'ReadMethod',
MeterSessionInputRF.EndpointID as EndpointID, 
MeterSessionInput.PreviousRead,
case MeterSessionOutput.ReadCode
when 0 then 'Missed'
else 'Processed'
end as ReadStatus,
MeterSessionOutput."Read",
MeterSessionOutput.ReadDateTime,
MeterSessionInput.ReadTypeCode,
MeterSessionOutputRF.RawRead, 
MeterSessionOutputRF.TamperCount1,
MeterSessionInput.NumberDials as NumberDials,
MeterSessionInput.NumberDecimals as NumberDecimals,
MeterSessionInput.ReadTypeCode as ReadTypeCode,
GPSInformation.Latitude as Latitude,
GPSInformation.Longitude as Longitude
from MeterSessionInput
left join Meter on MeterSessionInput.MeterKey = Meter.MeterKey
left join Customer on Meter.CustomerKey = Customer.CustomerKey
left join MeterSessionInputRF on MeterSessionInput.MeterSessionInputKey  = MeterSessionInputRF.MeterSessionInputKey
left join MeterSessionOutput on MeterSessionOutput.MeterSessionInputKey = MeterSessionInput.MeterSessionInputKey
left join MeterSessionOutputRF on MeterSessionOutputRF.MeterSessionOutputKey = MeterSessionOutput.MeterSessionOutputKey 
left join WorkSet on Customer.WorkSetKey = WorkSet.WorkSetKey
left join GPSInformation on Meter.MeterKey = GPSInformation.MeterKey
order by
WorkSet.WorkSetID,
Meter.UtilityMeterSequenceNumber

