Count the number of days an order has been at a specific status

Question

I can't figure out how to write a query that returns the results I need.

Below is a table that list in chronological order the status of an order on specific days and time.

An order can change from one status to another but can also change back to a status it was at previously.

How do I write a query that will give me the following for each time an order changes a status (minimum days is 1):

select doc_no, doc_type, line_no, status, DATEDIFF(day, '04/18/2023', '05/10/2023') AS   [number of days at this status before changing]

Data:

doc_no	doc_type	line_no	status	updated date	time
2353021	SA	21000	527	2023-02-17 00:00:00.000	100640
2353021	SA	21000	527	2023-03-16 00:00:00.000	114555
2353021	SA	21000	527	2023-03-23 00:00:00.000	101018
2353021	SA	21000	527	2023-03-23 00:00:00.000	145322
2353021	SA	21000	545	2023-03-23 00:00:00.000	145323
2353021	SA	21000	555	2023-03-23 00:00:00.000	145411
2353021	SA	21000	555	2023-04-14 00:00:00.000	113735
2353021	SA	21000	555	2023-04-14 00:00:00.000	115644
2353021	SA	21000	555	2023-04-14 00:00:00.000	115826
2353021	SA	21000	527	2023-04-14 00:00:00.000	122233
2353021	SA	21000	527	2023-04-14 00:00:00.000	190943
2353021	SA	21000	527	2023-04-18 00:00:00.000	32939
2353021	SA	21000	527	2023-04-18 00:00:00.000	161039
2353021	SA	21000	545	2023-04-18 00:00:00.000	161046
2353021	SA	21000	555	2023-04-18 00:00:00.000	161102
2353021	SA	21000	555	2023-04-18 00:00:00.000	163512
2353021	SA	21000	555	2023-05-09 00:00:00.000	134853
2353021	SA	21000	555	2023-05-10 00:00:00.000	140516

The results I am looking for:

doc_no	doc_type	line_no	status	number_of_days_at_status
2353021	SA	2100	527	27
2353021	SA	2100	545	1
2353021	SA	2100	555	22
2353021	SA	2100	527	4
2353021	SA	2100	545	1
2353021	SA	2100	555	22

Answer 1

Below query will help you to get your desired results : see sqlfiddle

Idea is to use the LAG() window function to get the previous row's updated_date and then calculate the number of days at each status using DATEDIFF() and filter the results to show only rows where the status has changed for a minimum of one day.

 WITH CTE AS (
  SELECT 
    doc_no,
    doc_type,
    line_no,
    [status],
    updated_date,
    LAG(updated_date) OVER (PARTITION BY doc_no, line_no ORDER BY updated_date, [time]) AS prev_updated_date
  FROM order_status
)
SELECT 
  doc_no,
  doc_type,
  line_no,
  [status],
  DATEDIFF(day, prev_updated_date, updated_date) AS number_of_days_at_status
FROM CTE
WHERE prev_updated_date IS NOT NULL
  AND DATEDIFF(day, prev_updated_date, updated_date) >= 1
ORDER BY updated_date;