SELECT
  ProviderOffering.providerConcernRoleID,
  COUNT(Place.placeID)
INTO
  :providerConcernRoleID,
  :numberOfRecords 
FROM
  ProviderOffering,
  Reservation,
  Place 
WHERE Reservation.status = :reservationStatus 
  AND Reservation.fromDate <= :endDateTime 
  AND 
  (
    Reservation.toDate >= :startDateTime 
    OR Reservation.toDate IS NULL 
  ) 
  AND Reservation.providerOfferingID = ProviderOffering.providerOfferingID 
  AND 
  (
    
    (
      :searchByProvider = '0' 
      OR ProviderOffering.providerConcernRoleID IN 
      ( 
      SELECT
        Provider.providerConcernRoleID 
      FROM
        Provider 
      WHERE Provider.providerConcernRoleID = :providerConcernRoleID 
        AND Provider.recordStatus = :providerStatus 
      ) 
    ) 
    AND 
    (
      :searchByServiceOffering = '0' 
      OR ProviderOffering.providerConcernRoleID IN 
      ( 
      SELECT
        Provider.providerConcernRoleID 
      FROM
        Provider,
        ProviderOffering,
        ServiceOffering 
      WHERE ServiceOffering.serviceOfferingID = :serviceOfferingID 
        AND ServiceOffering.recordStatus = :activeStatus 
        AND ServiceOffering.serviceOfferingID = ProviderOffering.serviceOfferingID 
        AND ProviderOffering.recordStatus = :providerOfferingStatus 
        AND ProviderOffering.startDate <= :searchDate 
        AND 
        (
          ProviderOffering.endDate >= :searchDate 
          OR ProviderOffering.endDate IS NULL 
        ) 
        AND ProviderOffering.providerConcernRoleID = Provider.providerConcernRoleID 
        AND Provider.recordStatus = :providerStatus 
      ) 
    ) 
    AND 
    (
      :searchByProviderType = '0' 
      OR ProviderOffering.providerConcernRoleID IN 
      ( 
      SELECT
        Provider.providerConcernRoleID 
      FROM
        Provider,
        ProviderCategoryPeriod,
        ProviderType 
      WHERE ProviderType.type = :providerType 
        AND ProviderType.recordStatus = :activeStatus 
        AND ProviderType.providerCategoryID = ProviderCategoryPeriod.providerCategoryID 
        AND ProviderCategoryPeriod.recordStatus = :activeStatus 
        AND ProviderCategoryPeriod.startDate <= :searchDate 
        AND 
        (
          ProviderCategoryPeriod.endDate >= :searchDate 
          OR ProviderCategoryPeriod.endDate IS NULL 
        ) 
        AND ProviderCategoryPeriod.providerConcernRoleID = Provider.providerConcernRoleID 
        AND Provider.recordStatus = :providerStatus 
      ) 
    ) 
  ) 
  AND Reservation.placeID = Place.placeID 
  AND Place.placeID NOT IN 
  ( 
  SELECT
    Placement.placeID 
  FROM
    Placement 
  WHERE Placement.recordStatus = :activeStatus 
    AND Placement.startDate <= :endDateTime 
    AND 
    (
      Placement.endDate >= :startDateTime 
      OR Placement.endDate IS NULL 
    ) 
  ) 
GROUP
  BY ProviderOffering.providerConcernRoleID 
ORDER BY ProviderOffering.providerConcernRoleID