How to find the percentage from these table?

Posted by Raghuldrag under Sql Server on 3/17/2015 | Points: 10 | Views : 539 | Status : [Member] | Replies : 3
create table Ram

(
Block int,
responsibility varchar(500),
Heads varchar(50),
Dept varchar(500),
Actuals float
)

insert into Ram values('1','Sales','Sales','01.Sales (net of Sales Tax)','1087.60')


My sample table Ram Shown like these

BLOCK RESPONSE HEADS DEPT ACTUALS
1 Sales Sales 01.Sales (net of Sales Tax) 1087.6
1 Finance Sales 02.LESS:-EXCISE DUTY 22.8
1 Sales Sales 03.Net Sales 1064.8
2 HR HR 04.Personnel Cost 170.6
3 Materials COGS 05.Material Cost 376.7



Now i wanna calculate percentage for these table that formula is

from block 2 onwards divide the value from block 1 dept=03.netsales values
(i.e)

percentage = dept(04) actuals * 100 / dept(03)

Expecting Output



BLOCK RESPONSE HEADS DEPT ACTUALS Pecentage
1 Sales Sales 01.Sales (net of Sales Tax) 1087.6 0
1 Finance Sales 02.LESS:-EXCISE DUTY 22.8 0
1 Sales Sales 03.Net Sales 1064.8 100 =>(1064.8*100/1064.8)
2 HR HR 04.Personnel Cost 170.6 16.17 =>(170.6 *100/1064.8)
3 Materials COGS 05.Material Cost 376.7 35.37 =>(376.7*100/1064.8)

How to make a code for my expecting output?




Responses

Posted by: Bandi on: 3/17/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
The below code suits for SQL Server 2012

SELECT Block, Responsibility, Heads, DEPT, Actuals, CASE WHEN BlockRowNumber = 1 then 100  
WHEN BlockRowNumber>1 THEN 0
ELSE (Actuals*100)/ActualsForDept3
END As Percentage
FROM ( SELECT *, Case When Block = 1 then ROW_NUMBER() OVER( ORDER BY Block, DEPT desc) ELSE 0 END BlockRowNumber,First_value(Actuals) OVER(ORDER BY case when Block= 1 then DEPT ELSE '' end desc) ActualsForDept3 FROM Ram
) TempBlocks
ORDER BY Block, DEPT


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nagamma on: 3/21/2015 [Member] Starter | Points: 25

Up
0
Down
try to better for this bandi.ij

SELECT Block, Responsibility, Heads, DEPT, Actuals, CASE WHEN BlockRowNumber = 1 then 100
WHEN BlockRowNumber>1 THEN 0
ELSE (Actuals*100)/ActualsForDept3
END As Percentage
FROM ( SELECT *, Case When Block = 1 then ROW_NUMBER() OVER( ORDER BY Block, DEPT desc) ELSE 0 END BlockRowNumber,First_value(Actuals) OVER(ORDER BY case when Block= 1 then DEPT ELSE '' end desc) ActualsForDept3 FROM Ram
) TempBlocks
ORDER BY Block, DEPT


Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nagamma on: 3/21/2015 [Member] Starter | Points: 25

Up
0
Down
try to better bandi.ij

try to better for this bandi

SELECT Block, Responsibility, Heads, DEPT, Actuals, CASE WHEN BlockRowNumber = 1 then 100
WHEN BlockRowNumber>1 THEN 0
ELSE (Actuals*100)/ActualsForDept3
END As Percentage
FROM ( SELECT *, Case When Block = 1 then ROW_NUMBER() OVER( ORDER BY Block, DEPT desc) ELSE 0 END BlockRowNumber,First_value(Actuals) OVER(ORDER BY case when Block= 1 then DEPT ELSE '' end desc) ActualsForDept3 FROM Ram
) TempBlocks
ORDER BY Block, DEPT


Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response