How to allocate the Amount to other columns accordingly

Question

Post reply

How to allocate the Amount to other columns accordingly

avdhut.k

SSCrazy

Points: 2834
More actions
May 24, 2013 at 1:22 am

#294333

Hi,
I have a requirement,
Following is the structure of my table,
Create Table #Test
(
Valuedt Datetime,
TotalDue_Amount Numeric (12,2),
TotalRecieved_Amount Numeric (12,2),
Maintaince_Due Numeric (12,2),
InsDue Numeric (12,2),
Stationary_Due Numeric (12,2),
Travelling_Exp_Due Numeric (12,2),
Maintaince_Collection Numeric (12,2),
Ins_Collection Numeric (12,2),
Stationary_Collection Numeric (12,2),
Travelling_Exp_Collection Numeric (12,2)
)
Insert Into #Test
Values (GETDATE(),2000,1000,500,200,100,50,0,0,0,0)
Insert Into #Test
Values (DateAdd(dd,1,GETDATE()),5000,4000,1000,500,500,100,0,0,0,0)
I have TotalDue_Amount which sum of all other due,
And I have TotalRecieved_Amount ,
Now my requirement is,
I want to allocate this TotalRecieved_Amount to
Maintaince_Collection,
Ins_Collection,
Stationary_Collection,
Travelling_Exp_Collection
according to follwing due amount in following order,
Maintaince_Due,
InsDue,
Stationary_Due,
Travelling_Exp_Due
Kindly help in this requirement.
Kindly tell me if any more information is needed.
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

Bhaskar.Shetty Hall of Fame Points: 3054 More actions · Answer 1

Allocate mince? do you have a percentage breakup to do it, or just randomly will allocate ?

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 2

How about this:

--== Test Data ==--

if object_id('tempdb..#Test') is not null

drop table #Test;

Create Table #Test

(

Valuedt Datetime,

TotalDue_Amount Numeric (12,2),

TotalRecieved_Amount Numeric (12,2),

Maintaince_Due Numeric (12,2),

InsDue Numeric (12,2),

Stationary_Due Numeric (12,2),

Travelling_Exp_Due Numeric (12,2),

Maintaince_Collection Numeric (12,2),

Ins_Collection Numeric (12,2),

Stationary_Collection Numeric (12,2),

Travelling_Exp_Collection Numeric (12,2)

);

/*

I have TotalDue_Amount which sum of all other due,

NOTE: TotalDue_Amount IS NOT THE SUM OF ALL OTHER DUE IN THE GIVEN TEST DATA.

*/

Insert Into #Test

Values (GETDATE(),2000,1000,500,200,100,50,0,0,0,0);

Insert Into #Test

Values (DateAdd(dd,1,GETDATE()),5000,4000,1000,500,500,100,0,0,0,0);

select * from #Test;

--== Suggested Solution ==--

update #Test

set Maintaince_Collection = (TotalRecieved_Amount * (Maintaince_Due/TotalDue_Amount)),

Ins_Collection = (TotalRecieved_Amount * (InsDue/TotalDue_Amount)),

Stationary_Collection = (TotalRecieved_Amount * (Stationary_Due/TotalDue_Amount)),

Travelling_Exp_Collection = (TotalRecieved_Amount * (Travelling_Exp_Due/TotalDue_Amount))

select * from #Test;

/*

I have TotalDue_Amount which sum of all other due,

And I have TotalRecieved_Amount ,

Now my requirement is,

I want to allocate this TotalRecieved_Amount to

Maintaince_Collection,

Ins_Collection,

Stationary_Collection,

Travelling_Exp_Collection

according to follwing due amount in following order,

Maintaince_Due,

InsDue,

Stationary_Due,

Travelling_Exp_Due

Kindly help in this requirement.

Kindly tell me if any more information is needed.

*/

Unless I'm missing something, TotalDue_Amount is not the sum of all other due in the test data provided.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

I'd define the column in the table as a PERSISTed Computed column to make life easier for everyone and the formula would simply be the addition of the other columns.

As a bit of a sidebar, NUMERIC(12,2) has a precision of 12 and that takes 9 bytes of storage. NUMERIC(19,2) has a precision of 19 but, guess what? It still "only" takes 9 bytes of storage.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)