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)

 

Answer 1

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.)

 

Answer 2

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).

 

---

--Brad (My Blog)

Answer 3

Brad,

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
	

Answer 4

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

 

---

--Brad (My Blog)

Answer 5

Brad i actually managed to solve this thank for the help