Currently I am doing some validations based on some credits
I have a table, which saves the account status of a credit as such, and I want to validate the following
That in 3 months, if the client has fully complied with the payment of the fee, that is, that he has paid in the first 5 days after the date agreed in the payment plan, that a field identifies me that this was fulfilled, for example a field called Fulfills and that accumulates 1, 2, etc.
For example,
I have this account statement for this credit
Credito Fecha Plan Pago Fecha en la que Pago
123456 1/6/2021 4/6/2021
123456 1/7/2021 1/7/2021
123456 1/8/2021 10/8/2021
123456 1/9/2021 1/9/2021
123456 1/10/2021 1/10/2021
123456 1/11/2021 1/11/2021
123456 1/12/2021 2/12/2021
123456 1/1/2022 3/1/2022
123456 1/2/2022 4/2/2022
123456 1/3/2022 13/6/2022
123456 1/4/2022 14/6/2022
123456 1/5/2022 15/6/2022
In the image we can see that since the credit began, the first 2 payments are compliant but the 3rd is no longer, then it goes to the 4th month and from there in 3 months if it is compliant, so when it is fulfilled I would like it to identify me that yes complied and save so that the count begins from there
I have made a basic script to the table where the information is, however I do not know the logic or how to start to do what I need
SELECT
Credito,
[FechaPlanPago],
[FechaEnLaQuePago]
FROM PlanCredit
WHERE Credito='123456'
I don't know if I should use cursors for this? creation of some temporary or physical tables
If someone help me please
The process to obtain this can be a bit complex when you lack experience, but it is important that you understand it well step by step so that you can understand the complete process and replicate it at any time.
First you need to identify which lines are compliant and which are not. That's the easy part.
Then you face a problem that in English is called "Gaps and Islands" (gaps and islands). There are multiple solutions for this with varying levels of performance and complexity. I am using an option with acceptable performance and minimal complexity. You can search for more information on these solutions, but most will be in English. References: link 1 , link 2 , link 3
Finally, we can obtain which are the groups of 3 consecutive months that are met. We divide the count of each "island" by 3 to get a whole number and multiply by 3 to get the maximum number of months. For example, if it is 8 months, the maximum will be 6, but if it is 9 months, the maximum will be 9. Thus, we only compare it with a consecutive number within each island and we can define which row complies and which does not.