Overview

When a customer requests to transfer their account from one premise to another, the contract should carry on from the point when they left their current premises and should not result in updating the end date of the contract. However, there are some cases where this date may get extended.

Information

The end dates of the existing contract are used to create a new RateTransition only if the current contract is a Variable Rate Plan. Any other plan will be recorded as a new enrollment and will result in an end date that is calculated from the date of the Transfer of Service(ToS), thus pushing it ahead. This is expected behavior and not an anomaly.

<supportagent>

You can also find contracts that have mis-matching dates with the below query, where EnrollCustID can be found from the EnrollCustomer table for the given account number.

Select 
Distinct 
C.CustNo,P.Premno,PS.Status,C.CustID,C.CustStatus,RT.SwitchDate,RT.EndDate,
C2.CustNo,P2.Premno,PS2.Status,C2.CustID,C2.CustStatus,RT2.SwitchDate,RT2.EndDate,
Case When RT.EndDate<>RT2.EndDate Then 'Contract Dates Mis-Match' Else 'Contract Dates Match' End [Contract Status],
C2.CreateDate [TOS Date],LDCShortName,RT2.SoldDate

    From Customer C
    Join Premise P On P.CustID=C.CustID
    Join Customer C2 On C.CustID=C2.MasterCustID
    Left Join Premise P2 On P2.CustID=C2.CustID
        --and P2.CreateDate=(Select Max(CreateDate) From Premise Where CustID=P2.CustID)
        Join PremiseStatus PS On PS.PremiseStatusID=P.StatusID 
        Join PremiseStatus PS2 On PS2.PremiseStatusID=P2.StatusID
        Join RateTransition RT On RT.CustID=C.CustID and RT.RateTransitiOnID=(
        Select Max(RateTransitionID) From RateTransition Where CustID=RT.CustID)
        Join RateTransitiOn RT2 On RT2.CustID=C2.CustID and RT2.RateTransitionID=(
        Select Max(RateTransitionID) From RateTransition Where CustID=RT2.CustID)
        and Cast(RT2.SoldDate as Date)= Cast(C2.CreateDate as Date)
        Join LDC L On L.LDCID=P2.TDSP
        
Where 1=1
and C.CustID= <EnrollCustID>
and (C.Master='N' and C.Master='N')
and (Cast(P2.Endservicedate as Date)is NULL)
and P2.StatusID=10
--and (RT.EndDate<>RT2.EndDate)
Order By C2.CreateDate Desc

</supportagent>