Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to allocate the Amount to other columns accordingly Expand / Collapse
Author
Message
Posted Friday, May 24, 2013 1:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:01 AM
Points: 132, Visits: 254
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!

Post #1456310
Posted Friday, May 24, 2013 1:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:41 AM
Points: 486, Visits: 422
Allocate mince? do you have a percentage breakup to do it, or just randomly will allocate ?
Post #1456314
Posted Friday, May 24, 2013 2:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
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.
Post #1456325
Posted Friday, May 24, 2013 4:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1456724
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse