Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to allocate the Amount to other columns accordingly


How to allocate the Amount to other columns accordingly

Author
Message
avdhut.k
avdhut.k
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 266
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!
Bhaskar.Shetty
Bhaskar.Shetty
Right there with Babe
Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)Right there with Babe (780 reputation)

Group: General Forum Members
Points: 780 Visits: 509
Allocate mince? do you have a percentage breakup to do it, or just randomly will allocate ?
laurie-789651
laurie-789651
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 1271
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45448 Visits: 39945
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search