Click here to Skip to main content
15,936,602 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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!
Posted
Updated 29-Mar-18 21:11pm
v2

1 solution

The question is too large to answer in detail but some ideas to help you forward:

Bullet 1: Since you know the date range, loop through the months between start and end date. During each iteration, insert a row in the table with values from the user interface

Bullet 2: In my opinion this is problematic since by default there is no process in the database that would automatically change the data when the date changes. You could build such mechanism with Sql Agent for example but I don't recommend this approach. Instead, why not save the prefixed fees in the first place? It would be quite simple to query the total balance by just reducing paid amount from the fee

Bullet 3: Again you would need a loop structure looping through the rows where balance is not zero, ordered by date. For each row assign a value into the paid amount from the payment until you run out of money in the payment transaction.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900