Calculate date difference between two rows of the same table

Hi friends,

Today I am going to demonstrate calculate date difference between two rows of the same table.

Consider the below table.

The query for calculating date difference between 2nd row to 1st row, 3rd row to 2nd row and so on.

Calculate difference in days:

DECLARE @temp1 TABLE (
 StudentID INT
 ,NAME VARCHAR(50)
 ,RN INT
 ,createddate DATETIME
 )

INSERT INTO @temp1 (
  StudentID
 ,NAME
 ,RN
 ,createddate
 )
SELECT [StudentID]
 ,dbo.Student.NAME
 ,RN = ROW_NUMBER() OVER (
  PARTITION BY [StudentID] ORDER BY createddate
  )
 ,createddate
FROM dbo.Student

DECLARE @temp2 TABLE (
 ID INT
 ,StudentID INT
 ,olddate DATETIME
 ,newdate DATETIME
 ,[Difference_InDays] INT
 );

;WITH cte as(
SELECT ROW_NUMBER() OVER(ORDER BY c1.StudentID) AS ID ,c1.StudentID,
       olddate=CASE WHEN c1.createddate=c2.createddate THEN NULL ELSE C1.createddate END,
       newdate=CASE WHEN c1.createddate=c2.createddate THEN NULL ELSE C2.createddate END
FROM @temp1 c1
INNER JOIN @temp1 c2 ON c1.[StudentID] = c2.[StudentID]
 AND c2.RN = c1.RN + 1
 )
INSERT INTO @temp2 (
  ID
 ,StudentID
 ,olddate
 ,newdate
 ,[Difference_InDays]
 )
SELECT *
 ,DATEDIFF(DD, c.olddate, c.newdate) AS [Difference_InDays]
FROM cte c

SELECT *
FROM @temp2 t 

Result:

Calcualte difference in months:

DECLARE @temp1 TABLE (
 StudentID INT
 ,NAME VARCHAR(50)
 ,RN INT
 ,createddate DATETIME
 )

INSERT INTO @temp1 (
  StudentID
 ,NAME
 ,RN
 ,createddate
 )
SELECT [StudentID]
 ,dbo.Student.NAME
 ,RN = ROW_NUMBER() OVER (
  PARTITION BY [StudentID] ORDER BY createddate
  )
 ,createddate
FROM dbo.Student

DECLARE @temp2 TABLE (
 ID INT
 ,StudentID INT
 ,olddate DATETIME
 ,newdate DATETIME
 ,[Difference_InMonth] INT
 );

;WITH cte as(
SELECT ROW_NUMBER() OVER(ORDER BY c1.StudentID) AS ID ,c1.StudentID,
       olddate=CASE WHEN c1.createddate=c2.createddate THEN NULL ELSE C1.createddate END,
       newdate=CASE WHEN c1.createddate=c2.createddate THEN NULL ELSE C2.createddate END
FROM @temp1 c1
INNER JOIN @temp1 c2 ON c1.[StudentID] = c2.[StudentID]
 AND c2.RN = c1.RN + 1
 )
INSERT INTO @temp2 (
  ID
 ,StudentID
 ,olddate
 ,newdate
 ,[Difference_InMonth]
 )
SELECT *
 ,DATEDIFF(MM, c.olddate, c.newdate) AS [Difference_InMonth]
FROM cte c

SELECT *
FROM @temp2 t

Result: