# How to find the percentage from these table?

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

(
Block int,
responsibility varchar(500),
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.61	Finance	         Sales	02.LESS:-EXCISE DUTY	                 22.81	Sales	         Sales	03.Net Sales	                       1064.82	HR	         HR	04.Personnel Cost	                170.63	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     Pecentage1	Sales	         Sales	01.Sales (net of Sales Tax)	       1087.6         01	Finance	         Sales	02.LESS:-EXCISE DUTY	                 22.8         01	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
 0 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)/ActualsForDept3END As PercentageFROM ( 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) TempBlocksORDER BY Block, DEPT` Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gifRaghuldrag, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Nagamma on: 3/21/2015 [Member] Starter | Points: 25
 0 try to better for this bandi.ijSELECT Block, Responsibility, Heads, DEPT, Actuals, CASE WHEN BlockRowNumber = 1 then 100 WHEN BlockRowNumber>1 THEN 0 ELSE (Actuals*100)/ActualsForDept3END As PercentageFROM ( 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) TempBlocksORDER BY Block, DEPTRaghuldrag, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Nagamma on: 3/21/2015 [Member] Starter | Points: 25
 0 try to better bandi.ijtry to better for this bandiSELECT Block, Responsibility, Heads, DEPT, Actuals, CASE WHEN BlockRowNumber = 1 then 100 WHEN BlockRowNumber>1 THEN 0 ELSE (Actuals*100)/ActualsForDept3END As PercentageFROM ( 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) TempBlocksORDER BY Block, DEPTRaghuldrag, if this helps please login to Mark As Answer. | Alert Moderator
Latest Posts