根据存在的状态有条件地选择日期

Question

我需要根据4种可能的情况为StatusKey=OCM选择InsertedDate(参见内联注释)。在我的示例中，我使用临时表只是为了模拟我可以在真实的dbo.history表中找到的数据。

我必须为每个单独的ProgramID返回InsertedDate。在我的例子中，我应该得到

ProgramID InsertedDate
1        2022-01-01 10:00:00
4        2022-01-01 10:30:00
8        2022-01-05 07:00:00
12        2022-01-06 07:00:00 

我卡住了，我不能很好地使用CASE WHEN。我怎么解决这个问题呢?

下面是setup查询

DROP TABLE IF EXISTS #TestHistory
CREATE TABLE #TestHistory
(
ProgramID int,
InsertedDate datetime,
StatusKey nvarchar(10),
UpdatedBy nvarchar(10)
)
INSERT INTO #TestHistory
(ProgramID, InsertedDate, StatusKey, UpdatedBy)
VALUES
(1, '2022-01-01 08:00:00', 'PER', 'User'),
(1, '2022-01-01 10:00:00', 'OCM', 'User'), --< When there's an OCM with a date greater than an IPC, select the first OCM InsertedDate
(1, '2022-01-02 08:00:00', 'IPC', 'User'),
(1, '2022-01-03 07:00:00', 'OCM', 'User'),
(4, '2022-01-01 09:59:00', 'PER', 'User'),
(4, '2022-01-01 10:30:00', 'OCM', 'User'), --< when there's an OCM with a date greater than a C, select the first OCM InsertedDate
(4, '2022-01-02 08:00:00', 'IPC', 'User'),
(4, '2022-01-03 07:00:00', 'C',   'User'),
(4, '2022-01-04 10:00:00', 'OCM', 'User'),
(8, '2022-01-02 08:00:00', 'PER', 'User'),
(8, '2022-01-03 07:00:00', 'OCM', 'User'),
(8, '2022-01-04 08:00:00', 'PER', 'System'),
(8, '2022-01-05 07:00:00', 'OCM', 'User'), --< when there's an OCM with a date greater than PER updatedby SYSTEM select max OCM date
(12, '2022-01-02 08:00:00', 'PER', 'User'),
(12, '2022-01-03 07:00:00', 'OCM', 'User'),
(12, '2022-01-04 08:00:00', 'PER', 'User'),
(12, '2022-01-06 07:00:00', 'OCM', 'User') --< when there's an OCM with a date greater than PER updatedby USER select max OCM date

这是我的初始查询

--SELECT 
--CASE
--  WHEN (SELECT MIN(a.InsertedDate) AS 'OCM') > 'C' THEN 'OCM' ELSE 'C' END

-- FROM (
SELECT
h.ProgramID,
h.InsertedDate,
h.StatusKey,
h.UpdatedBy,
ROW_NUMBER() OVER (PARTITION BY h.ProgramID, h.StatusKey ORDER BY h.InsertedDate DESC) rownum
FROM #TestHistory h 
--) A

Answer 1

我认为诀窍是为每个ProgramId计算你想在子查询(CTE)中比较的各种值，然后构建一个CASE表达式，按优先级顺序评估你的条件。

with cte as (
select ProgramID
, min(case when StatusKey = 'OCM' then InsertedDate else null end) MinOcm
, max(case when StatusKey = 'OCM' then InsertedDate else null end) MaxOcm
, max(case when StatusKey = 'IPC' then InsertedDate else null end) MaxIpc
, max(case when StatusKey = 'PER' then InsertedDate else null end) MaxPer -- Seems not to matter about the user
, max(case when StatusKey = 'C' then InsertedDate else null end) MaxC
from #TestHistory
group by ProgramID
)
select ProgramId
, case
when MaxIpc is not null and MaxOcm > MaxIpc then MinOcm
when MaxC is not null and MaxOcm > MaxC then MinOcm
when MaxPer is not null and MaxOcm > MaxPer then MaxOcm
end InsertedDate
from cte
order by ProgramId;

返回请求:

tbody> <<tr>48

ProgramId	InsertedDate
1	2022-01-01 10:00:00.000
	2022-01-01 10:30:00.000
	2022-01-05 07:00:00.000
12	2022-01-06 07:00:00.000