You may want to re-calculate adjustments to the invoice amounts of a customer for a certain period in the past, with different rates than were actually applied. This might be for customers who were rolled on to variable-rate plans and now need invoice amounts for fixed-rate plans for certain billing period(s).
Create a support ticket mentioning the customer's account number or customer ID and the rate schedule comprising of the date ranges during which those rates will be effective and the effective rates for those ranges. Make sure that the rate intervals start on the month boundaries as shown below.
|Mar 1, 2019 - Feb 29, 2020||0.611|
|Mar 1, 2020 - Jun 1, 2021||0.519|
If the rate schedule intervals shared by the client don't start on month boundaries then get it corrected with the client.
- Login to the Jump server 10.185.40.141.
- Open Microsoft SSMS and connect to
New Queryin the top bar.
- Select the client-specific database.
- Execute the following query to create a temporary table containing the rate schedule. There will be as many select statements as there will be months in the rate schedule, as depicted below for the example suggested above.
create table #tmpa(dt Date,Rate decimal(15,7),Mon Varchar(20)) insert into #tmpa (dt,rate,mon)
-- below select formatted as select 'MM/01/YYYY' ,'RATE' ,MM/YYYY' union all select '03/01/2019' ,'0.611' ,'03/2019' union all
select '04/01/2019' ,'0.611' ,'04/2019' union all
select '05/01/2019' ,'0.611' ,'05/2019' union all
select '06/01/2019' ,'0.611' ,'06/2019' union all
select '07/01/2019' ,'0.611' ,'07/2019' union all
select '08/01/2019' ,'0.611' ,'08/2019' union all
select '09/01/2019' ,'0.611' ,'09/2019' union all
select '10/01/2019' ,'0.611' ,'10/2019' union all
select '11/01/2019' ,'0.611' ,'11/2019' union all
select '12/01/2019' ,'0.611' ,'12/2019' union all
select '01/01/2020' ,'0.611' ,'01/2020' union all
select '02/01/2020' ,'0.611' ,'02/2020' union all
select '03/01/2020' ,'0.519' ,'03/2020' union all
select '04/01/2020' ,'0.519' ,'04/2020' union all
select '05/01/2020' ,'0.519' ,'05/2020' union all
select '06/01/2020' ,'0.519' ,'06/2020' union all
select '07/01/2020' ,'0.519' ,'07/2020' union all
select '08/01/2020' ,'0.519' ,'08/2020' union all
select '09/01/2020' ,'0.519' ,'09/2020' union all
select '10/01/2020' ,'0.519' ,'10/2020' union all
select '11/01/2020' ,'0.519' ,'11/2020' union all
select '12/01/2020' ,'0.519' ,'12/2020' union all
select '01/01/2021' ,'0.519' ,'01/2021' union all
select '02/01/2021' ,'0.519' ,'02/2021' union all
select '03/01/2021' ,'0.519' ,'03/2021'
- Execute the following query, remembering to substitute either the provided account number or customer ID depending on what the client provided, to generate the revised billing amount
id.CategoryID,id.InvDetDesc,id.InvDetQty,id.Rate as Invoice_Rate,id.InvDetAmt,
a.Rate as PTC_Rate,(a.Rate-id.Rate) as RateDiff, id.InvDetQty * (a.Rate-id.Rate) as InvAdj from Customer c join Invoice i on i.CustID = c.CustID join InvoiceDetail id on id.InvoiceID = i.InvoiceID left join #tmpa a on Concat(DATEPART(month, a.dt), DATEPART(year, a.dt)) = Concat(DATEPART(month, i.InvDate), DATEPART(year, i.InvDate) ) where 1=1 and c.CustID=<client-provided customer ID>
-- and c.CustNo='<client-provided account number>' and id.CategoryID=1 order by i.invdate
InvAdjcolumn in the above query has the adjustment amount for each invoice. Export this result set and share it with the client.