Rounding seconds up to the nearest 15 minutes

I have a field with seconds in it and I need to disply it in hours which I can do by dividing it by 3600, but I am trying to figure out how to round it up to the nearest 15 minutes. I have tried a couple of things with ROUND and CEILING, but am not getting the right numbers back. Any help would be greatly appreciated.

Hi,

You need to divide the seconds by 15 first, round that (up or down as your logic dictates) and then divide by 4 to get hours.

Try this as example:

DECLARE @.secondsint
SET @.seconds = 1632
DECLARE @.hoursdecimal(18,2)
SET @.hours = CEILING(convert(decimal,@.seconds)/15 )
SELECT @.hours, @.hours/4

The result should be 109, 27.25 (27 and a quarter hours).
I included the two results so you can see what is happening.
Change the seconds to 1640 and the figures are 110, 27.50 (27 and half hours)

|||

Okay, you totally lost me. I only get to dabble with mssql every few months so please excuse my ignorance. I am trying to work what you said into my query, but I when I put in the decimal it throws an error.

SELECT dbo.SLPTRANS.ClientID,SUM(dbo.SLPTRANS.TransValue)AS Expr1,SUM(CEILING(dbo.SLPTRANS.TimeSpent / 15) * dbo.SLPTRANS.RateValue)AS BillableFeesFROM dbo.SLPTRANSINNERJOIN dbo.INVOICEON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordIDGROUP BY dbo.SLPTRANS.ClientIDHAVING (dbo.SLPTRANS.ClientID = 405)

|||

Hi,
Apologies for the late reply!

I think the SUM should be:
SUM( (CEILING(db.SLPTRANS.TimeSpent /15)/4 ) * dbo.SLPTRANS.RateValue)

What error message do you get?