How to sum up time field in SQL Server

by 
SELECT EmployeeID, minutes_worked = SUM(DATEDIFF(MINUTE, '0:00:00', WrkHrs)) 
FROM dbo.table 
-- WHERE ...
GROUP BY EmployeeID;

You can format it pretty on the front end. Or in T-SQL:

;WITH w(e, mw) AS
(
    SELECT EmployeeID, SUM(DATEDIFF(MINUTE, '0:00:00', WrkHrs)) 
    FROM dbo.table 
    -- WHERE ...
    GROUP BY EmployeeID
)
SELECT EmployeeID = e,
  WrkHrs = RTRIM(mw/60) + ':' + RIGHT('0' + RTRIM(mw%60),2)
  FROM w;

However, you’re using the wrong data type. TIME is used to indicate a point in time, not an interval or duration. Wouldn’t it make sense to store their work hours in two distinct columns, StartTime and EndTime?

More Related Contents:

  1. How can I schedule a job to run a SQL query daily?
  2. What’s the best practice for primary keys in tables? [closed]
  3. Reset identity seed after deleting records in SQL Server
  4. How to create multiple one to one’s
  5. Generate a resultset of incrementing dates in TSQL
  6. T-SQL Cast versus Convert
  7. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  8. How would you implement sequences in Microsoft SQL Server?
  9. What does =* mean?
  10. Correct use of transactions in SQL Server
  11. How to run a stored procedure in sql server every hour?
  12. Splitting the string in sql server
  13. Why do you create a View in a database?
  14. Need to list all triggers in SQL Server database with table name and table’s schema
  15. How can I do a BEFORE UPDATED trigger with sql server?
  16. Favourite performance tuning tricks [closed]
  17. Deferrable Constraints in SQL Server
  18. How to check which locks are held on a table
  19. Know relationships between all the tables of database in SQL Server
  20. Can SQL Server Express LocalDB be connected to remotely?
  21. How do I create and query linked database servers in SQL Server?
  22. The object ‘DF__*’ is dependent on column ‘*’ – Changing int to double
  23. Using SQL Server as a DB queue with multiple clients
  24. Suggestions for implementing audit tables in SQL Server?
  25. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  26. How do I change db schema to dbo
  27. The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
  28. Functions vs Stored Procedures
  29. Executing SQL query on multiple databases
  30. Single or multiple databases

Leave a Comment