SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[TableA](
    [ID] [varchar](50) NULL,
    [Status] [varchar](50) NULL
);
CREATE TABLE [dbo].[TableB](
    [SiteId] [varchar](50) NULL,
    [db_addr] [varchar](50) NULL,
    [Start] [datetime] NULL,
    [End] [datetime] NULL,
    [ID] [varchar](50) NULL
);
    
INSERT [dbo].[TableA] ([ID], [Status]) VALUES (N'Z1001', N'');
INSERT [dbo].[TableA] ([ID], [Status]) VALUES (N'Z1002', N'');
INSERT [dbo].[TableA] ([ID], [Status]) VALUES (N'Z3001', N'');
INSERT [dbo].[TableA] ([ID], [Status]) VALUES (N'Z3002', N'');
INSERT [dbo].[TableB] ([SiteId], [db_addr], [Start], [End], [ID]) VALUES (N'1001', N'E001',   CAST(N'2023-05-01T00:00:00.000' AS DateTime), CAST(N'2023-05-02T00:10:00.000' AS DateTime), N'Z1001');
INSERT [dbo].[TableB] ([SiteId], [db_addr], [Start], [End], [ID]) VALUES (N'1001', N'E001', CAST(N'2023-05-02T01:00:00.000' AS DateTime), CAST(N'2023-05-02T01:10:00.000' AS DateTime), N'Z1001');

    INSERT [dbo].[TableB] ([SiteId], [db_addr], [Start], [End], [ID]) VALUES (N'1001', N'E001', CAST(N'2023-05-03T01:00:00.000' AS DateTime), CAST(N'2023-05-03T01:10:00.000' AS DateTime), N'Z1001');

    INSERT [dbo].[TableB] ([SiteId], [db_addr], [Start], [End], [ID]) VALUES (N'2001', N'B002',     CAST(N'2023-05-01T00:00:00.000' AS DateTime), CAST(N'2023-05-02T00:10:00.000' AS DateTime), N'Z1002');

    INSERT [dbo].[TableB] ([SiteId], [db_addr], [Start], [End], [ID]) VALUES (N'2001', N'B002', CAST(N'2023-05-02T01:00:00.000' AS DateTime), CAST(N'2023-05-02T01:10:00.000' AS DateTime), N'Z1002');
    
    INSERT [dbo].[TableB] ([SiteId], [db_addr], [Start], [End], [ID]) VALUES (N'3001', N'B005', CAST(N'2023-05-02T01:00:00.000' AS DateTime), NULL, N'Z3001');
    
    INSERT [dbo].[TableB] ([SiteId], [db_addr], [Start], [End], [ID]) VALUES (N'3002', N'C007', CAST(N'2023-05-10T01:00:00.000' AS DateTime), CAST(N'2023-05-10T01:00:00.000' AS DateTime), N'Z3002');
    
11 rows affected

with cte as (
  select a.*, b.[End], ROW_NUMBER() OVER (partition by a.ID order by [End]) as rn
  from TableA a
  inner join TableB b on b.ID = a.ID
)
select ID, case when [End] is null then 'Currently Running'
          when [End] >= DATEADD(day, -2, GETDATE()) then 'Recently FInished'
          when [End] < DATEADD(day, -2, GETDATE()) then 'not run in more than 48hours'
          else 'no recent activity' end as Status
from cte
where rn = 1


ID
Status


Z1001
not run in more than 48hours

Z1002
not run in more than 48hours

Z3001
Currently Running

Z3002
Recently FInished


with cte as (
  select a.*, b.[End], ROW_NUMBER() OVER (partition by a.ID order by [End]) as rn
  from TableA a
  inner join TableB b on b.ID = a.ID
)
UPDATE TableA
set TableA.Status = case when [End] is null then 'Currently Running'
          when [End] >= DATEADD(day, -2, GETDATE()) then 'Recently FInished'
          when [End] < DATEADD(day, -2, GETDATE()) then 'not run in more than 48hours'
          else 'no recent activity' end
from cte c
inner join TableA a on a.ID = c.ID
where rn = 1
4 rows affected

select *
from TableA


ID
Status


Z1001
not run in more than 48hours

Z1002
not run in more than 48hours

Z3001
Currently Running

Z3002
Recently FInished
ID	Status
Z1001	not run in more than 48hours
Z1002	not run in more than 48hours
Z3001	Currently Running
Z3002	Recently FInished