我使用INNER JOIN状态作为假设每个主记录都引用一个现有的状态记录。如果不是这种情况，您可能需要将其更改为LEFT JOIN。

对于WhenDate，您只需离开即可加入Secondary。如果找到一条记录，则可以与Secondary.WhenDate进行比较，否则，可以与Main.WhenDate进行比较。

SELECT
  m.ID as MainID,
  m.WhenDate as MainWhenDate, 
  m.InfoText, 
  m.StatusID,
  st.StatusText,
  m.TypeID,
  s.WhenDate as SecondaryWhenDate,
  CASE WHEN s.MainID IS NULL THEN 
    m.WhenDate 
  ELSE 
    s.WhenDate 
  END AS ActualWhenDate
FROM
  Main m
  INNER JOIN Status st ON st.ID = m.StatusID
  LEFT JOIN Secondary s ON s.MainID = m.ID
WHERE
  ( s.MainID IS NULL AND m.WhenDate = <YourDate>
    OR
    s.MainID IS NOT NULL AND s.WhenDate = <YourDate> )
  AND TypeId = <TypeFilter>
  AND ... other filters, if you need any ...