Using AVG but need to ROUND UP

Question

Post reply

Using AVG but need to ROUND UP

DaveBriCam

SSCarpal Tunnel

Points: 4298

January 6, 2021 at 7:06 pm

#3828233

Is there any way to get the following code to ROUND UP instead of DOWN?:

AVG(CASE 
      WHEN  [TIME] >= '09:00:00' AND [TIME] <= '21:00:59' AND [CAMPAIGN] = 'Vaccine' AND [DISPOSITION] = 'Disconnected' 
            THEN dbo.fnGetSeconds(QUEUE_WAIT_TIME) 
END)  AS Abandon_Time_Seconds,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Possibly, but without knowing what dbo.fnGetSeconds does, we can't help much.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 2

fnGetSeconds takes time in 00:00:00 format and turns it into seconds:

ALTER FUNCTION [dbo].[fnGetSeconds](@TimeFormatted varchar(10))RETURNS decimal(10, 2)ASBEGINRETURN(SELECT (LEFT(@TimeFormatted,2)*3600) +ROUND(DATEDIFF(MS, 0, '00' + RIGHT(@TimeFormatted,LEN(@TimeFormatted)-2))/1000.0,0)AS 'TimeSeconds')END

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Round up to what? You are getting an average of the number of seconds which will be a whole number - for example you could have an average of 23, 38, 126 - etc...

So - what are the rules for rounding up?

BTW - is [TIME] an actual time data type? If so - you should change your check to: [TIME] >= '09:00:00' AND [TIME] < '21:01:00' - this will allow for sub-seconds up to but not including 21:01:00 (e.g. 21:00:59.9999999).

Also - is QUEUE_WAIT_TIME really stored as a varchar(10) data type? If so - isn't there a column available that already has the number of seconds of queued wait time?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

If you want round up the avg to whole seconds, then you can use CEILING. If you want to round to a certain number of decimal places, use ROUND. In the meantime, let's clean up the function:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER FUNCTION [dbo].[fnGetSeconds] (
    @TimeFormatted varchar(10)
)
RETURNS decimal(10, 2)
AS
BEGIN
RETURN (
    SELECT DATEDIFF(SECOND, '00:00', @TimeFormatted)
)
END /*FUNCTION*/
GO

CEILING(AVG(CASE 
      WHEN  [TIME] >= '09:00:00' AND [TIME] <= '21:00:59' AND [CAMPAIGN] = 'Vaccine' AND [DISPOSITION] = 'Disconnected' 
            THEN dbo.fnGetSeconds(QUEUE_WAIT_TIME) 
END))

Or:

ROUND(AVG(CASE 
      WHEN  [TIME] >= '09:00:00' AND [TIME] <= '21:00:59' AND [CAMPAIGN] = 'Vaccine' AND [DISPOSITION] = 'Disconnected' 
            THEN dbo.fnGetSeconds(QUEUE_WAIT_TIME) 
END), 2)

Or: 

ROUND(AVG(CASE 
      WHEN  [TIME] >= '09:00:00' AND [TIME] <= '21:00:59' AND [CAMPAIGN] = 'Vaccine' AND [DISPOSITION] = 'Disconnected' 
            THEN dbo.fnGetSeconds(QUEUE_WAIT_TIME) 
END) + 0.05, 2, 1)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".