0

Sample data]

I have a table with two columns (TASKID and Dateadded). I am trying to find difference between timings and would like to calculate the sum of the diff (time) attribute.

 select taskid,dateAdded,CAST((dateadded -lag(dateadded) over (order by dateadded)) as time )as diff  
 from xyz where taskid=21240923 

with this statement got the difference between times but I am unable to sum the dynamically generated time column diff.

Post calculating the difference and sum of time column

Could any body please suggest to achieve this goal?

https://www.blogger.com/blogger.g?blogID=1341056964785354138#editor/target=post;postID=5797398234800649517;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=0;src=postname

  • Presumably you would use the SUM() function (together with appropriate grouping) to calculate the sum. – mustaccio Oct 8 at 17:15
  • Hi Mustaccio ,Tried with sum but it's not allowing sum (time). – Nalo Nenu Oct 8 at 17:47
  • Clearly you cannot add times, only durations. What would be the result of adding 9am and 5pm? – mustaccio Oct 8 at 18:22
  • in this case, for one task id there are multiple activities involved . would like to get the total completion time for the activity, based on the above table, if i can add all duration columns then it could be the total turn around time.. So would like to display the total sum of duration. here – Nalo Nenu Oct 8 at 19:30
1

Use your current query as a CTE (Common Table Expression) and select form that with aggregate functions, like:

WITH TimeDiffs AS (<your query>)
SELECT   taskid
       , SUM(diff)
FROM     TimeDiffs
GROUP BY taskid

(assuming you want the total per task, your question is not very clear on the details).

You could also use the existing query as a derived table:

SELECT taskid
     , SUM(diff)
FROM   (<your query>) AS TimeDiffs
GROUP BY taskid

which will produce the same result, though the CTE arrangement usually ends up being more readable and so easier to maintain going forward.

  • Hi David, tried the same approach but it is giving below error. "Operand data type time is invalid for sum operator." – Nalo Nenu Oct 8 at 16:49
  • Utilize DATEDIFF to generate a difference between the dates after which you can SUM those integer values and then cast back to a TIME datatype as the output. – John Eisbrener Oct 8 at 21:23
0

You can easily retrieve this using a CTE and the DATEDIFF and DATEADD functions:

Setup test data:

CREATE TABLE #TimeData
(
    TaskID INT,
    DateAdded DATETIME
)
GO
DECLARE @Bucket INT = 1,
    @Counter INT = 1

WHILE @Bucket < 5
BEGIN
    WHILE @Counter < 10
    BEGIN
        INSERT INTO #TimeData (TaskID, DateAdded)
        VALUES (@Bucket, DATEADD(SECOND, @Counter, GETDATE()))

        SET @Counter = @Counter + 1
    END
    SET @Counter = 0
    SET @Bucket = @Bucket + 1
END

Query:

The CTE creates a pseudo table and uses DATEDIFF and LAG to get the time difference in seconds between each row and the preceding row. In the main SELECT we SUM the TimeDiff (in seconds) and add those seconds to 0 to get the total time difference represented as a time value.

;WITH TimeDiff AS (
    SELECT TaskID,
        DateAdded,
        COALESCE(DATEDIFF(SECOND, LAG(DateAdded) OVER (PARTITION BY TaskID ORDER BY DateAdded), DateAdded), 0) AS TimeDiff
    FROM #TimeData
)

SELECT TaskID,
    MIN(DateAdded) AS DateStarted,
    MAX(DateAdded) AS DateEnded,
    CAST(DATEADD(SECOND, SUM(TimeDiff), 0) AS TIME) AS TotalTimeDiff
FROM TimeDiff
GROUP BY TaskID

Results:

TaskID  |   DateStarted             |   DateEnded               |   TotalTimeDiff
------------------------------------------------------------------------------------
1       |   2019-10-15 15:30:20.820 |   2019-10-15 15:30:28.820 |   00:00:08.0000000
2       |   2019-10-15 15:30:19.820 |   2019-10-15 15:30:28.820 |   00:00:09.0000000
3       |   2019-10-15 15:30:19.820 |   2019-10-15 15:30:28.820 |   00:00:09.0000000
4       |   2019-10-15 15:30:19.820 |   2019-10-15 15:30:28.820 |   00:00:09.0000000

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.