CODEWITHSUNDEEP
sqlsql-servergroup-byrow-number
DotNetLearner
DotNetLearner

Reputation: 95

How to compare the difference for the last row in SQL

I have a view and this is the result of it:

ID  Type  Time                                RowNum
18  2     2015-12-14 12:15:00.9330000 +00:00  214
18  6     2015-12-14 12:21:22.4330000 +00:00  215
18  2     2015-12-15 09:04:41.2870000 +00:00  216
18  6     2015-12-15 09:06:22.9400000 +00:00  217
18  5     2015-12-15 09:07:28.0130000 +00:00  218

I am trying to compare the time difference between rows and group by the type by using the query below. My issue is for the last row I want to get the difference wth the current time and calculate the sum of difference. This is the query am using. How to compare only for the last row with the current time and calculate the sum:

WITH rows AS (SELECT * FROM [dbo].[View])
SELECT  p.ID ,
        p.Type ,
        SUM(DATEDIFF(SECOND, p.Time, q.Time))
FROM    rows p
        JOIN rows q ON p.RowNum = q.RowNum - 1
GROUP BY p.ID ,
         p.Type

Upvotes: 1

Views: 2267

Answers (2)

mindbdev
mindbdev

Reputation: 444

If you are using SQL Server 2012 or above this will work

;WITH CTE AS
(

SELECT
 a.ID
,a.Type
,a.Time,
ISNULL(LEAD(a.Time) OVER (Order by RowNum ASC),GETDATE()) AS NextTime
FROM
 FROM [dbo].[View] a
)
SELECT
         ID,
        Type,
        SUM(DATEDIFF(SECOND, CTE.Time, CTE.NextTime))
FROM CTE
GROUP BY
ID,
Type

Reference for LEAD()

https://msdn.microsoft.com/en-IN/library/hh213125.aspx

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Use LEFT JOIN and ISNULL function:

DECLARE @t TABLE
    (
      ID INT ,
      Type INT ,
      Time DATETIMEOFFSET ,
      RowNum INT
    )

INSERT  INTO @t
VALUES  ( 18, 2, '2015-12-14 12:15:00.9330000 +00:00', 214 ),
        ( 18, 6, '2015-12-14 12:21:22.4330000 +00:00', 215 ),
        ( 18, 2, '2015-12-15 09:04:41.2870000 +00:00', 216 ),
        ( 18, 6, '2015-12-15 09:06:22.9400000 +00:00', 217 ),
        ( 18, 5, '2015-12-15 09:07:28.0130000 +00:00', 218 );

SELECT  p.ID ,
        p.Type ,
        SUM(DATEDIFF(SECOND, p.Time, ISNULL(q.Time, GETDATE())))
FROM    @t p
        LEFT JOIN @t q ON p.RowNum = q.RowNum - 1
GROUP BY p.ID ,
         p.Type

Output:

18  2   483
18  5   21186
18  6   74665

Upvotes: 0

Related Questions