WITH T ( SKey, FrDt, ToDt , acd ,bcd ) AS ( SELECT '100' , '20160701' , '20160715' , '10' , '11' FROM DUAL UNION ALL SELECT '100' , '20160801' , '20160815' , '10' , '11' FROM DUAL UNION ALL SELECT '101' , '20160725' , '20160728' , '10' , '11' FROM DUAL ) SELECT COUNT(*) FROM T WHERE SKey = '100' AND ( ( '20160710' BETWEEN FrDt AND ToDt ) OR ( '20160728' BETWEEN FrDt AND ToDt ) ) ;