Conditional where statement in T-SQL

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

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

So if the value starts at 4, changes to 2, then again changes to 1. The records will go

1
4
2

I'm currently creating an inner join on the table to itself as follows, which gets the max date of the 3 above records, which would be 2.. The actual value I need is the 4. The easiest way to tell if a record is a historical one is that the 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 record, but only if it exists. So something along the lines of 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, record 2 is the one I'm after.

回答1:

Try this:

    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

回答2:

Use CASE in place of your pseudo-code:

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