SELECT
  CA.ClientID,
  CS.FinNumber,
  CS.GroupName,
  CA.CaseID,
  CA.CaseCategoryID,
  CA.OpposingParty,
  IF((CA.CaseClosedDate IS NULL) OR (CA.CaseClosedDate <= 0), 'Open', 'Closed') AS CaseStatus,
  S1.OptionDescription AS CaseStatusName,
  S2.OptionDescription AS CaseCategoryName,
  U1.FullName AS AssignedTo,
  CT.CaseActionID,
  CT.CreatedByID,
  DATE_FORMAT(FROM_UNIXTIME(CT.Created), '%Y-%m-%d') AS Created,
  CT.ActionID,
  CT.ActionedByID,
  DATE_FORMAT(FROM_UNIXTIME(CT.DateDiarised), '%Y-%m-%d') AS DateDiarised,
  DATE_FORMAT(FROM_UNIXTIME(CT.SetdownDate), '%Y-%m-%d') AS SetdownDate,
  CT.ActionComment,
  CT.Outsourced
FROM
  taCaseAction AS CT
    LEFT JOIN taCase AS CA ON (CA.CaseID = CT.CaseID)
    LEFT JOIN taUsers AS U1 ON (U1.UserID = CA.AssignedToID)
    LEFT JOIN taClients AS CS ON (CS.ClientID = CA.ClientID)
    LEFT JOIN taSystemOption AS S1 ON ((S1.OptionType = 'CASE-STATUS') AND (S1.OptionID = CA.CaseStatusID))
    LEFT JOIN taSystemOption AS S2 ON ((S2.OptionType = 'CASE-CATEGORY') AND (S2.OptionID = CA.CaseCategoryID))
WHERE
  (CaseActionID = @nCaseActionID)
