Member# 33A Downgraded on 3/23/2016 but it was Active on 1/11/2016.
In the Contract Table This contract Has an Status of DG which is the Latest Status of that Contract.
But Status History Table has Two Status of this Contract AC on 1/11/2016 and DG on 3/23/2016.
The way we Recognize revenue is, From the Status Code(Contract Table) we only take AC & PA and from
the Status Code History(Status History Table) we only take AC & PA.
The Problem is, for example Member# 33A the Status Code (in the Contract table) is DG on 3/23/2016. But on
1/11/2016 it was AC in the Contracts table. We can see that from the History table. I would like to
Recognize this Contract as Revenue on 1/11/2016. Since this contract is no more AC status in contract
table i cant get this as revenue Because its in DG status in the Contract table.
According to Business logic if i us AC & PA from Status Code (from Contract Table) and AC & PA from
Status Code History (History table) then I am missing out Member# 33A as revenue.
How can i resolve this problem? Please help. Please find attach file for the example data.