SQL Server partition and Run Case Expression

eufgjt7s 于 2023-06-04 发布在 SQL Server

关注(0)|答案(2)|浏览(147)

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;

sql-server

来源：https://stackoverflow.com/questions/76231505/sql-server-partition-and-run-case-expression

2条答案

按热度按时间

px9o7tmv1#

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

赞(0）回复(0）举报 2023-06-04

gev0vcfq2#

It does not look like you need window functions here. You can get the latest end of each id from the second table with just a max .

We can also avoid the CTE (that your code later joins back to the table), and return the new value directly from a correlated subquery:

update a
set status = (
    select 
        case
            when max([End]) is null then 'currently running'
            when max([End]) >= dateadd(day, -2, getdate()) then 'recently finished'
            else 'did not run in more than 48 hours'
        end   
    from tableb b
    where b.id = a.id
)
from tablea a
where a.id is not null

Notes:

the last branch of your case (the one that yields no recent activity) is unreachable ; the previous branch, did not run in more than 48 hours, would match first
this also updates rows that have no match at all in the second table, and will give them status did not run in more than 48 hours - which to me seems consistent with your problem statement and attempt

赞(0）回复(0）举报 2023-06-04

我来回答

SQL Server partition and Run Case Expression

2条答案

相关问题

热门标签

最新问答