# Calculating percentage • ### Question

• Im trying to calculate a percentage in SQL, basically the following is the formula I need to calculate

Hibernated hrs + Sleep Hrs

----------------------------------                  * 100

Ontime + Hibernated hrs + Sleep hrs

I have all these values calculated in my stored procedure already, I'm just not too sure how to add and divide and multiple to get my percentage %. this is my attempt so far, I have "isnull" in there because i need to change any null values to 0 so that they can be added and used for division, thanks in advance for any help

```UPDATE #hibernatetemp
SET hibernatepercentage = ((isnull(total_hibernate_hrs, 0) + (total_sleep_hrs, 0)) / (isnull(total_hibernate_hrs, 0) + (total_sleep_hrs, 0) + total_hrs_on) * 100)
```

• Changed type Monday, June 14, 2010 4:52 PM It's a question
Monday, June 14, 2010 3:40 PM

• I think, then, that we have to check for NULLness in EACH hour column as opposed to checking for NULLness on their collective sums.

Try this... it should come out with non-zero percentages:

```UPDATE #hibernatetemp
SET hibernatepercentage = CASE
WHEN ISNULL(total_hibernate_hrs,0)+ISNULL(total_sleep_hrs,0)+ISNULL(total_hrs_on,0) = 0
THEN 0.0
ELSE 100.0
* (ISNULL(total_hibernate_hrs,0)+ISNULL(total_sleep_hrs,0))
/ (ISNULL(total_hibernate_hrs,0)+ISNULL(total_sleep_hrs,0)+ISNULL(total_hrs_on))
END
```

Either that, or you could change your code to put zeroes into the hours columns instead of NULLs... if you do that, then the percentage is simpler:

```UPDATE #hibernatetemp
SET hibernatepercentage = CASE
WHEN total_hibernate_hrs+total_sleep_hrs+total_hrs_on = 0
THEN 0.0
ELSE 100.0
* (total_hibernate_hrs+total_sleep_hrs)
/ (total_hibernate_hrs+total_sleep_hrs+total_hrs_on)
END
```

• Marked as answer by Wednesday, June 16, 2010 2:58 PM
Tuesday, June 15, 2010 5:33 PM

### All replies

• 1) Technically, the parenthesis goes before the *100.
2) If storing total hours and total hours is 0, a 0 should be stored, not NULL. NULL means N/A or unknown. This would negate the need for those confusing ISNULL()s
3) If the denominator = 0, there will be a overflow error. (NULLs avoid that.)

Monday, June 14, 2010 3:53 PM
• Are your Hours saved as integers?  If so, then you'll have to convert the expression to a decimal expression in order to get an accurate percentage.

You also have to account for a possible division by zero:

UPDATE #hibernatetemp
SET hibernatepercentage = CASE
WHEN ISNULL(total_hibernate_hrs+total_sleep_hrs+total_hrs_on,0) = 0
THEN 0.0
ELSE 100.0 * ISNULL(total_hibernate_hrs+total_sleep_hrs,0) / (total_hibernate_hrs+total_sleep_hrs+total_hrs_on)
END

So the CASE will check for a possible zero in the denominator... if it sees that it is zero, then it will set the percentage to zero.  Otherwise, it will do the calculation (note how I multiplied by 100.0 to convert the entire expression into a decimal).

• Proposed as answer by Monday, June 14, 2010 8:03 PM
• Edited by Monday, June 14, 2010 9:32 PM Forgot 2nd parameter of ISNULL
Monday, June 14, 2010 4:59 PM
• Thanks for the post my hours are set as 'decimal' but can be changed if needed. All my results are coming back as '0'  heres my full Stored Procedure with sample data, the problem from what i can see is that i have NULL values in my hibernate and sleep column, i guess i need to change these to 0?  thanks for the help appreciate it.

```CREATE PROCEDURE [dbo].[SP_HIBERNATE_PERCENTAGE]

AS

/* Create temp table */
CREATE TABLE #hibernatetemp
(
cost_id int,
pc_profile_id int,
pc_name varchar(50),
on_time datetime,
hibernate_Time datetime NULL,
sleep_Time datetime NULL,
off_time datetime,
total_hrs_on decimal(18, 0),
total_hibernate_hrs decimal(18, 0),
total_sleep_hrs decimal(18, 0),
hibernatepercentage float,

)

/** Insert Values into Temp Table from View, including Day/Night Rates*/

insert into #hibernatetemp (cost_id, pc_profile_id, pc_name, on_time, hibernate_time, sleep_time, off_time)
SELECT '1','1', 'test', '20100211 09:12:10', NULL, NULL, '20100211 18:12:10' UNION ALL
SELECT '1','1', 'test', '20100211 10:12:10', '20100211 13:12:10',NULL, NULL UNION ALL
SELECT '1','1', 'test', '20100211 08:12:10', NULL, '20100211 14:12:10', NULL

/** Get total hours computer is off*/
UPDATE #hibernatetemp
SET total_hrs_on = DATEDIFF (MINUTE, on_time ,COALESCE (hibernate_time , sleep_time, off_time))/ 60.0

/** Get total Hibernated hours*/
UPDATE #hibernatetemp
SET total_hibernate_hrs = DATEDIFF(MINUTE, on_time, hibernate_time)/ 60.0

/** Get total Sleep hours*/
UPDATE #hibernatetemp
SET total_sleep_hrs = DATEDIFF(MINUTE, on_time, sleep_time)/ 60.0

/** Get % Hibernate time*/

UPDATE #hibernatetemp
SET hibernatepercentage = CASE WHEN ISNULL(total_hibernate_hrs+total_sleep_hrs+total_hrs_on,0) = 0
THEN 0.0
ELSE 100.0 * ISNULL(total_hibernate_hrs+total_sleep_hrs,0) / (total_hibernate_hrs+total_sleep_hrs+total_hrs_on)
END

select * from #hibernatetemp

RETURN
```

Tuesday, June 15, 2010 10:40 AM
• I think, then, that we have to check for NULLness in EACH hour column as opposed to checking for NULLness on their collective sums.

Try this... it should come out with non-zero percentages:

```UPDATE #hibernatetemp
SET hibernatepercentage = CASE
WHEN ISNULL(total_hibernate_hrs,0)+ISNULL(total_sleep_hrs,0)+ISNULL(total_hrs_on,0) = 0
THEN 0.0
ELSE 100.0
* (ISNULL(total_hibernate_hrs,0)+ISNULL(total_sleep_hrs,0))
/ (ISNULL(total_hibernate_hrs,0)+ISNULL(total_sleep_hrs,0)+ISNULL(total_hrs_on))
END
```

Either that, or you could change your code to put zeroes into the hours columns instead of NULLs... if you do that, then the percentage is simpler:

```UPDATE #hibernatetemp
SET hibernatepercentage = CASE
WHEN total_hibernate_hrs+total_sleep_hrs+total_hrs_on = 0
THEN 0.0
ELSE 100.0
* (total_hibernate_hrs+total_sleep_hrs)
/ (total_hibernate_hrs+total_sleep_hrs+total_hrs_on)
END
```

• 