Overview
You want the Residential and/or Commercial Request for Service (enrollment) data with certain conditions such as that for a particular period of time (eg., for the April of last year), or for a particular geography(eg., for the state of PA).
You can see the enrollments on the CSR portal by navigating from the menu to Request for Service > Request for Service Search. However, this page doesn't allow you to download this information or filter based on state and time range.
Solution
To download the enrollment information based on region and date filters, open a support request specifying the customer type (Residential and/or Commercial) and any other conditions such as the time period for which the data is needed or any specific region selection.
You should also specify the columns that you need in the required data. Eg: Account #, Type, Customer Name, Date Enrolled, Status, Service Address.
<supportagent>
You should have access to the Jump server for performing the below steps:
- Login to the Jump server 10.185.40.141.
- Open Microsoft SSMS and connect to
aes.cons.com
. - Select
New Query
in the top bar. - Run the below query replacing the check on
CustomerTypeID
with the appropriate value for Residential(CustomerTypeID
=1) or Commercial(CustomerTypeID
=2). The values are also mentioned in theCustomerType
table. The first MM/DD/YYYY is the start date of the period that the customer has mentioned in the ticket and the second MM/DD/YYYY is the end date of that period:
use <Client specific Database>;
select distinct ec.enrollcustid, ecp.CustomerAccountNumber, ct2.Description [CustomerType], ec.CustName,ec.EnrollmentSentDate, ec.CreateDate,
es.Description [EnrollStatus], Concat (ecp.Addr1, ' ', ecp.Addr2, ' ', ecp.City, ' ', ecp.State, ', ', ecp.Zip) as 'Service Address'
from enrollcustomer ec
join EnrollCustomerPremise ECP on ecp.enrollcustid = ec.enrollcustid
Join customertype ct on ct.custtype = ec.custtype
Join customertype ct2 on ct2.customertypeid = ct.category -- select * from customertype
join enrollstatus es on ec.EnrollStatusID = es.EnrollStatusID
where 1=1
and ct.CustomerTypeID=<relevant CustomerTypeID>
and ec.CreateDate between 'MM/DD/YYYY' and 'MM/DD/YYYY'
Note: Adjustments to the above query can be needed on the fly as per the column requirements from the customer. Such as for selecting enrollments of one state, say PA, add to the where clause of the query to change it to this:
use <Client specific Database>;
select distinct ec.enrollcustid, ecp.CustomerAccountNumber, ct2.Description [CustomerType], ec.CustName,ec.EnrollmentSentDate, ec.CreateDate,
es.Description [EnrollStatus], Concat (ecp.Addr1, ' ', ecp.Addr2, ' ', ecp.City, ' ', ecp.State, ', ', ecp.Zip) as 'Service Address'
from enrollcustomer ec
join EnrollCustomerPremise ECP on ecp.enrollcustid = ec.enrollcustid
Join customertype ct on ct.custtype = ec.custtype
Join customertype ct2 on ct2.customertypeid = ct.category -- select * from customertype
join enrollstatus es on ec.EnrollStatusID = es.EnrollStatusID
where ecp.state = 'PA' -- state check mentioned here
and ct.CustomerTypeID=<relevant CustomerTypeID>
and ec.CreateDate between 'MM/DD/YYYY' and 'MM/DD/YYYY'
Copy the results of the above into an appropriate tool (eg: Google Sheets, Microsoft Excel) and send that to the customer.
</supportagent>