Insert rows of all months b/n a date range & add amount to the rows till the current range row for each change of month in SQL and VB.NET

See more:

VB

SQL-Server

Respected Seniors!

I am working for an ongoing project that uses 'VB.Net & SQL Server-2012'.

The DB & Table details are below:

SQL Database named 'DB_COLLECTOR',
Table named 'Fee_Payment',
Total 6 Columns named:

'S_No' Int Primary key identity(1,1),
        'Date_Start' datetime Null,
        'Date_End' datetime Null,
        'Prefixed_Fee' decimal(10) Null, 
        'Paid_Amount' decimal(10) Null, 
        'Balance' decimal(10) Null

I have 2 Forms:

1st Form takes 'Person Name, Deal_Start_Date, Deal_End_Date, Monthly_Fee' that saves datas to 'Customer_Master' Table in DB &

2nd Form takes 'Person Name, Payment_Amount' that saves datas to 'Fee_Payment' table.

When I enter a
Deal_Start_Date as '01-04-2018' &
Deal_End_Date as '31-03-2019'
for a Customer named 'John'
with a monthly fee fixed as $50.00 to 1st Form,

Help Required:

1) it should AUTOMATICALLY add 12 Rows in 'Fee_Payment' table with each row having 'Date_Start' as '01-Apr-2018' & 'Date_End' as '30-Apr-2018, apart from the relevant datas saved to 'Customer_Master'.

S_No  Date_Start  Date_End      Prefixed_Fee   Paid_Amount  Balance  <br />
----  ----------  --------      ------------   -----------  -------  <br />
1     01-Apr-2018 30-Apr-2018      Null           Null       Null    <br />
2     01-May-2018 31-May-2018      Null           Null       Null    <br />
3     01-Jun-2018 30-Jun-2018      Null           Null       Null    <br />
..     ....         ....            ..             ..         ..     <br />
12    01-Mar-2019 31-Mar-2019      Null           Null       Null

2) it should AUTOMATICALLY replace 'Null' with '$50.00' to 'Prefixed_Fee' Column of Apr-2018 Row if the date has come under the range of '01-Apr-2018 to 30-Apr-2018' i.e. if the current date is '02-Apr-2018'.

S_No  Date_Start  Date_End      Prefixed_Fee   Paid_Amount  Balance  <br />
----  ----------  --------      ------------   -----------  -------  <br />
1     01-Apr-2018 30-Apr-2018      50.00          Null       50.00   <br />
2     01-May-2018 31-May-2018      Null           Null       Null    <br />
3     01-Jun-2018 30-Jun-2018      Null           Null       Null    <br />
..     ....         ....            ..             ..         ..     <br />
12    01-Mar-2019 31-Mar-2019      Null           Null       Null

3) If customer, John had paid $125.00, it should allocate $50.00 from $125.00 to first row of Apr-2018 by filling the amount mentioned in row-1 of PreFixed_Fee (FIRST) & then from Balance amount, $50.00 to second row of May-2018 (SECOND) & Balance amount of $25.00 to Jun-2018 (THIRD).

S_No  Date_Start  Date_End      Prefixed_Fee   Paid_Amount  Balance  <br />
----  ----------  --------      ------------   -----------  -------  <br />
1     01-Apr-2018 30-Apr-2018      50.00          50.00       0.00   <br />
2     01-May-2018 31-May-2018      50.00          50.00       0.00   <br />
3     01-Jun-2018 30-Jun-2018      50.00          25.00      25.00   <br />
..     ....         ....            ..             ..         ..     <br />
12    01-Mar-2019 31-Mar-2019      Null           Null       Null

Thanks alot in Advance!

What I have tried:

Nothing comes to my mind, even to try something, thus no sample codes added that was tried. Hence, I apologize for the same. Please help!