SELECT
  CA.CaseID,
  CA.CaseType,
  IF((CA.CaseClosedDate IS NULL) OR (CA.CaseClosedDate <= 0), 'Open', 'Closed') AS CaseStatus,
  S4.OptionDescription AS LitigationRepName,
  S2.OptionDescription AS CaseCategoryName,
  S1.OptionDescription AS CaseStatusName,
  U1.FullName AS AllocatedConsultant,
  U2.FullName AS AssignedTo,
  CA.ClientID,
  CA.ClientBranchID,
  CA.ExternalCaseID,
  CA.CreatedByID,
  CA.CaseForumID,
  CA.OpposingParty,
  CA.RepresentativeID,
  IF((CA.Created IS NULL) OR (CA.Created <= 0), '', DATE_FORMAT(FROM_UNIXTIME(CA.Created), '%Y-%m-%d')) AS Created,
  IF((CA.CaseDiarisedDate IS NULL) OR (CA.CaseDiarisedDate <= 0), '', DATE_FORMAT(FROM_UNIXTIME(CA.CaseDiarisedDate), '%Y-%m-%d')) AS CaseDiarisedDate,
  IF((CA.CaseSetdownDate IS NULL) OR (CA.CaseSetdownDate <= 0), '', DATE_FORMAT(FROM_UNIXTIME(CA.CaseSetdownDate), '%Y-%m-%d')) AS CaseSetdownDate,
  CA.ContactFirstName,
  CA.ContactSurname,
  CA.ContactTitle,
  CA.ContactEmail,
  CA.ContactAreaCode,
  CA.ContactTelephone,
  CA.ClientOrderNumber,
  CA.AllocatedConsultantID,
  CA.LabourNetBranchID,
  CA.AssignedToID,
  CA.LitigationRepID,
  CA.ClientBranchID,
  CA.CaseCategoryID,
  CA.CaseStatusID,
  IF((CA.CaseClosedDate IS NULL) OR (CA.CaseClosedDate <= 0), '', DATE_FORMAT(FROM_UNIXTIME(CA.CaseClosedDate), '%Y-%m-%d')) AS CaseClosedDate,
  CA.OutcomeAmount,
  CA.CostsAwarded,
  CA.CaseComment,
  CA.CaseOutcomeTypeID,
  CA.CaseOutcomeTypeBasisID AS CaseOutcomeWonID,
  CA.CaseOutcomeTypeBasisID AS CaseOutcomeLostID,
  CA.CaseOutcomeTypeBasisID AS CaseOutcomeSettledID,
  CA.CaseOutcomeTypeBasisID AS CaseOutcomeWithdrawnID,
  CA.CaseOutcomeTypeBasisID AS CaseOutcomeDismissedID,
  CA.CaseOutcomeTypeBasisID AS CaseOutcomeNotAttendID,
  CA.CaseOutcomeTypeBasisID AS CaseOutcomeConcludedID,
  CA.CaseOutcomeTypeDescriptionID AS CaseOutcomeLostProcID,
  CA.CaseOutcomeTypeDescriptionID AS CaseOutcomeLostSubsID,
  CA.CaseOutcomeTypeDescriptionID AS CaseOutcomeLostBothID,
  CA.CaseOutcomeTypeDescriptionID AS CaseOutcomeSettledAllID
FROM
  taCase AS CA
    LEFT JOIN taUsers AS U1 ON (U1.UserID = CA.AllocatedConsultantID)
    LEFT JOIN taUsers AS U2 ON (U2.UserID = CA.AssignedToID)
    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))
    LEFT JOIN taSystemOption AS S3 ON ((S3.OptionType = 'CASE-STATUS')    AND (S3.OptionID = CA.CaseStatusID))
    LEFT JOIN taSystemOption AS S4 ON ((S4.OptionType = 'CASE-LITIGATION-REP') AND (S4.OptionID = CA.LitigationRepID))
WHERE
  (CaseID = @nCaseID)
