Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I have timespan values stored in SQL server like 165:56,258:65,258:50.

I need sum of those values like 683:51

What I have tried:

SQL
WITH CTE_SUM AS (
  SELECT
    SUM(
      LEFT([tot.time], 2) * 3600 + SUBSTRING([tot.time], 4, 2) * 60
    ) AS SUM_AS_SECONDS
  FROM
    [dbo].[production dt]
  WHERE
    [ordernumber] = '800000025277'
    AND setup = 'I'
    AND machine IN('HMC-01', 'HMC-02')
)
SELECT
  isnull(
    CAST(SUM_AS_SECONDS / 3600 AS VARCHAR) + ':' + CAST(SUM_AS_SECONDS % 3600 / 60 AS VARCHAR),
    '00:00'
  )
FROM
  CTE_SUM
Posted
Updated 9-Jan-23 22:44pm
v3

The most obvious solution is simple: don't store numeric values as strings - convert them to a "sensible" numeric value. For example, if the difference is usually measured in minutes and seconds, convert the timespans to seconds and store that in a INT field.
That way, you can do math on them directly without any fannying about with string chopping and conversion which gets incredibly slow on a large DB since SQL string handling is poor at best. And it means that the values stored are always valid instead of potentially corrupt.

I have no idea what intervals you samples are: they aren't minutes and seconds, or hours and minutes, they aren't formatted right for a "standard number" - so it could be anything, including invalid values already in your DB!
 
Share this answer
 
Comments
CPallini 10-Jan-23 4:44am    
5.
Richard Deeming 10-Jan-23 4:47am    
Treating it as hours and minutes gives the desired total, even though 258:65 should really be 259:05. :)
OriginalGriff 10-Jan-23 4:58am    
That's what I mean - already his DB has crap in it! :laugh:
Storing time-spans as strings is always a bad idea. Ideally, you should be storing them as a numeric value - for example, the total number of minutes.

If you're going to use strings, then you need to use them properly. Your first value is 165:56, so its clear that taking the first two characters as the number of hours isn't going to work.

You need to find the index of the : character, and split the string on that:
SQL
WITH cteTimes As
(
    SELECT
        SUM(
            CAST(LEFT([tot.time], CHARINDEX(':', [tot.time]) - 1) As int) * 3600
            + CAST(SUBSTRING([tot.time], CHARINDEX(':', [tot.time]) + 1, LEN([tot.time])) As int) * 60
        ) As TotalTimeInSeconds
    FROM
        dbo.[production dt]
    WHERE
        ordernumber = '800000025277'
    AND
        setup = 'I'
    AND
        maching In ('HMC-01', 'HMC-02')
)
SELECT
    CAST(TotalTimeInSeconds / 3600 As varchar(20)) + ':' + CAST((TotalTimeInSeconds % 3600) / 60 As varchar(20))
FROM
    cteTimes
;
NB: You have given your table and columns names which include "special" characters - "tot.time", "production dt" - which forces you to wrap them in square brackets in your queries. You should avoid this, and only use standard characters (A-Z, a-z, 0-9, _) in the names.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900