coll balance should adjust for due in sql server.

Question

HI SIR,

I Have a doubt in my script iamn having two table 1. My due 2. Coll Month on month my due will generate in due table but coll will come either correctly or two months gap like that.i want to know for this due when I got my coll if due and coll is equal need to update my status and when my coll got that date and amount.

1.coll got adusted in due again some balance is tere balance only taken for next due adjustment how to do this sir.

Iam giving my sample data.

Due table

pk_id LnNo InstlmtDueDt TotInstlmtAmt process Status CollRcpt CollDt CollAmt

1 L1 2016-03-07 2560 N Null Null Null Null

2 L1 2016-04-07 2560 N Null Null Null Null

3. L1 2016-05-07 2560 N Null Null Null Null

4. L1 2016-06-07 2560 N Null Null Null Null

5. L1 2016-07-07 2560 N Null Null Null Null

6. L1 2016-08-07 2560 N Null Null Null Null

 

Instalment amount will be same.

Coll table

pk_id LnNo LnRcptNo EffDt TranTypeAmt Balance Process collstatus

1 L1 R1 2016-03-07 2560.00 0.00 N NULL

2. L1 R2 2016-05-31 5120.00 0.00 N NULL

3. L1 R3 2016-07-01 2600.00 0.00 N NULL

3. L1 R4 2016-07-30 2520.00 0.00 N NULL

3. L1 R5 2016-08-27 2560.00 0.00 N NULL

Expected o/p In due table

pk_id LnNo InstlmtDueDt TotInstlmtAmt process Status CollRcpt CollDt CollAmt

1 L1 2016-03-07 2560 Y U R1 2016-03-07 2560

2 L1 2016-04-07 2560 Y U R2 2016-05-31 2560

3. L1 2016-05-07 2560 Y U R2 2016-05-31 2560

3. L1 2016-06-07 2560 Y U R3 2016-07-01 2560

4. L1 2016-07-07 40 Y I R3 2016-07-01 40 (Newly Inserted)

5. L1 2016-07-07 2520 Y U R4 2016-07-30 2520

6. L1 2016-08-07 2560 Y U R5 2016-08-27 2560

Colltable process alone Y (if adjusted to due)