Skip to main content
Skip table of contents

SQL - Find Invalid asset_meters Records

Description

SQL used to remove asset_meters records that are no longer valid

Usage

When a meter is end dated, it can have existing asset_meters records for a timeslice outside the new effective dates of the meter.

This script will remove any asset_meters records that are outside the effective date of the meter associated to it.

SQL

CODE
SELECT DISTINCT
	am.id,
	a.number,
	m2.number,
	m2.from_date,
	m2.to_date,
	am.from_date,
	am.to_date
FROM
	asset_meters am
	LEFT JOIN meters m ON m.anchor_id = am.meter_anchor_id
		AND m.to_date >= am.to_date
	LEFT JOIN meters m2 ON m2.anchor_id = am.meter_anchor_id
	JOIN assets a ON a.id = am.asset_id
WHERE
	m.id IS NULL
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.