Conditionally select a date depending on what statuses exist

Question

I need to select the InsertedDate for StatusKey=OCM based on 4 possible scenarios (see inline comments). In my example, I'm using a temp table just to simulate the data that I can find in the real dbo.history table.

I have to return the InsertedDate for each individual ProgramID. So in my example, I should be getting

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

I'm stuck, and I cannot make a good use of the CASE WHEN. How could I solve this?

Here's the setup query

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

Here's my initial query

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

user_1127428 · Answer

I think the trick is to calculate for each ProgramId the various values you want to compare in a sub-query (CTE) and then build a CASE expression which evaluates your conditions in order of priority.

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;

Returns as requested:

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

Conditionally select a date depending on what statuses exist

1 Answers