Self Left Join with no identitical row values

I am trying to create a self left join on a table which looks like below-

SystemID	UserID	DateTimeStamp	Entry
1234	ABC	29/07/2023 22:04:48	New Process :30/07/23
1232	ABC	29/07/2023 22:04:47	Task : PQR
1214	ABC	29/07/2023 22:04:42	Task : PQR
1211	ABC	29/07/2023 22:04:26	Task : PQR
1209	ABC	29/07/2023 22:03:56	New Process :30/07/23
1206	ABC	29/07/2023 22:03:56	Task : LMN
1201	ABC	29/07/2023 22:03:47	Task : LMN
1189	ABC	29/07/2023 22:03:26	Task : LMN
1167	DEF	29/07/2023 21:58:30	New Process :30/07/23
1166	DEF	29/07/2023 21:58:29	Task : ZZZ
1160	DEF	29/07/2023 21:58:23	Task : ZZZ
1154	DEF	29/07/2023 21:58:22	Task : ZZZ

The table says that SystemID 1234 is a New Process and is for the Task PQR. Similarly, SystemID 1209 is a New Process for Task LMN and SystemID 1167 is a New Process for Task ZZZ

Is there a way I can get the 'New Process' row with the top most 'Task'row and the related UserID and DateTimeStamp linked to it like below -

SystemID	UserID	DateTimeStamp	Entry	Task	TaskDateTime	TaskUserID
1234	ABC	29/07/2023 22:04:48	New Process:30/07/23	PQR	29/07/2023 22:04:48	ABC
1209	ABC	29/07/2023 22:03:56	New Process:30/07/23	LMN	29/07/2023 22:03:56	ABC
1167	DEF	29/07/2023 21:58:30	New Process:30/07/23	ZZZ	29/07/2023 21:58:39	DEF

Is there any way I can achieve the above result set?

Solution

You can use a APPLY() operator

select *
from   table1 t
       outer apply
       (
           select top 1 *
           from   table1 x
           where  x.UserID = t.UserID
           and    x.DateTimeStamp < t.DateTimeStamp
           order by x.DateTimeStamp desc
       ) a
where  t.Entry like 'New Process%'

Or row_number()

with cte as
(
  select *, rn = row_number() over(partition by UserID order by DateTimeStamp desc)
  from   table1
)
select *
from   cte c1
       left join cte c2 on  c2.UserID = c1.UserID
                        and c2.rn     = c1.rn + 1
where  c1.Entry like 'New Process%'

Or lead() or lag() window function

with cte as
(
  select *, 
         prevEntry = lead(Entry) over(partition by UserID order by DateTimeStamp desc),
         prevDT    = lead(DateTimeStamp) over(partition by UserID order by DateTimeStamp desc)
  from   table1
)
select *
from   cte c
where  c.Entry like 'New Process%'