Download 100% FREE Office Document APIs for .NET
DotNetFunda.com
Online: 1008
Home > Forums > Sql Server >

How to find the percentage from these table?

Posted by Raghuldrag under Sql Server on 3/17/2015 | Points: 10 | Views : 640 | Status : [Member] | Replies : 3
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
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?



Reply | Reply with Attachment
Alert Moderator 

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

Latest Posts