Overview

You inquire if there are active accounts with a rate code having the prefix PREM (PREM_754404).

Solution

Accounts show the rate code instead of a product when there is no active product on the account.

Please create a support ticket and we will share a list of such active accounts that do not have active products on the account thereby showing the rate code.

<supportagent>

Steps

1. To fetch active accounts that do not have active products on the account thus showing the rate code, it is required to get the customer accounts that do not have any valid Active RateTransition entry. By valid, it implies that the SwitchDate is less than the current date and EndDate is greater than the current date.
Run the select query below to get the accounts:

select c.custNo [Account Number], c.custID,
c.custName, p.premID, p.premNo [Utility Account Number],
ps.status [premise status],
pr.productcode,
rt.ratetransitionid, rt.switchdate, rt.enddate,
st.description [Account Status],
p.premtype [Commodity Type],
l.ldcshortname, r.ratecode
from customer c
join rate r on r.rateid = c.rateid
join premise p on p.custID = c.custID
join premiseStatus ps on p.statusID = ps.premisestatusid
join ratetransition rt  on rt.custid = c.custid
join product pr on pr.rateid = rt.rateid
join status st  on c.custstatus = st.statusid
join customerAdditionalInfo csai on csai.custid = c.custid
join ldc l on p.ldcid = l.ldcid
Where c.custID not in
(select custID
from RateTransition
where switchDate < getdate() and enddate > getdate())
and ratecode like 'PREM%'
order by switchdate desc

2. Share the query result with the client.

Note: If the client requests for all accounts that show a rate code with a prefix (not specifically PREM), you can run the query in step 1 without the filter for ratecode. In this case, you can expect to get accounts with ratecode having a prefix like PREM, CUST, ServiceTran, etc.

</supportagent>