How to get different time between every row and previous row

Question

i work on sql server i can't calculate created date time from every row and previous based on created date
my structure table as below :

CREATE TABLE [dbo].[TradeCodesErrorLog](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [ErrorMessage] [varchar](1000) NULL,
    [Line] [int] NULL,
    [OperationName] [varchar](100) NULL,
    [Comment] [varchar](200) NULL,
    [CreationDate] [datetime] NULL,
 CONSTRAINT [PK_TradeCodesErrorLog] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING ON
GO
SET IDENTITY_INSERT [dbo].[TradeCodesErrorLog] ON 

INSERT [dbo].[TradeCodesErrorLog] ([ID], [ErrorMessage], [Line], [OperationName], [Comment], [CreationDate]) VALUES (176797, N'Success', 4044, N'Trade Code Generation Job', N'ProductID: 4044', CAST(0x0000ADE500896D45 AS DateTime))
INSERT [dbo].[TradeCodesErrorLog] ([ID], [ErrorMessage], [Line], [OperationName], [Comment], [CreationDate]) VALUES (176798, N'Success', 4046, N'Trade Code Generation Job', N'ProductID: 4046', CAST(0x0000ADE50089D078 AS DateTime))
INSERT [dbo].[TradeCodesErrorLog] ([ID], [ErrorMessage], [Line], [OperationName], [Comment], [CreationDate]) VALUES (176799, N'Success', 4047, N'Trade Code Generation Job', N'ProductID: 4047', CAST(0x0000ADE50089DEF1 AS DateTime))
INSERT [dbo].[TradeCodesErrorLog] ([ID], [ErrorMessage], [Line], [OperationName], [Comment], [CreationDate]) VALUES (176800, N'Success', 4049, N'Trade Code Generation Job', N'ProductID: 4049', CAST(0x0000ADE50089F23A AS DateTime))
INSERT [dbo].[TradeCodesErrorLog] ([ID], [ErrorMessage], [Line], [OperationName], [Comment], [CreationDate]) VALUES (176801, N'Success', 4050, N'Trade Code Generation Job', N'ProductID: 4050', CAST(0x0000ADE50089FB8E AS DateTime))
INSERT [dbo].[TradeCodesErrorLog] ([ID], [ErrorMessage], [Line], [OperationName], [Comment], [CreationDate]) VALUES (176802, N'Success', 4052, N'Trade Code Generation Job', N'ProductID: 4052', CAST(0x0000ADE5008A058E AS DateTime))

ID  ErrorMessage    Line    OperationName   Comment CreationDate    difftime
176797  Success 4044    Trade Code Generation Job   ProductID: 4044 2021-11-19 08:20:21.350 0
176798  Success 4046    Trade Code Generation Job   ProductID: 4046 2021-11-19 08:21:46.000 0
176799  Success 4047    Trade Code Generation Job   ProductID: 4047 2021-11-19 08:21:58.350 0
176800  Success 4049    Trade Code Generation Job   ProductID: 4049 2021-11-19 08:22:14.807 0
176801  Success 4050    Trade Code Generation Job   ProductID: 4050 2021-11-19 08:22:22.767 0
176802  Success 4052    Trade Code Generation Job   ProductID: 4052 2021-11-19 08:22:31.300 0

so diff time for first row as 176797 will be 0
diff time for second row as 176798 will be
2021-11-19 08:21:46.000
subtract or minus
2021-11-19 08:20:21.350 and result will assign to diff time to 176798
diff time for third row as 176799 will be
2021-11-19 08:21:58.350
subtract or minus
2021-11-19 08:21:46.000 and result will assign to diff time to 176799
so how to make select query display different time by minute

Answer 1

Hi @ahmed salah ,

Please also check:

;WITH cte  
as(SELECT *,ROW_NUMBER() OVER(ORDER BY ID) rr  
FROM TradeCodesErrorLog)  
  
SELECT c1.*,  
TimeDeiff=RIGHT(LEFT(CAST(DATEADD(ms,DATEDIFF(ms,c2.CreationDate,c1.CreationDate), 0) AS time(3))  
,8),5)  
FROM cte c1  
LEFT JOIN cte c2 ON c1.rr-1=c2.rr  

Output:

;WITH cte  
as(SELECT *,ROW_NUMBER() OVER(ORDER BY ID) rr  
FROM TradeCodesErrorLog)  
  
SELECT c1.*,  
TimeDeiff =RIGHT(CAST(DATEADD(ms,DATEDIFF(ms,c2.CreationDate,c1.CreationDate), 0) AS time(0))  
,5)  
FROM cte c1  
LEFT JOIN cte c2 ON c1.rr-1=c2.rr  

Output:

time(0) will be rounded up, and the final result will be slightly different

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

I don't understand what output you are looking for, but this query will give you the difference in milliseconds from the previous row.

SELECT *, datediff(ms, LAG(CreationDate) OVER(ORDER BY CreationDate), CreationDate)
FROM   TradeCodesErrorLog