January 20, 2012 at 10:49 am
Hi,
My question might be a little confusing.If so let me know.. I will try to rephrase it
CompanyName Amount
xxx 10
yyy 8
zzz 9
Unknown 3
Now the Total amount is 30 .. How do i calculate the Cost allocated percentage for each company knowing that we have 3$ worth of Unknwon charges which can't be allocated to any of the Companies..
Can any one please help?
I am looking for logic than SQL
Thanks
January 20, 2012 at 11:17 am
If you don't want to include unknown amounts in the % you would subtract them from the total and then divide each company's amount by that new total. Right? Am I missing something here?
January 20, 2012 at 11:24 am
I too am confused if you want unknown in the total or not. If you do, you could do something like this:
DECLARE @t TABLE (companyname VARCHAR(10), amount int)
INSERT @t
VALUES ('xxx', 10),
('yyy', 8),
('zzz', 9),
('unknown', 3)
select companyname, amount, amount/((select SUM(amount) from @t)/100.0) perc
from @t
If you don't want it, then something like this:
DECLARE @t TABLE (companyname VARCHAR(10), amount int)
INSERT @t
VALUES ('xxx', 10),
('yyy', 8),
('zzz', 9),
('unknown', 3)
select companyname, amount, amount/((select SUM(amount) from @t where companyname <> 'unknown')/100.0) perc
from @t
where companyname <> 'unknown'
January 20, 2012 at 12:04 pm
Thanks for the reply guys..
but i am not sure if that's the answer i am looking for.. so, i am trying to re phrase my original question and it might seem completely different from what i have asked..but i think this is what my boss wants
If they were no Unknown charges.. all the percentages would been 100% but now that we have few unknown charges which cannot be distributed to any of these companies. i want to calculate what % of the charges are being allocated to each company
Hope it makes sense
January 20, 2012 at 12:08 pm
Hi
based on the example data you originally provided...can you please tell us what your expected results are??....hopefully this will clarify for all of us.
regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 20, 2012 at 12:24 pm
I am not sure what the formula would be to calculate the percentage and that's the reason i am posting it here for your help
January 20, 2012 at 12:40 pm
danny09 (1/20/2012)
I am not sure what the formula would be to calculate the percentage and that's the reason i am posting it here for your help
I am not looking for you to provide the formula...only the expected results....
here's another thought....
DECLARE @t TABLE (companyname VARCHAR(10), amount int)
INSERT @t
VALUES ('xxx', 10),
('yyy', 8),
('zzz', 9),
('unknown', 3)
select companyname, amount, CAST (amount/((select SUM(amount) from @t)/100.0) as decimal (5,2)) perc
from @t
where companyname <> 'unknown'
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 20, 2012 at 1:13 pm
I am confused here, are you just confused on the math and not the SQL code? If so, to calculate the percentage of each company's amount, you would take their amount and divide by the total of all amounts and multiply that result by 100. For example, company xxx is 10/30 = 0.33333 * 100 = 33.333, or 10/27 if you don't include the unknown in the total which equals 0.370370 and multiply that by 100 to get 37.0370.
January 20, 2012 at 1:16 pm
I understand, i am unable to come up with a logic and can't tell you what the exact answer would be.. i am expecting the percentages to be around or more than 90%
Let me try to explain what i am looking for ..reason for calculating these percents
basically i am trying to bill the companies for the calls they made, but we are unable to charge for few calls due to various reasons and so they are billed as unknown charges which is a loss to the company..
So,If we consider that if there are no unknown charges, then all the companies would have been charged correctly for the calls they have made and the percentage would be 100% for each of them. Now that we have unknown calls which can be part of calls made from any company, they want to know how much percent of charges have been allocated
January 20, 2012 at 1:32 pm
Yes, i am basically confused on the mathematical logic needed to be applied... I am not looking for percentage of the charges for a total value
January 20, 2012 at 1:34 pm
another idea.....
DECLARE @t TABLE (companyname VARCHAR(10), amount int)
INSERT @t
VALUES ('xxx', 10),
('yyy', 8),
('zzz', 9),
('unknown', 3)
select 100- CAST (amount/((select SUM(amount) from @t)/100.0) as decimal (5,2)) as AllocatedCharge_percentageoftotal
from @t
where companyname = 'unknown'
eg 3 out of a total possible amount of 30 (10%) cannot be charged...therefore 90% has been charged
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply