I have a table like below
ID ProductId Status Time
---------------------------------------
1 1 Open 27/01/2025 6:30
2 1 Closed 27/01/2025 7:30
3 2 Open 27/01/2025 7:35
4 3 Open 27/01/2025 7:45
5 4 Open 27/01/2025 8:45
6 1 Open 27/01/2025 8:55
7 2 Closed 28/01/2025 7:30
8 4 Closed 28/01/2025 8:30
9 1 Closed 28/01/2025 8:30
10 1 Open 28/01/2025 9:30
11 3 Closed 28/01/2025 9:35
12 4 Open 28/01/2025 9:45
I would like to find the open time and its related closed time of the given product id like below
ProductId OpenTime CloseTime
-----------------------------------------------
1 27/01/2025 6:30 27/01/2025 7:30
1 27/01/2025 8:55 28/01/2025 8:30
1 28/01/2025 9:30
2 27/01/2025 7:35 28/01/2025 7:30
3 27/01/2025 7:45 28/01/2025 9:35
4 27/01/2025 8:45 28/01/2025 8:30
4 28/01/2025 9:45
You can also take advantage of the FIRST_VALUE function to find the first value for the product where Status='Closed' following the row where Status='Open' e.g.
with cte as (
select ProductId, [Status], StatusTime
, StatusTime OpenTime
, first_value(case when [Status] = 'Closed' then StatusTime else null end)
over (partition by ProductId order by StatusTime asc rows between 1 following and unbounded following) CloseTime
from dbo.ProductStatus
)
select ProductId, OpenTime, CloseTime
from cte
where [Status] = 'open'
order by ProductId, StatusTime asc;
Returns, as requested, for the provided sample data:
| ProductId | OpenTime | CloseTime |
|---|---|---|
| 1 | 2025-01-27 06:30:00 | 2025-01-27 07:30:00 |
| 1 | 2025-01-27 08:55:00 | 2025-01-28 08:30:00 |
| 1 | 2025-01-28 09:30:00 | NULL |
| 2 | 2025-01-27 07:35:00 | 2025-01-28 07:30:00 |
| 3 | 2025-01-27 07:45:00 | 2025-01-28 09:35:00 |
| 4 | 2025-01-27 08:45:00 | 2025-01-28 08:30:00 |
| 4 | 2025-01-28 09:45:00 | NULL |
However this could fail if you ever have a mismatched number of open and close events, in which case if you were running SQL Server 2022 you could use the IGNORE NULLS to correct this.