stacki stacki - 3 months ago 8
SQL Question

Sum time in SQL Stored procedure

How sum time in SQL my procedure return:

START -- FINISH --- TOTAL
18:14:47 20:32:54 02:18:06
12:35:45 15:06:06 02:30:21
18:08:26 21:25:39 03:17:12


I need sum column TOTAL (08:05:40)

My stored procedure:

Alter PROCEDURE [dbo].[timeTotal]
@userName nvarchar(50)
AS
BEGIN

SELECT

CONVERT(VARCHAR(8),dateadd(HH,7,workStart),108) AS workStart,
CONVERT(VARCHAR(8),dateadd(HH,7,workFinish),108) AS workFinish,
CONVERT(VARCHAR(8),workFinish - workStart,108) AS total


FROM workTime
WHERE userName = userName


END


I need get 08:05:40

Answer

To sum time difference in hours, use the following:

SELECT
   (RIGHT('00' + CONVERT(VARCHAR(10), SUM(DATEDIFF(MINUTE, FromTime, ToTime)) / 60), 2)
  + ':' +
    RIGHT('00' + CONVERT(VARCHAR(2), SUM(DATEDIFF(Minute, FromTime, ToTime)) % 60), 2)
  + ':' +
    RIGHT('00' + CONVERT(VARCHAR(2), SUM(DATEDIFF(SECOND, FromTime, ToTime)) % 60), 2)) 
AS TotalTime FROM AllocateRoom

Use the above in the stored procedure and it returns sum of time difference as follows:

01:20:20

Finally your expected data as follows:

;WITH DEMO(ID, FromTime, ToTime) AS
(
    SELECT TOP 1
        ID
       ,CONVERT(DATETIME,FromTime,120) AS FromTime
       ,CONVERT(DATETIME,ToTime,120) AS ToTime
    FROM (VALUES (1,'2016-09-03 18:14:47.000','2016-09-03 20:32:54.000')
                ,(2,'2016-09-03 12:35:45.000','2016-09-03 15:06:06.000')
    ) AS X(ID,FromTime,ToTime)
)
SELECT TOP 1
    k.ID,
    k.FromTime,
    k.ToTime,
    DATEADD(SECOND,DATEDIFF(SECOND, k.FromTime, k.ToTime),CONVERT(TIME(0),'00:00:00',0)) AS TIME_DIFF,
    DATEADD(SECOND,SUM(DATEDIFF(SECOND, k.FromTime, k.ToTime)) OVER 
        (
            PARTITION BY (SELECT NULL)
        ),CONVERT(TIME(0),'00:00:00',0)) AS TIME_SUM
FROM DEMO k;