SQL Server Self Join to Calculate the Age of an Incident

How to Calculate the Age of an Incident in SQL Server
Suppose we the Incidents data as follows..
Select * From [dbo].[Tbl_Incidents_All]

Now from this Data, if we wants to know/track the Age of an Incident/Case, the duration between the date of the latest Incident Status and  its the Open date of an Incident.

We can calculate using the below Self Join query as follows:

Select S1.[Incident Id],S1.[Incident Status], S2.[Inc_Open_Date],S2.[Inc_Cur_Date],

DATEDIFF(dd,S2.[Inc_Open_Date],S2.[Inc_Cur_Date]) [Inc_Age_Days]
From [dbo].[Tbl_Incidents_All] S1
Left Join (Select [Incident Id],Min([Incident Date]) [Inc_Open_Date],

                Max([Incident Date]) [Inc_Cur_Date]
    From  [dbo].[Tbl_Incidents_All]  Group by [Incident Id] ) S2
 On S1.[Incident Id]=S2.[Incident Id] and S1.[Incident Date]=S2.[Inc_Cur_Date]
 Where S2.[Inc_Cur_Date] IS NOT NULL

The result as follows..

Further, we can calculate the Incident Age in Hours, Minutes as follows...

Select S1.[Incident Id],S1.[Incident Status], S2.[Inc_Open_Date],S2.[Inc_Cur_Date],
CAST(DATEDIFF(dd, S2.[Inc_Open_Date], S2.[Inc_Cur_Date]) AS VARCHAR) [Inc_Age_Days],
CAST(DATEDIFF(hh, S2.[Inc_Open_Date], S2.[Inc_Cur_Date]) AS VARCHAR) [Inc_Age_Hours],
CAST(DATEDIFF(mi, S2.[Inc_Open_Date], S2.[Inc_Cur_Date]) AS VARCHAR) [Inc_Age_Minutes],
CAST(DATEDIFF(ss, S2.[Inc_Open_Date], S2.[Inc_Cur_Date]) AS VARCHAR) [Inc_Age_Seconds]
From [dbo].[Tbl_Incidents_All] S1
Left Join (Select [Incident Id],Min([Incident Date]) [Inc_Open_Date],

                Max([Incident Date]) [Inc_Cur_Date]
    From  [dbo].[Tbl_Incidents_All]  Group by [Incident Id] ) S2
 On S1.[Incident Id]=S2.[Incident Id] and S1.[Incident Date]=S2.[Inc_Cur_Date]
 Where S2.[Inc_Cur_Date] IS NOT NULL

Result:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------