SELECT
  P.CaseID,
  P.ClientID,
  P.FinNumber,
  P.ClientName,
  P.LabourNetBranchName,
  P.ExternalCaseID,
  P.OpposingParty,
  P.ForumName,
  P.CaseCategoryName,
  P.CaseStatusName,
  P.CaseClosed,
  IF(TRIM(COALESCE(Z.ActionComment, '')) = '',
       TRIM(COALESCE(P.CaseComment, '')),
       IF(TRIM(COALESCE(P.CaseComment, '')) = '',
            TRIM(COALESCE(Z.ActionComment, '')),
            CONCAT(TRIM(COALESCE(P.CaseComment, '')), '<br />', TRIM(COALESCE(Z.ActionComment, '')))
         )
    ) AS CaseComment,
  P.CostsAwarded,
  P.CaseOutcomeName,
  P.OutcomeAmount,
  P.LitigationRep,
  P.AssignedTo,
  P.MaxCaseActionID,
  S6.OptionDescription AS LastAction,
  P.CreateDate,
  P.DiarisedDate,
  P.SetdownDate,
  IF((Z.DateDiarised IS NULL) OR (Z.DateDiarised = 0), '', DATE_FORMAT(FROM_UNIXTIME(Z.DateDiarised), '%Y-%m-%d')) AS LastActionDate,
  P.CaseClosedDate
FROM (

    SELECT
      CA.CaseID,
      CA.ClientID,
      CS.FinNumber,
      CS.GroupName AS ClientName,
      BR.BranchName AS LabourNetBranchName,
      CA.ExternalCaseID,
      CA.OpposingParty,
      S1.OptionDescription AS ForumName,
      S2.OptionDescription AS CaseCategoryName,
      S3.OptionDescription AS CaseStatusName,
      IF(CA.CaseClosedDate IS NULL, 'Open', 'Closed') AS CaseClosed,
      CA.CaseComment,
      CA.CostsAwarded,
      S4.OptionDescription AS CaseOutcomeName,
      S5.OptionDescription AS LitigationRep,
      CA.OutcomeAmount,
      U1.FullName AS AssignedTo,
      ( 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.Created IS NULL) OR (CA.Created = 0), '', DATE_FORMAT(FROM_UNIXTIME(CA.Created), '%Y-%m-%d')) AS CreateDate,
      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,
      IF((CA.CaseClosedDate IS NULL) OR (CA.CaseClosedDate = 0), '', DATE_FORMAT(FROM_UNIXTIME(CA.CaseClosedDate),   '%Y-%m-%d')) AS CaseClosedDate
    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))
        LEFT JOIN taSystemOption AS S4 ON ((S4.OptionType = 'CASE-OUTCOME')        AND (S4.OptionID = CA.CaseOutcomeTypeID))
        LEFT JOIN taSystemOption AS S5 ON ((S5.OptionType = 'CASE-LITIGATION-REP') AND (S5.OptionID = CA.LitigationRepID))
    WHERE
      1
      @pBranchID
      @pCaseType
      @pLitigationRepID
      @pAllocatedConsultantID
      @pAssignedToID
      @pClientID
      @pCaseForumID
      @pCaseCategoryID
      @pCaseStatusID
      @pCaseOpenClosed
      @pCaseOutcomeTypeID
      @pCreatedFromTo
      @pDiarisedFromTo
      @pSetdownFromTo
      @pFilter

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