SQL How to add a conditional to the aggregate function within a pivot table?

Question

I tried to create a pivot table for all the relevant timestamps in this database, but discovered that the MAX() function in the aggregate statement of the pivot table was returning values that I didn't necessarily want. I'm trying to track timestamps to look at bottlenecks and average times between events.

For this database there is a record for every event and each event is timestamped. Certain events can be "cleared", but these are recorded as events with their own timestamps as well (they get marked as a "cleared" = 1 event). Let's say an order is completed, and then cleared--when I pull the MAX(EventTimeStamp) for that order, I will get the cleared timestamp which can be misleading when I try to look at the data. I instead want to get a NULL value returned.

Here is what the original data looks like:

Case	State	EventTimeStamp	Cleared
1	OrderReceived	2024-03-20 12:20	0
1	Completed	2024-03-20 12:51	0
1	Completed	2024-03-20 12:55	1
2	OrderReceived	2024-03-21 10:00	0
2	Completed	2024-03-22 08:00	0

There are maybe 25 different states that I'd like to track for many different cases which is why I pivoted it to look like this

Case	OrderReceived	Completed
1	2024-03-20 12:20	2024-03-20 12:55
2	2024-03-21 10:00	2024-03-22 08:00

For these cases I'd like to have a table that looks like this. Since the last Completed Event was a "Cleared" event I'd like it to read as NULL:

Case	OrderReceived	Completed
1	2024-03-20 12:20	NULL
2	2024-03-21 10:00	2024-03-22 08:00

Here is the condensed query for the pivot table:

SELECT [CaseID]
    ,[OrderReceived]
    ,[Completed]

FROM (SELECT [CaseID]
        ,[Cleared]
        ,[State]
        ,[EventTimeStamp]   
    FROM CaseTracking
        ) AS SourceTable

PIVOT (
    MAX(EventTimeStamp)
    FOR [State] IN ([OrderReceived]
    ,[Completed])) AS PivotTable

I'm not sure how to go about this to be honest. I thought about using a CASE statement to pair with the MAX() function, but I don't know how to evaluate the Cleared variable that is paired with the MAX(EventTimeStamp).

I do know that in layman's terms my conditional statement is something like: IF the "Cleared" value for the MAX(EventTimeStamp) = 0 THEN return MAX(EventTimeStamp). IF the "Cleared" value for the MAX(EventTimeStamp) = 1 THEN return NULL.

Any guidance would be much appreciated!

Answer 1

I used the recommendations from SQL Server - PIVOT on CASE statement and used '1990-1-1' as a temp stand in for NULL.

CREATE TABLE CaseTracking 
(
    [Case]  VARCHAR(512),
    [State] VARCHAR(512),
    EventTimeStamp  VARCHAR(512),
    Cleared VARCHAR(512)
);

INSERT INTO CaseTracking ([Case], [State], EventTimeStamp, Cleared) VALUES
    ('1', 'OrderReceived', '2024-03-20 12:20', '0'),
    ('1', 'Completed', '2024-03-20 12:51', '0'),
    ('1', 'Completed', '2024-03-20 12:55', '1'),
    ('2', 'OrderReceived', '2024-03-21 10:00', '0'),
    ('2', 'Completed', '2024-03-22 08:00', '0');

SELECT * FROM CaseTracking


SELECT [CASE]    
      ,MAX([OrderReceived]) as [OrderReceived] 
      ,CASE MIN([Completed]) WHEN '1990-1-1' THEN NULL ELSE MIN([Completed]) END as [Completed]
FROM
(
  
SELECT [Case]
    ,[OrderReceived]
    ,[Completed]

FROM (SELECT [Case]
        ,[Cleared]
        ,[State]
        ,[EventTimeStamp]  
        ,CASE [CLEARED]  WHEN 1 THEN '1990-1-1' ELSE [EventTimeStamp] END as ets
     
    FROM CaseTracking
        ) AS SourceTable

PIVOT (
    MAX(ets)
    FOR [State] IN ([OrderReceived]
    ,[Completed])) AS PivotTable

) t
GROUP BY [CASE]

fiddle

CASE	OrderReceived	Completed
1	2024-03-20 12:20	null
2	2024-03-21 10:00	2024-03-22 08:00