
select 
WorkSet.WorkSetID as RouteNo,
Meter.UtilityMeterSequenceNumber as SequenceNo,
substring(AccountNumber,1, 10) 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',
MeterSessionInput.PreviousRead,
case MeterSessionOutput.ReadCode
when 0 then 'Missed'
else 'Processed'
end as ReadStatus,
MeterSessionOutput."Read",
MeterSessionOutput.ReadDateTime,
MeterSessionInput.ReadTypeCode,
MeterSessionInput.NumberDials as NumberDials,
MeterSessionInput.NumberDecimals as NumberDecimals,
MeterSessionInput.ReadTypeCode as ReadTypeCode,
MeterSessionOutput.TroubleCode1,
MeterSessionOutput.TroubleCode2,
MeterSessionOutput.TroubleCode3,
MeterSessionOutput.TroubleCode4,
MeterSessionOutput.TroubleCode5,
MeterSessionOutput.TroubleCode6,
MeterSessionOutput.TroubleMessage
from MeterSessionInput
left join Meter on MeterSessionInput.MeterKey = Meter.MeterKey
left join Customer on Meter.CustomerKey = Customer.CustomerKey
left join MeterSessionOutput on MeterSessionOutput.MeterSessionInputKey = MeterSessionInput.MeterSessionInputKey
left join WorkSet on Customer.WorkSetKey = WorkSet.WorkSetKey
where 
MeterSessionOutput.TroubleCode1 > '00' or
MeterSessionOutput.TroubleCode2 > '00' or
MeterSessionOutput.TroubleCode3 > '00' or
MeterSessionOutput.TroubleCode4 > '00' or
MeterSessionOutput.TroubleCode5 > '00' or
MeterSessionOutput.TroubleCode6 > '00' or
MeterSessionOutput.TroubleMessage > ''
order by
WorkSet.WorkSetID,
Meter.UtilityMeterSequenceNumber
