SELECT
  @nProductID AS ProductID,
  AT.AttendanceTypeID,
  AT.ProductCategory,
  PR.ProductCategory AS ProductProductCategory,
  AT.AppointmentType,
  AT.AlwaysAllowed,
  IF(AT.AlwaysAllowed, 1, IF(AE.AttendanceTypeID IS NULL, 1, 0)) AS AttendanceAllowed,
  COALESCE(IF(AT.BillingRule = 1, IF((CT.CoverTypeID IS NULL) OR (CT.CoverTypeID < 3), 3, CT.CoverTypeID), CT.CoverTypeID), 0) AS CoverTypeID,
  ( CASE AT.UnitOfMeasureID
      WHEN 1 THEN 'Days'
      WHEN 2 THEN 'Hours'
      WHEN 3 THEN 'Sessions'
      WHEN 4 THEN 'Delegates'
      WHEN 5 THEN 'Half Days'
      ELSE AT.UnitOfMeasureID
    END
  ) AS NrOfUnitsDesc,
  AT.BillingRule,
  AT.IsService
FROM
  taProducts AS PR,
  tbAppointmentTypes AS AT
    LEFT JOIN taAttendanceExclusion AS AE ON ((AE.ProductID = @nProductID) AND (AE.AttendanceTypeID = AT.AttendanceTypeID))
    LEFT JOIN taCoverType AS CT ON ((CT.ProductID = @nProductID) AND (CT.AttendanceTypeID = AT.AttendanceTypeID))
WHERE
  (PR.ProductID = @nProductID) AND
  ((PR.ProductCategory = AT.ProductCategory) OR (PR.ProductCategory = @aAdHocProductCategory)) AND
  ((AT.BillingRule <> 1) OR (PR.ProductCategory <> @aAdHocProductCategory)) AND
  (AT.IsActive = 1) AND
  (TRIM(AT.AppointmentType) <> '')
ORDER BY
  AT.ProductCategory,
  AT.BillingRule,
  AT.AttendanceTypeID
