Search code examples
sqlsql-serversql-updatesubquerywindow-functions

SQL Server partition and Run Case Expression

I am trying to get the latest records in TableB for an ID .. I tired to rank the top value and then using the results to apply the following conditions to the column called 'Status' in TableA:

  • If end is null or blank then status = 'Currently Running'

  • If the latest record is from the past 48 hours then status = 'Recently Finished'

  • If the end was more than 48 hours ago then status = 'not run in more than 48hours'

  • else 'no recent activity'

I have attached a screenshot of the data.

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
) 
GO

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'Z1003', 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'Z2001')
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'Z2001')
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')

I tried to write a SQL Partition statement and then the CASE but it's all going wrong.

WITH cte AS (
  SELECT a.ID, Status,
    ROW_NUMBER() OVER (PARTITION BY b.db_addr, b.ID ORDER BY b.[End] DESC) AS rn,
    MAX(b.[End]) OVER (PARTITION BY b.db_addr, b.ID) AS latest_end
  FROM TableA a
  JOIN TableB b ON a.ID = b.ID
)
UPDATE a
SET Status = CASE
  WHEN cte.latest_end IS NULL OR cte.latest_end = '' THEN 'Currently Discharging'
  WHEN cte.rn = 1 AND cte.latest_end >= DATEADD(hour, -48, GETDATE()) THEN 'Discharged recently'
  ELSE 'Not discharged in more than 48hours'
  END
FROM TableA a
LEFT JOIN cte ON a.ID = cte.ID
WHERE a.ID IS NOT NULL;
Solution

  • to retrieve the most recent row for each ID, use the window method ROW_NUMBER(), and then use case clause to get the expected result :

    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

    This is the update statement :

    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

    Result :

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

    Demo here