SELECT
  1 AS CoverType,
  COUNT(*) AS AppointmentCount,
  SUM(AP.CoverUsed) AS CoverUsed
FROM
  tbAppointments AS AP
    INNER JOIN taClientBranchProducts AS BP ON (BP.ClientBranchProductID = AP.ClientBranchProductID)
    INNER JOIN taClientProducts       AS CP ON (CP.ClientProductID       = BP.ClientProductID)
WHERE
  (AP.AppointmentStage = 'Authorised') AND
  (AP.DontDeductFromCover = 0) AND
  (AP.BillingDate BETWEEN @aValueStartDate AND @aValueEndDate) AND
  (CP.ClientProductID = @nClientProductID @pParentClientProductID)
  @pExcludeAppointment

UNION

SELECT
  3 AS CoverType,
  COUNT(*) AS AppointmentCount,
  SUM(AP.FreeHoursUsed) AS CoverUsed
FROM
  tbAppointments AS AP
    INNER JOIN taClientBranchProducts AS BP ON (BP.ClientBranchProductID = AP.ClientBranchProductID)
    INNER JOIN taClientProducts       AS CP ON (CP.ClientProductID       = BP.ClientProductID)
WHERE
  (AP.AppointmentStage = 'Authorised') AND
  (AP.DontDeductFromCover = 0) AND
  (AP.BillingDate BETWEEN @aHoursStartDate AND @aHoursEndDate) AND
  (CP.ClientProductID = @nClientProductID)
  @pExcludeAppointment

UNION

SELECT
  4 AS CoverType,
  COUNT(*) AS AppointmentCount,
  SUM(AP.FreeConsultationUsed) AS CoverUsed
FROM
  tbAppointments AS AP
    INNER JOIN taClientBranchProducts AS BP ON (BP.ClientBranchProductID = AP.ClientBranchProductID)
    INNER JOIN taClientProducts       AS CP ON (CP.ClientProductID       = BP.ClientProductID)
WHERE
  (AP.AppointmentStage = 'Authorised') AND
  (AP.DontDeductFromCover = 0) AND
  (AP.BillingDate BETWEEN @aVisitsStartDate AND @aVisitsEndDate) AND
  (CP.ClientProductID = @nClientProductID)
  @pExcludeAppointment

ORDER BY 1