How many days / hours / minutes a record is in a particular state

Transact-SQL
1

Hey Experts,
Is it possible fetch from the database, we are still in dev state to create requirements that can determine how many DAYS, HOURS, MIN a particular record is in a particular state
CustomerID is the primary Key, We have various Complaint Type (No_service, Delayed_Response, NotSatisfiedwithFix, etc) and we have StatusID( ['Not Started',
'Completed',
'Done',
'In Progress',
'Ready',
'Started',
'Void']

I am writing requirements that want to know the workflow and time taken and one of the requirements is to Identify Each Complaint is on what state and how long before DONE.
Status Transition Time

What I know:
These API data is pushed to EDW every day certain time (not sure) but not in real time but is reflected in the application database instantly and in real time.

  1. Please may I know if I can accept this as feasible before I discuss with the Report Developers?
  2. Any sample code that you can help me to see how this can be fetched from database?
  3. Really appreciate if you can give me some links to read further as I have similar requirement that I need to document and I need to map to tables and write the calculations involved.

Is this a good link, that satisfies my needs?

Many Regards and Thanks

2

The date when the status is changed should be available in the API, the date in the EDW (Enterprise Data Warehouse (?)) is not accurate if it's loaded and should not be used as business data.

You can use the function LEAD/LAG to calculate the difference in time between status.

DROP TABLE IF EXISTS #Temp 

SELECT
	1 AS CustomerID,
	'Not Started' AS StatusID,
	'2022-11-23 12:00' AS DateTime
	INTO #Temp
UNION ALL
SELECT
	2 AS CustomerID,
	'Not Started' AS StatusID,
	'2022-11-23 13:00' AS DateTime
UNION ALL
SELECT
	1 AS CustomerID,
	'Started' AS StatusID,
	'2022-11-23 12:30' AS DateTime
UNION ALL
SELECT
	2 AS CustomerID,
	'Started' AS StatusID,
	'2022-11-23 14:30' AS DateTime
UNION ALL
SELECT
	1 AS CustomerID,
	'Done' AS StatusID,
	'2022-11-24 14:30' AS DateTime;

SELECT * FROM #Temp;
	
WITH CustomerStatus AS
(
SELECT 
	CustomerID,
	StatusID,
	[DateTime],
	LAG([DateTime],1,[DateTime]) OVER (PARTITION BY CustomerID ORDER BY DateTime) AS PrevDateTime,
	LAG(StatusID,1,[DateTime]) OVER (PARTITION BY CustomerID ORDER BY DateTime) AS PrevStatus	
FROM
	#Temp
)
SELECT 
CustomerID, 
StatusID, 
[DateTime], 
PrevDateTime, 
DATEDIFF(MINUTE,PrevDateTime,[DateTime]) AS DurationMinutes,
DATEDIFF(HOUR,PrevDateTime,[DateTime]) AS DurationHours
FROM CustomerStatus;