Overview

You want a breakdown of GA paperless customers with and without email addresses having the details Ista Account Number, UAN, Customer Name, Service Address, Email Address. You may also request the SQL script for this report.

Or, you may request Aurea to provide Active and Pending Enrollment GA paperless customers with the criteria: Customer account #, Customer name, Mailing address, Premise ID, AGL LDC, and Account Status.

Solution

Please raise a support ticket with us specifying the set of details (as mentioned above) you are looking for. We will share the report and the SQL script for this report (if requested).

Prerequisites:

Access to the Client database

Steps:

Depending on the requested criteria, run one of the select queries below to fetch the information.
a. For criteria Ista Account Number, UAN, Customer Name, Service Address, Email Address

Select CustNo, 
p.PremNo 'UtilityAccountNumber', 
c.CustName, DeliveryTypeDesc,PS.Status,
Concat (a.Addr1,' ',a.Addr2,' ',a.City,' ',a.Zip,' ',a.State) as 'Mailing Address', 
a.Email,
Concat (a2.Addr1,' ',a2.Addr2,' ',a2.City,' ',a2.Zip,' ',a2.State) as 'Service Address'
From Customer C
Join Premise P on P.custid=C.Custid
Join DeliveryType DT on DT.DeliveryTypeID=C.DeliveryTypeID
Join PremiseStatus PS on PS.PremiseStatusID=P.StatusID
Join Address A on A.AddrID=C.MailAddrID
Join Address A2 on A2.AddrID=p.AddrID
Where 1=1
and P.StatusID in (10,1,5,6)
and C.DeliveryTypeID<>1
and P.TDSP=74
and PremID = (Select Max(PremID) From Premise where CustID=P.CustID)

Screenshot_2021-09-26_at_3.25.17_PM.png

b. For criteria: Customer account #, Customer name, Mailing address, Premise ID, AGL LDC, and Account Status
The client is referring to the Utility Account Number by Premise ID.
The filter P.StatusID filters the customers with premises having status Active (10) and Enrollment Pending (0).

Select c.CustNo 'Customer Account Number',
c.CustName,DeliveryTypeDesc,
Concat (a.Addr1,' ',a.Addr2,' ',a.City,' ',a.Zip,' ',a.State) 
as 'Mailing Address', 
a.Addr1,a.Addr2,a.City,a.Zip,a.State,
p.PremNo 'UtilityAccountNumber', 
l.ldcshortname 'AGL LDC',
PS.Status
From Customer C
Join Premise P on P.custid=C.Custid
join LDC l on l.ldcid = p.ldcid
Join DeliveryType DT on DT.DeliveryTypeID=C.DeliveryTypeID
Join PremiseStatus PS on PS.PremiseStatusID=P.StatusID
Join Address A on A.AddrID=C.MailAddrID
Join Address A2 on A2.AddrID=p.AddrID
Where 1=1
and P.StatusID in (10,0)
and C.DeliveryTypeID<>1
and P.TDSP=74
and l.marketid=17
and PremID = (Select Max(PremID) From Premise where CustID=P.CustID)

2. Share the query result with the client.
If the client requests the SQL script, share the query from the previous step.

</supportagent>