Saturday, 13 October 2018

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

The result as follows..




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

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts