CODEWITHSUNDEEP
sqlsql-server-2012
Sabyasachi Mukherjee
Sabyasachi Mukherjee

Reputation: 303

Rounding seconds to minutes in SQL server

I have 10 minutes i.e 600 seconds for an activity. I have display the used part and remaining part in minutes. For example if 90 seconds is used then 510 seconds is left. I must display 2 minutes used and 8 minutes left. I have the following SQL

WITH SampleData (SECONDS) AS (  
    SELECT 90
    UNION ALL 
    SELECT 510
)
SELECT roundedUpSeconds =  CEILING(CAST(SECONDS AS DECIMAL(18,6)) / 60)
FROM SampleData

It returns 2 & 9 minutes respectively. It does not sum up to 10. How should I solve this issue so that it sums up to 10. I am using SQL Server.

Upvotes: 0

Views: 510

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Getting rounded numbers to add up is tricky. You can calculate the total sum and the cumulative sum -- then use the difference for the last value.

Your data doesn't have an ordering, so this just uses the maximum seconds as the last score:

WITH SampleData (SECONDS) AS (  
      SELECT 90
      UNION ALL 
      SELECT 510
     )
SELECT (CASE WHEN seconds < max_seconds THEN minutes
             ELSE total_minutes - sum(minutes) over (order by seconds rows between unbounded preceding and 1 preceding)
        END)
FROM (SELECT sd.*,
             (SUM(seconds) OVER ()) / 60 as total_minutes,
             CEILING(seconds * 1.0 / 60) as minutes,
             MAX(seconds) OVER () as max_seconds
      FROM SampleData sd
     ) sd;

Here is a db<>fiddle.

Upvotes: 2

Arvind
Arvind

Reputation: 141

90 seconds is 1.5 minutes and 510 seconds is 8.5 minutes which adds up to 10, but since you are using the ceiling function it round 1.5 up to 2 and 8.5 to 9, so you are not getting the right answer

Upvotes: 1

Related Questions