Based on above record in ‘factMaster’ table I need to insert rows into ‘factDetail’ table as below:

OrderID

PaidMonth

PaidAmount

217

2012-11-04

82

217

2012-10-04

82

217

2012-09-04

82

217

2012-08-04

82

217

2012-07-04

30

General Explanation:

When a factMaster table row has a Total refund ( e.g. 358 ) it has be distributedto factDetail table , based on MonthlyRefund value ( 82)

This has to be done creating rows on MONTHLY BASIS , starting from factMaster End Date ( 2012-11-04)

So,

2012-11-04$82

2012-10-04$82 …….Until Total Refund is paid off, but when we come up to inserting first 4 records we have come up to 328 ( i.e. 82 * 4 ),, therefore I cannot have another row as MonthlyRefund as 82 , then amount will
be 328 + 82 = 410 ( why then I exceed the Total refund of 358 )so my last record should have 358-328 = 30 ….

Réponses

The script below covers your requirement, although it's worth pointing out that you need to be careful around the logic used for the payment dates. I've allowed for differences at the end of each month in this script, but you need to go through your
business logic in full and ensure your final solution covers all "tricky" scenarios.

Also, test out the performance before implementing. This script does not allow for the setup of your environment - for example you will ideally have a pre-built Tally table to use rather than generating one.

The script takes each order, and generates monthly records using the Tally table based on a calculation for the number of months required. I'm happy to explain further if necessary.

Toutes les réponses

The script below covers your requirement, although it's worth pointing out that you need to be careful around the logic used for the payment dates. I've allowed for differences at the end of each month in this script, but you need to go through your
business logic in full and ensure your final solution covers all "tricky" scenarios.

Also, test out the performance before implementing. This script does not allow for the setup of your environment - for example you will ideally have a pre-built Tally table to use rather than generating one.

The script takes each order, and generates monthly records using the Tally table based on a calculation for the number of months required. I'm happy to explain further if necessary.

This is fantastic, I run your code on my Test server , it worked well, Thanks,,, But I need to get my head around about this 'Tally Table' concept, this is the first time I am playing with this type of logic.... I am trying to run this query over and over
and try get my head around it, at the same time appreciate if you could provide bit more explanation on this please..