.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Calculating a Percentage Value in SQL

Posted By:      Posted Date: September 14, 2010    Points: 0   Category :Sql Server
Hey, I'm trying to calculate a percentage value in SQL. The following is my full stored procedure with sample data, if you run it you will see that I get one result which is grouped per Policy with a total for hrs_on and a total for hrs_hibernated, to calculate my percentage I need to use the following formula total_hrs_hibernated / total_hrs_on * 100 My problem is I can actually get the percentgae working fine if I do it for each individual record, if you run "Select * from #hibernatetemp" you will see my results with the percentage which are correct. I however need to group my results per policy and I'm not sure how to get the calculations to work correctly when I group by policy etc.  Would appreciate any help or advice on the method to do it as I may be over thinking this and trying to do it a more difficult way, Thanks    CREATE PROCEDURE [dbo].[SP_TEST_TEST2] AS /* Create temp table */ CREATE TABLE #hibernatetemp ( on_time datetime NULL, hibernate_time datetime NULL, startup_after_hibernate datetime NULL, off_time datetime NULL, sub_policy_name varchar(50), total_hrs_on decimal(18, 0), total_hrs_hibernated decimal(18, 0), hibernate_percentage decimal(18, 0) ) /** Insert Values into Temp Table from View, including Day/Night Rates*/ insert into #hibernatetemp (on_time, hibernate_time, startup_after_hi

View Complete Post

More Related Resource Links

Calculating percentage of sceduled hours

 Hi all,       I want to calcultae how many employees are scheduled less 24 hrs for each week,b/w 24 and 48 and greater than 48         In excel file i have weekly wise total hours scheduled for each employee  and total no of days scheduled in week(example 6 days and 1 off)which i will upload it to database,based on this i want to display reports  in bound controls Result should be like this                    Scheduled Hrs      8/1/2010        8/8/2010       8/15/2010       8/22/2010                  Less than 24 hrs     5%                       5%                  5%                   5%        Between 24 and 48 hrs    5%         

Calculating Percentage?


I need to calculate a PERCENTAGE figure based on two numbers? Column PatOperatedOn and TotalOperations

Why doesn't my query work?








COUNT(*) AS PatOperatedOn


SSRS ..Problem with SubTotal and Calculating Percentage


I am having problem to calculating % on base of subtotal value in Matrix Report.
                  R1   %           R2    %         GrandTot  %          
A       1     20     40%          10  50%         30  34%
         2     30     60%          10  50%         40  66%
       Total  50     100%        20  100%      60  100%
B       1     20   33%            10    20%       30     30%
         2     10   16%           10    20%       20     20%
         3     30    51%          30    60%       50     50%
       Total  60   100%       50     100%   100 100%

The Bold % i nee

Calculating GridView total using JavaScript/JQuery


Hello everyone.

One month ago I've had very nice conversation with a asp.net forum member asking about issue regarding calculation of GridView textboxes total using JavaScript. The main thing was to allow user to manually write values in the text boxes (placed inside GridView column) and if the value has changed automatically to perform calculation on client side.

At the end of the conversation, we came up to two solutions. The first solution was using plain JavaScript, but since I thought it was not so robust, and in order to make it more robust we would need to write few more functions. Therefore, I have simply proposed another solution using JQuery which came up much better, more robust with less code.

In this blog post I will show both of the solutions and will provide step by step explanation on how we did that.

Note: I'm using VWD.

Calculating Full Year Total

Im creating a sales report in SSRS by pointing it at a cube in SSAS.  Im creating calculated members to aggregate my data in various ways so they can be shown on the report.   I have a FACT table containing sales figures and various dimension tables including a server created time dimension.   Ive managed to create pretty much everthing I need (e.g. Current YTD, Prior year YTD).  What I now need is a full year figure to compare current year sales to.   Below is what Im aiming for i.e. the total of 122712 from 2009 appearing against every month in 2010.   Fiscal Year Fiscal Month Count Full Prior Year sales 2009 Apr-09 9805     May-09 9942     Jun-09 8986     Jul-09 9696     Aug-09 8912     Sep-09 10097     Oct-09 11713     Nov-09 10434     Dec-09 8820     Jan-10 10821     Feb-10 11844     Mar-10 11645     Total 122712   2010 Apr-10 3517 122712   May-10 1669 122712   Jun-10 2518 122712   Jul-10 3306 122712   Ive been bashing my head against a brick wall with this one so any help would be gratefully received!

Reports in Percentage

Hi ALL, I have scheduled hours of each employee for each week and no of days scheduled in a week.i want to get reports for weekly wise no of employees scheduled less 24 hrs per week,b/w 24 and 48 hrs and greater than 48 hrs       Scheduled Hrs              8/1/2010         8/8/2010        15/8/2010            22/8/2010 Less than 24 hrs               5%                      5%                       5%                          4%   b/w 24 and 48 hrs          4%                        6%                    &

calculating field in ssrs

I have a dataset which brings in sales, transcast, transbilling, and cogs in by group. I want to calculate net spend as a percent to sales and gross spend as a percent to sales. but can't seem to figure it out since sales, cost, transbilling, and cogs are all in one column(entry_type)...   my dataset looks like this   group | entry_type| year | jan | feb | mar | apr | may | etc...

wrong Percentage calculation showing in pie chart

Hi,       Im using "#PERCENT{P2}" this one to calculate percentage in pie chart.  It should take 2 decimal only rite? But it is taking 3 decimal values. Example: 20.90678 After applying the formula "#PERCENT{P2}"  it is showing as 20.91 i want to show as 20.90 only pls help me Thanks in advance, Abdul2010

Percentage of matching days off among employee's

Hi All,i have Intime and Outtime of each employee for all the days of 1st,2,3,4 week.There will be a off for 1 day or 2 days for each employees.Based on this i need to calculate consisitency days off assignmanet i.e no of matching days off among employees in percentage.If there are 100 emplyees out of 100 employees 50 people may get week off on the same day(for example sunday). I want result in percentage.result like thisConsistency Days of assignment            Across 4 weeks            3 weeks                2weeks                0                                                             2%                                   0%     &

Pie show percentage greater than 100%

Hi all , i have a pie chart and show percentage as label ,i set label data as #PERCENT{P0} but when report runs it has 3 value: 58%,11%,32% you can see the 3 value add up to 101% is something else i need to set on label ? any help for this ?

Calculating Sum of First values.

Hi All, I have a report with two groups. In the footer of child group I print First(Fields!Starting.Value). In the footer of the parent group I want total of what is printed in the footer of the child group. Is it doable? Thank you. Igor    

calculating the size required for backup of database

Hi, How to calculate the size required to take backup of a database in mssql 2000.the results returned by sp_spaceused differ too much from the actual size taken on the disk. Thanks in advance

Calculating day's until date

Hi There, I have a table that I am using as a diary - there are 3 columns, ID(numeric),Subject(varchar) and date(datetime). Is there any way that I can get SQL to select the Subject and then calculate how many day's it is until the date? For example: Subject        Date             Days Until Date Xmas           25/12/2010   100 Days Thanks Matt

Calculating email address from AD Username in a form

I have  a change request form that has a requied filed where the user enters a valid username.  In my view for this form I want to display this users email address based their username but I when I look at calculated values the username files is not one that I can do calculations from.  Does anybody know how to do this sort of calculation?

Add percentage subtotal to Matrix

I know this question has been asked many times but I can't seem to find a solution that works for me. I have a matrix as follows with two row groups (provider and hospital) and one column group (mix type). The matrix looks like this:     CKD ESRD PRIOR TOTAL Dr. Amin Arrowhead Hospital 2 3 1 6 Central 0 1 4 5 Westview 1 1 3 5 SUBTOTAL 3 5 8 16 Dr. Sacks Arrowhead Hospital 4 1 2 7 Central 2 2 4 8 Westview 0 4 1 5 SUBTOTAL 6 7 7 20 TOTAL 9 12 15 36 PCT 25% 33% 42% The PCT row is the subtotal row I'm trying to add to the matrix. I've tried adding a third row group and using the InScope function but I can not get it to produce the desired results below the subtotal row. Thanks in advance for any suggestions anyone can offer.                       

Percentage Calculation in T-SQL

Hello T-SQL people,   I need help on the above subject. I have two tables, the name of the first one is Productions with 3 fields namely: agentid,amountproduced,datesold then the second table name is budget with 3 fields namely: agentid, yearbudgetamount, budgetyear, now what i want now is that i want to calculate the percentage of each agent on  amountproduced as against the yearbudget, it must be within that budgetyear. Please I need Idea.   Thanks in advance!

Calculating MAT (Moving annual total) in MDX

Hi, I am presently using the following script to calculate the MAT : CREATE MEMBER CURRENTCUBE.[Measures].[LatestMAT] AS SUM([Time].[Time].currentmember.lag(13) : [Time].[Time].currentmember,[Measures].[Volume]) Could someone please tell me if there's any other more efficient way to do this? MAT or moving annual total is calculated as the sum of volume of every time period from the present month to a year back. (in my case, i have 13 periods in a year. so that is 13 periods back)
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend