March 16, 2015 at 12:25 am
Hi Friends,
i made a procedure to the below output now i need to add column percentage depends on these output
block Response Heads Dept Actuals
1Sales Sales01.Sales (net of Sales Tax)1087.5999999999999
1Finance Sales02.LESS:-EXCISE DUTY 22.800000000000001
1Sales Sales03.Net Sales 1064.8
2HR HR 04.Personnel Cost 170.60000000000002
3Materials & productionCOGS 05.Material Cost 376.70000000000005
(.i.e)
block Response Heads Dept Actuals percentage
2HR HR 04.Personnel Cost 170.60 170.60*100/1064.8(dept=03.Net
Sales)
3Materials & productionCOGS 05.Material Cost 376.70 376.70*100/1064.8(dept=03.Net
Sales)
How to write make a query for that?
March 16, 2015 at 1:04 am
Can you post the Create Table and INSERT statements to reproduce this? (Figured you would have figured that out already. You've been here for a while!)
March 16, 2015 at 3:23 am
Hi Friends,
create table test
( Block int,
responsibility varchar(500),
Heads varchar(50),
Dept varchar(500),
Actuals float
)
insert into test values ('1','Sales','Sales','01.Sales (net of Sales Tax)','1087.59')
like my table show here
1Sales Sales 01.Sales (net of Sales Tax)1087.59
1Finance Sales 02.LESS:-EXCISE DUTY 22.80
1Sales Sales 03.Net Sales 1064.8
2HR HR 04.Personnel Cost 170.60
3Materials & production COGS 05.Material Cost 376.70
My actual table is above mentioned
now i wanna calculate percentage column
i.e
each dept value can be divided by 03.Net Sales this column value
how to do that
March 16, 2015 at 5:44 am
I'm not 100% sure I undertand but if you want each row to have a percentage of the total by department it will interesting as the % in all rows will have to be recalculated each time a rows is added, removed or updated. The solution to create a % can be done a couple of ways. Use a temp table to store the totals by department and then join to it and update the %. Or use a CTE. You could also use a subquery but that could be expensive depending on the rowcount.
March 16, 2015 at 11:38 pm
Not enough inserts to recreate the table data you're working on.
You REALLY need to read Jeff's article on how to get good help.
Please read this article.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply