Knowledgebase: EC Enterprise
EC Enterprise: Script for Updating Meter Begin and End Dates
Posted by Barry Kroeker, Last modified by Joel Brickell on 28 May 2015 03:07 PM

In some unusual circumstances, it may be necessary to assign or re-assign begin and end dates to all meters in EnergyCAP.

The following script looks at the billing history of a meter in order to set the begin and end (if necessary) dates of the meter. The begin date of the first bill on the meter becomes the begin date of the meter, and the end date of the last bill on the meter becomes the end date of the mater IF that date falls outside a user-defined threshold. You can set this threshold for 6 months, 1 year, or more/less. The script will look to see if a bill has been entered on the meter within that time window, and if so, then it will determine the meter to still be active and leave the end date to be 1/1/3000. otherwise, it will determine the meter to be inactive and set an end date.

Request EnergyCAP assistance before running the script. Always back up your EnergyCAP database prior to running scripts against it.

The script to accomplish this is as follows (see internal script notes for more details):

____________________________________________

/*
This query is intended to update the meter begin and end dates based
on when the meter was first and last read using the billing history as a guide.

I am making the assumption that a meter is inactive if it has not received a bill in the last x days
'

DECLARE @meter_begin_date datetime, @meter_end_date datetime, @bill_begin_date datetime, @bill_end_date datetime, @logicaldeviceid int, @test int, @min_ld_date datetime, @max_ld_date datetime, @threshold int

SET @threshold = 365 -- This defines how far back in days we look for bills on a meter before we assume the meter is inactive

DECLARE meter_list
CURSOR SCROLL STATIC FOR
SELECT logicaldeviceid
FROM dbo.LogicalDevice ld
WHERE logicalDeviceTypeID = 1 -- ensures that we only update meters and not weather stations or counters

open meter_list

fetch next from meter_list
into @logicaldeviceid

while @@fetch_status = 0
begin

-- Test to see if meter has any bills. If a meter does not have bills, then we can't do anything
set @test = (select count(*)
from billaccountmeter
JOIN meter on billaccountmeter.meterid = meter.meterid
where
meter.logicaldeviceid = @logicaldeviceid)

if (@test > 0)
begin
-- Find the oldest bill for the current meter to update the meter begin date
set @bill_begin_date = (select min(beginDate)
from billaccountmeter bam
JOIN meter on bam.meterid = meter.meterid
where
meter.logicaldeviceid = @logicaldeviceid)

-- Get the oldest devicelogicaldevice record since a meter can have multiple ones
set @min_ld_date = (select min(begindate) from dbo.DeviceLogicalDevice d where logicalDeviceID = @logicaldeviceid)

-- Update the begin date of the meter with the begin date of the earliest bill
update devicelogicaldevice
set begindate = @bill_begin_date
where logicalDeviceID = @logicaldeviceid
and begindate = @min_ld_date

set @test = (select count(*)
from billaccountmeter bam
JOIN meter on bam.meterid = meter.meterid
where meter.logicaldeviceid = @logicaldeviceid
and bam.endDate >= (getdate() - @threshold)) -- This condition checks to see there were any bills in the last days - defined by the variable @threshold - to ensure the meter is not active


if (@test = 0) -- Does the end date of the most recent bill on the current meter fall within the time period defined in @threshold
begin

-- Get the most recent devicelogicaldevice record since a meter can have multiple ones
set @max_ld_date = (select max(enddate) from dbo.DeviceLogicalDevice d where logicalDeviceID = @logicaldeviceid)

-- Find the most recent bill for the meter to update the meter end date
set @bill_end_date = (select max(endDate)
from billaccountmeter bam
JOIN meter on bam.meterid = meter.meterid
where
meter.logicaldeviceid = @logicaldeviceid)

update devicelogicaldevice
set enddate = @bill_end_date
where logicaldeviceid = @logicaldeviceid
and enddate = @max_ld_date

end -- second if end

end -- first if end

fetch next from meter_list
into @logicaldeviceid
end -- while loop end

close meter_list
deallocate meter_list

______________________________________

by Administrator  last modified 06/25/2007  EnergyCAP Rel. 5.0 and newer

(256 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments: