SELECT
  audDate,
  audOperator,
  CASE WHEN (audType = 'I') THEN
    'Insert'
  ELSE
    CASE WHEN (audType = 'D') THEN
      'Delete'
    ELSE
      CASE WHEN (audType = 'E') THEN
        'Update'
      ELSE
        CONCAT('Unknown Type ', audType)
      END
    END
  END AS audType,
  CASE audTable
    WHEN 'taClientBranches'       THEN (SELECT CONCAT('(', CS.ClientID, ') ', GroupName) FROM taClients AS CS LEFT JOIN taClientBranches AS CB ON (CB.ClientID = CS.ClientID) WHERE (CB.ClientBranchID  = X.audKey))
    WHEN 'taClientCover'          THEN (SELECT CONCAT('(', CS.ClientID, ') ', GroupName) FROM taClients AS CS LEFT JOIN taClientCover    AS CV ON (CV.ClientID = CS.ClientID) WHERE (CV.ClientCoverID   = X.audKey))
    WHEN 'taClientProducts'       THEN (SELECT CONCAT('(', CS.ClientID, ') ', GroupName) FROM taClients AS CS LEFT JOIN taClientProducts AS CP ON (CP.ClientID = CS.ClientID) WHERE (CP.ClientProductID = X.audKey))
    WHEN 'taClientContacts'       THEN (SELECT CONCAT('(', CS.ClientID, ') ', GroupName) FROM taClients AS CS LEFT JOIN taClientContacts AS CC ON (CC.TypeID   = CS.ClientID) WHERE (CC.ContactID       = X.audKey))
    WHEN 'taClientBranchProducts' THEN (SELECT CONCAT('(', CS.ClientID, ') ', GroupName) FROM taClients AS CS LEFT JOIN taClientBranches AS CB ON (CB.ClientID = CS.ClientID) LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientBranchID = CB.ClientBranchID) WHERE (BP.ClientBranchProductID = X.audKey))
    WHEN 'taClientProductFees'    THEN (SELECT CONCAT('(', CS.ClientID, ') ', GroupName) FROM taClients AS CS LEFT JOIN taClientBranches AS CB ON (CB.ClientID = CS.ClientID) LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientBranchID = CB.ClientBranchID) LEFT JOIN taClientProductFees AS PF ON (PF.ClientBranchProductID = BP.ClientBranchProductID) WHERE (PF.ClientProductFeeID = X.audKey))
    ELSE '-'
  END AS audParent,
  audTable,
  audField,
  audKey,
  audPre,
  audPost
FROM (
  SELECT * FROM tbAudit WHERE
    1
    @pProcess
    @pFromTo
    @pAuditTable
    @pAuditField
    @pAuditKey
    @pFilter
    @pOperator
    @pShowAppointments
  UNION
  SELECT * FROM cms_archive.tbAudit WHERE
    1
    @pProcess
    @pFromTo
    @pAuditTable
    @pAuditField
    @pAuditKey
    @pFilter
    @pOperator
    @pShowAppointments
) AS X
ORDER BY 1 DESC