Overview

You may need the customer count and revenue earned per product for audit or accounting purposes and this article mentions the method to get that report.

Solution

Create a support ticket mentioning the date ranges to be considered for the report, and any markets that need to be excluded and support will share the report with you.

<supportagent>

1. Login to the Jump server 10.185.40.141.
2. Open Microsoft SSMS and connect to aes.cons.com.
3. Select New Query in the top bar.
4. If regions are provided to include or exclude, execute the following query to find the relevant LDCCodes

   select LDCCode, * from LDC
   join Market on LDC.MarketID = Market.MarketID
   where Market.MarketCode = '<client specified abbreviated state name, eg.,
   TX, NY, MD>'

5. Execute the following query, substituting information as needed, to export information about the revenue per product.

   select p.ProductCode,p.ProductID,Sum(id.InvDetAmt) as Revenue
   from Customer c
   join Invoice i on i.CustID = c.CustID
   join InvoiceDetail id on id.InvoiceID = i.InvoiceID and id.CategoryID=1
   join RateDetail rd on rd.RateDetID = id.RateDetID
   join RateTransition rt on rt.RateTransitionID = rd.RateTransitionID
   join Product p on p.RateID = rt.RateID
   where 1=1
   -- and p.LDCCode in (the only LDCCodes that need to be counted)
   -- and p.LDCCode not in (LDCCodes that need to be excluded)
   and i.InvDate between '<From date as MM/DD/YYYY>' and '<To date as MM/DD/YYYY>'
   group by p.ProductCode,p.ProductID
   order by 1

6. Execute the following query, substituting information as needed, to export information about the customer count per product

   select distinct c.CustID,rt.RateID,p.ProductCode,p.ProductID
   into #tmpc
   from Customer c
   join Invoice i on i.CustID = c.CustID
   join InvoiceDetail id on id.InvoiceID = i.InvoiceID
   join RateDetail rd on rd.RateDetID = id.RateDetID
   join RateTransition rt on rt.RateTransitionID = rd.RateTransitionID
   join Product p on p.RateID = rt.RateID
   where 1=1
   and i.InvDate between '<From date as MM/DD/YYYY>' and '<To date as MM/DD/YYYY>'
   -- and p.LDCCode in (the only LDCCodes that need to be counted)
   -- and p.LDCCode not in (LDCCodes that need to be excluded)
   order by 2 desc
   
   select ProductCode,ProductID,Count(distinct CustID) as BilledCustomer_Count
   from #tmpc
   group by ProductCode,ProductID
   order by 1

</supportagent>