Where clause in T-SQL

Question

I have a table that returns the history of the value, as well as the current one, each with a date.

The oldest date is the master record. If the value is changed, a new record is created with the old value, and the master record is updated with the new value. If this happens again, a third record is created, which now contains the old value.

So, if the value starts with 4, changes to 2, then again changes to 1. Records will go

1 4 2 

I am currently creating an inner join for a table as follows, which gets the maximum date of the three records above, which will be 2. The actual value I need is 4. The easiest way to tell if the record is historical, then TriageEndDateTime is NULL .

 INNER JOIN (SELECT EmergencyAttendanceId,MIN(SourceCreateDateTime) as Max_Date FROM FactEmergencyAttendanceTriageDetail GROUP BY EmergencyAttendanceId) AS EAiD ON EAiD.EmergencyAttendanceId = FactEmergencyAttendanceTriageDetail.EmergencyAttendanceId AND EAiD.Max_Date = FactEmergencyAttendanceTriageDetail.SourceCreateDateTime 

What I need to do is select the second entry, but only if it exists. So something like this.

  SELECT EmergencyAttendanceId,MIN(SourceCreateDateTime) as Max_Date FROM FactEmergencyAttendanceTriageDetail WHERE IF COUNT(EmergencyAttendanceId) > 1 THEN TriageEndDateTime Is NULL ELSE NOT NULL GROUP BY EmergencyAttendanceId inside the INNER JOIN. 

Can anyone help me with this?

Sample data

In the above case, entry 2 is the one I will be following.

Answer 1

Try the following:

  SELECT EmergencyAttendanceId case when count(EmergencyAttendanceId) > 1 then MIN ( case when TriageDateTime is null then SourceCreateDateTime end ) else min(SourceCreateDateTime) end as max_date FROM FactEmergencyAttendanceTriageDetail GROUP BY EmergencyAttendanceId 

Answer 2

Use CASE instead of your pseudocode:

 WHERE TriageEndDateTime = CASE WHEN EmergencyAttendanceId > 1 THEN NULL ELSE NOT NULL END