How to Update Column based on Due and collection as explained in post

Question

Post reply

How to Update Column based on Due and collection as explained in post

avdhut.k

SSCrazy

Points: 2834
More actions
September 20, 2013 at 3:47 am

#298949

Create Table #temp
(
Number Int,
Princ_Due Int,
Int_Due Int,
Other_Due Int,
Collectionn Int,
Princ_Adj Int,
Int_Adj Int
)
Insert Into #Temp(Number,Princ_Due,Int_Due,Other_Due,Collectionn)
Values(1,0,100,200,300),
(2,100,200,300,800),
(3,100,200,-100,200),
(4,100,200,100,1000)
Select * from #Temp
/*
Following is the column description,
Princ_Due --> princple due( means amount yet to be collected)
Int_Due --> Interest due
Other_Due --> Other due
Collectionn -- Total amount collected.
My requirement is ,Collection Amount should be get adjusted according to priority,first Other,Interest and then Principle.
For example in --> record 1 ,
Collection = 300,
Other_Due = 200
Int_Due = 100
Princ_due = 0
So my Desired output should be,
Int_Adj = 0
Princ_Adj = 0
-->record 2 ,
Collection = 800,
Other_Due = 300
Int_Due = 200
Princ_due = 100
So my Desired output should be,
Int_Adj = 0
Princ_Adj = -200
-->record 3 ,
Collection = 200,
Other_Due = -100 (Negative means it is not due it is collected )
Int_Due = 200
Princ_due = 100
So my Desired output should be,
Int_Adj = 0
Princ_Adj = 100
-->record 3 ,
Collection = 1000,
Other_Due = 100
Int_Due = 200
Princ_due = 100
So my Desired output should be,
Int_Adj = 0
Princ_Adj = -600
*/
Please help me .
Thanks in Advance!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

Can you explain the math at work in your examples. For example

-->record 2 ,

Collection = 800,

Other_Due = 300

Int_Due = 200

Princ_due = 100

So my Desired output should be,

Int_Adj = 0

Princ_Adj = -200

Not sure how those numbers equal a negative? Do you have a matematical formula that is used?

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

All I understand would be something like this.

UPDATE #temp SET

Princ_Adj = ( Princ_due + Int_Due + Other_Due) - Collection ,

Int_Adj Int = 0

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

avdhut.k SSCrazy Points: 2834 More actions · Answer 3

Hi dan

The meaning of the negative figures is,

In simple words,

if the collection 800,

we should adjust this collection against the due in priority, first otherdue,if any collection is left after adjusting otherdue then Intdue,if any collection still left then adjust in princ_due,and still any collection is left then show this as negative amount (negative amount indicates that there is still collection is pending.),

hence in the given example negative (200) is required.