CODEWITHSUNDEEP
sqlsql-server
Peter_James
Peter_James

Reputation: 647

How would I get the final SUM of all the Minutes?

How would I get the total of all the minutes using the below code. I feel I am missing something.

The Results so far as as follows:

ActionLog | Mins

2015-06-15 16:04:00 | 3

2015-06-15 16:25:00 | 2

2015-06-15 16:26:00 | 1

--create our table
DECLARE @TableRows TABLE
(
ID TINYINT,
ActionLog SMALLDATETIME
);

--insert some data
INSERT INTO @TableRows
VALUES
(10,'20150615 16:01:00'),
(10,'20150615 16:04:00'),
(10,'20150615 16:23:00'),
(10,'20150615 16:25:00'),
(10,'20150615 16:26:00');

--set up a CTE which we will perform a self join on
WITH ExampleCTE
AS
(SELECT 
      ROW_NUMBER() OVER(ORDER BY ActionLog) AS RowNum
    , ActionLog
FROM @TableRows)

--now query the CTE using the self join to get our result
SELECT 
      t1.ActionLog
    , DATEDIFF(MINUTE,t2.ActionLog,t1.ActionLog) as Mins
FROM ExampleCTE t1
    LEFT JOIN ExampleCTE t2 ON T1.RowNum = T2.RowNum + 1
WHERE
    DATEDIFF(MINUTE,t2.ActionLog,t1.ActionLog) < 10
ORDER BY
    t1.ActionLog

Upvotes: 1

Views: 63

Answers (1)

Chris Wijaya
Chris Wijaya

Reputation: 1286

To find the sum of the difference based on your query:

--now query the CTE using the self join to get our result
SELECT 
      sum(DATEDIFF(MINUTE,t2.ActionLog,t1.ActionLog)) as Mins
FROM ExampleCTE t1
    LEFT JOIN ExampleCTE t2 ON T1.RowNum = T2.RowNum + 1
WHERE
    DATEDIFF(MINUTE,t2.ActionLog,t1.ActionLog) < 10

Upvotes: 1

Related Questions