How to join tables when there are multiple entries of the same value in SQL Server on the right table?

Question

I want to measure the lead time for each stage of order fulfillment in store and these lead times should be available in an existing view.

The view has several columns and entries, but to simplify consider these two columns and this specific entry:

ORDER_NR	Id
SE2844697	3824

And on the table [OnlineFulFillmentActionLogs] I have the different status according to each stage of the order fulfillment:

ACTION_DATE	OnlineFulFillmentId	Status
2023-04-18 12:27:31.0000000	3824	0
2023-04-18 12:43:20.0000000	3824	1
2023-04-18 12:43:46.0000000	3824	3
2023-04-18 12:46:26.0000000	3824	3
2023-04-18 12:46:26.0000000	3824	5

There are cases with more than one entry for the same status. I can solve this with a min(Action_Date), to get the date of the first occurrence. I can also calculate the lead time for each stage with this query:

ISNULL(DATEDIFF(HOUR,Column1,Column2),0) AS [LEAD_TIME_FIRST_STAGE] where,

• Column1 would be the Action_Date of the status=0;
• Column2 would be the Action_Date of the status=1, if existing;

But I'm struggling to figure out how to join this right table [OnlineFulFillmentActionLogs] to the existing view, since I have multiple entries with the same value OnlineFulFillmentId=3824 and I want to calculate the lead time for each status (from status 0 to status 1, from status 1 to status 2, from status 2 to status 3,...)

Expected result:

ORDER_NR	Id	LEAD_TIME_FIRST_STAGE	LEAD_TIME_SECOND_STAGE	LEAD_TIME_THIRD_STAGE	...
SE2844697	3824	16	...	...	...

Answer 1

First, the next and current state is specified. If the next record is equal to the specified next state, it is calculated

If the time Status columns are dynamic, you should use sql dynamic calculate the time with window function and then it is used pivot

--ORDER_NR  Id  LEAD_TIME_FIRST_STAGE   LEAD_TIME_SECOND_STAGE  LEAD_TIME_THIRD_STAGE
;with _listsatus as(
         select 0 as currentstatus,1 nextstatus union 
         select 1 as currentstatus,2 nextstatus union 
         select 2 as currentstatus,3 nextstatus union 
         select 3 as currentstatus,4 nextstatus union 
         select 4 as currentstatus,5 nextstatus union 
         select 5 as currentstatus,6 nextstatus
 )
SELECT 
        ORDER_NR
        ,id
        ,ISNULL(cast( [0] as varchar(100)),'-') LEAD_TIME_FIRST_STAGE
        ,ISNULL(cast([1] as varchar(100)),'-') LEAD_TIME_SECOND_STAGE
        ,ISNULL(cast( [2] as varchar(100)),'-') LEAD_TIME_three_STAGE
        ,ISNULL(cast( [3] as varchar(100)),'-') LEAD_TIME_Four_STAGE
        ,ISNULL(cast( [4] as varchar(100)),'-') LEAD_TIME_Five_STAGE
        ,ISNULL(cast( [5] as varchar(100)),'-') LEAD_TIME_Sex_STAGE
        
FROM   
(
     
             select ORDER_NR,id,Status, case 
                        when   exists(  select * 
                                       from _listsatus  
                                       where currentstatus=Status   and nextstatus= nStatus 
                                     ) then
                    ISNULL(DATEDIFF(MINUTE, ACTION_DATE,nACTION_DATE ),0) else null end AS [DATEDIFF_STAGE]         
             from (
             select ORDER_NR,id,Status,
             ACTION_DATE,
        
                   LEAD(Status) over(partition by a.OnlineFulFillmentId order by Status) as nStatus
                    , LEAD(ACTION_DATE) over(partition by a.OnlineFulFillmentId order by ACTION_DATE) as nACTION_DATE
                    from OnlineFulFillmentActionLogs a
                    inner join OnlineFulFillment b on a.OnlineFulFillmentId=b.Id
            )a
) t 
PIVOT(
    sum([DATEDIFF_STAGE]) 
    FOR Status IN (
        [0], 
        [1], 
        [2],
        [3],
        [4],
        [5])
) AS pivot_table;

Base Data:

create table OnlineFulFillment
(ORDER_NR varchar(100), Id int)
insert into OnlineFulFillment values('SE2844697','3824')

create table OnlineFulFillmentActionLogs(
ACTION_DATE datetime,   OnlineFulFillmentId int,    Status int)
insert into OnlineFulFillmentActionLogs values('2023-04-18 12:27:31',   3824,   0)
insert into OnlineFulFillmentActionLogs values('2023-04-18 12:43:20',   3824,   1)
insert into OnlineFulFillmentActionLogs values('2023-04-18 12:43:46',   3824,   3)
insert into OnlineFulFillmentActionLogs values('2023-04-18 12:46:26',   3824,   3)
insert into OnlineFulFillmentActionLogs values('2023-04-18 12:46:26',   3824,   5)