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

Write New Post |

Search Forums | Answered

Resolved 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

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 (Actuals100)/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

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

0	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 (Actuals100)/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

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

0	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 (Actuals100)/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

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

Latest Posts