CODEWITHSUNDEEP
sqlsql-server
Dinesh
Dinesh

Reputation: 112

Calculate total Working hours from sql column

Expected result is 3201:20. I have done this with split with ":". Please suggested best way to achieve this. 

DECLARE @tmpTime TABLE
(
    RowId INT IDENTITY(1, 1),
    EmployeeId INT,
    TotalWorkingTime NVARCHAR(10)
);

INSERT INTO @tmpTime
(
    EmployeeId, TotalWorkingTime
)
VALUES
(1,N'1500:30'),
(2,N'1700:50');

SELECT SUM(TotalWorkingTime) FROM @tmpTime

Upvotes: 0

Views: 601

Answers (3)

Zack
Zack

Reputation: 2341

As you can see from @GordonLinoff's answer, your query is quite complex when using a VARCHAR to represent what is really a duration of time. If you represent your data in a more natural way, your query becomes much simpler. For example, if you store your time worked as an integer (total minutes), you can use an intermediate CTE and a couple of CROSS APPLYs to get what you need:

-- note that TotalWorkingTime is now TotalWorkingTimeMinutes
DECLARE @tmpTime TABLE
(
    RowID INT IDENTITY(1,1),
    EmployeeID INT,
    TotalWorkingTimeMinutes INT
);

-- while I'm using a calculation to show
-- how the minutes get added, this would likely
-- be  done by the application, before it gets
-- sent to the database.
INSERT INTO @tmpTime
(EmployeeID, TotalWorkingTimeMinutes)
VALUES
(1, (1500 * 60) + 30),
(2, (1700 * 60) + 50);

-- I think this intermediate CTE makes things a bit clearer.
-- but of course, you can inline it as well.
WITH SummedMinutesWorked(SummedMinutes) AS
(
    SELECT SUM(TotalWorkingTimeMinutes)
    FROM @tmpTime
)

-- you can use the CROSS APPLY to get the hours,
-- then reference those to get the "remainder minutes"
-- the SELECT has to cast your hours and minutes to a VARCHAR
-- for concatenation
SELECT CAST(H AS VARCHAR(255)) + ':' + CAST(M AS VARCHAR(255))
FROM SummedMinutesWorked
    CROSS APPLY (SELECT SummedMinutes / 60 AS H) AS HoursWorked
    CROSS APPLY (SELECT SummedMinutes - (H * 60) AS M) AS RemainderMinutes

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

SQL Server doesn't off a time type with more than 24 hours. So, don't think of what you are doing in terms of time. It is just a funky string representation of numbers.

So, you can parse the value into numbers, do the summation, and then reconstruct the value:

select (cast(sum(hh) + sum(mm) / 60 as varchar(255)) + ':' +
        right('00' + cast(sum(mm) % 60 as varchar(255)), 2)
       ) as hhmm
from ( VALUES (1,N'1500:30'), (2,N'1700:50') ) t(EmployeeId, TotalWorkingTime) cross apply
     (values (cast(left(TotalWorkingTime, charindex(':', TotalWorkingTime) - 1) as int),
              cast(stuff(TotalWorkingTime, 1, charindex(':', TotalWorkingTime), '') as int)
             )
     ) v(hh, mm)

Upvotes: 3

Fahmi
Fahmi

Reputation: 37473

You can try using left() and right() function for finding character before and afer ':'

select 
 concat
 (
  sum(cast(left(TotalWorkingTime,CHARINDEX(':',TotalWorkingTime)-1) as int)),
  ':',
  case when sum(cast(right(TotalWorkingTime,CHARINDEX(':',TotalWorkingTime)-3) as int))>60 then sum(cast(right(TotalWorkingTime,CHARINDEX(':',TotalWorkingTime)-3) as int))-60 else sum(cast(right(TotalWorkingTime,CHARINDEX(':',TotalWorkingTime)-3) as int)) end
 ) FROM @tmpTime

Upvotes: 0

Related Questions