SELECT
  P.CaseID,
  P.ClientID,
  P.FinNumber,
  P.ClientName,
  P.ExternalCaseID,
  P.OpposingParty,
  P.CaseCategoryName,
  P.CaseStatusName,
  P.CaseClosed,
  P.AssignedTo,
  P.ForumName,
  P.MaxCaseActionID,
  S4.OptionDescription AS LastAction,
  IF((Z.DateDiarised IS NULL) OR (Z.DateDiarised = 0), '', DATE_FORMAT(FROM_UNIXTIME(Z.DateDiarised), '%Y-%m-%d')) AS LastActionDate,
  P.DiarisedDate,
  P.SetdownDate
FROM (

    SELECT
      CA.CaseID,
      CA.ClientID,
      CS.FinNumber,
      CS.GroupName AS ClientName,
      CA.ExternalCaseID,
      CA.OpposingParty,
      U1.FullName AS AssignedTo,
      S1.OptionDescription AS ForumName,
      S2.OptionDescription AS CaseCategoryName,
      S3.OptionDescription AS CaseStatusName,
      IF((CA.CaseClosedDate IS NULL) OR (CA.CaseClosedDate = 0), 'Open', 'Closed') AS CaseClosed,
      ( SELECT MAX(M.CaseActionID)
        FROM taCaseAction AS M
        WHERE
          (M.CaseID = CA.CaseID) AND
          (M.DateDiarised = (
            SELECT MAX(N.DateDiarised)
            FROM taCaseAction AS N
            WHERE (N.CaseID = M.CaseID)
          ))
      ) AS MaxCaseActionID,
      IF((CA.CaseDiarisedDate IS NULL) OR (CA.CaseDiarisedDate = 0), '', DATE_FORMAT(FROM_UNIXTIME(CA.CaseDiarisedDate), '%Y-%m-%d')) AS DiarisedDate,
      IF((CA.CaseSetdownDate IS NULL) OR (CA.CaseSetdownDate = 0), '', DATE_FORMAT(FROM_UNIXTIME(CA.CaseSetdownDate), '%Y-%m-%d')) AS SetdownDate
    FROM
      taCase AS CA
        LEFT JOIN taClients AS CS ON (CS.ClientID = CA.ClientID)
        LEFT JOIN taBranches AS BR ON (BR.BranchID = CA.LabourNetBranchID)
        LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = CA.ClientBranchID)
        LEFT JOIN taUsers AS U1 ON (U1.UserID = CA.AssignedToID)
        LEFT JOIN taUsers AS U2 ON (U2.UserID = CA.CreatedByID)
        LEFT JOIN taUsers AS U3 ON (U3.UserID = CA.RepresentativeID)
        LEFT JOIN taSystemOption AS S1 ON ((S1.OptionType = 'CASE-FORUM')    AND (S1.OptionID = CA.CaseForumID))
        LEFT JOIN taSystemOption AS S2 ON ((S2.OptionType = 'CASE-CATEGORY') AND (S2.OptionID = CA.CaseCategoryID))
        LEFT JOIN taSystemOption AS S3 ON ((S3.OptionType = 'CASE-STATUS')   AND (S3.OptionID = CA.CaseStatusID))
    WHERE
      1
      @pClientID
      @pCaseStatus
      @pFilter

  ) AS P
    LEFT JOIN taCaseAction AS Z ON (Z.CaseActionID = P.MaxCaseActionID)
    LEFT JOIN taSystemOption AS S4 ON ((S4.OptionType = 'CASE-ACTION-TYPE') AND (S4.OptionID = Z.ActionID))

