Scenario

Event – Parent Event
Sub-Event – Child Event

1.The Events contains multiple sub events.
2.When there is Parent event, there should be No sub event of that Parent event in that same period
3.There can me more than one sub event of the same Parent event in the same period.There can be overlapping sub event.

 SELECT COUNT(ParentEventId)    
    FROM Events
   WHERE lower(status) = 'open'
     AND ParentEventId = p_parent_id
      AND (ChildEvent is null or p_child_event IS NULL OR
          ChildEvent = NVL(p_child_event, child_event))
     AND (p_startdate BETWEEN StartDate AND
          EndDate OR
          p_admin_enddate BETWEEN StartDate AND
          EndDate OR
          (p_startdate <= StartDate AND
          p_enddate >= EndDate));