Using AVG but need to ROUND UP

Question

Post reply

Using AVG but need to ROUND UP

DaveBriCam

SSCarpal Tunnel

Points: 4294

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: 246695 More actions · Answer 1

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

DaveBriCam SSCarpal Tunnel Points: 4294 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: 90252 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: 100948 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) A socialist is someone who will give you the shirt off *someone else's* back.