We recently came across an issue where register reads may go backwards due to a quirk in our AMI system. I was tasked with mining the EnergyIP data to find these. While not perfect, this script does the job. Below script will only look 15 days in the past. You may want to go back further.
SELECT *
FROM ( SELECT meter.udc_id,
rr.channel_id,
rr.insert_time,
rr.local_read_time,
rr.cum_read register,
/* lag function return the previous record value in a sorted and/or grouped list */
LAG (
cum_read,
1,
0)
OVER (
/* group by meter AND channel so we don't evaluate the previous record */
/* if the meter was changed out */
PARTITION BY meter.udc_id, rr.channel_id
ORDER BY meter.udc_id, rr.channel_id, rr.local_read_Time)
AS prior_register
FROM emapp.register_reads rr,
emapp.lookup_mv_meters meter,
emapp.lookup_mv_channels1 channel
WHERE channel.channel_type = 'Cumulative Consumption'
AND channel.logical_channel_no = 1
AND channel.channel_id = rr.channel_id
AND rr.utc_read_time > SYSDATE - 15
AND channel.meter_id = meter.meter_id
ORDER BY meter.udc_id, rr.channel_id, rr.utc_read_time ASC)
WHERE /* skip the first interval */
prior_Register > 0
/* register appeared to go backwards */
AND register < prior_register
/* omit cases that are likely due to differences in granularity between actual registers */
/* and End of Interval snapshots due to decimals and rounding */
AND prior_register - register > 1;
