SELECT COUNT(*) FROM (select t.*,
(case
when ((to_date('2016/03/15 19:03', 'yyyy/mm/dd HH24:mi') between
to_date(t.S_time, 'yyyy/mm/dd HH:mi') and
to_date(t.e_time, 'yyyy/mm/dd HH24:mi'))
AND
(to_date('2016/03/15 19:10', 'yyyy/mm/dd HH24:mi') between
to_date(t.S_time, 'yyyy/mm/dd HH:mi') and
to_date(t.e_time, 'yyyy/mm/dd HH24:mi')))
OR
((to_date('2016/03/15 19:03', 'yyyy/mm/dd HH24:mi') between
to_date(t.S_time, 'yyyy/mm/dd HH:mi') and
to_date(t.e_time, 'yyyy/mm/dd HH24:mi'))
AND
(to_date('2016/03/15 19:10', 'yyyy/mm/dd HH24:mi') >
to_date(t.e_time, 'yyyy/mm/dd HH24:mi')))
OR
((to_date('2016/03/15 19:03', 'yyyy/mm/dd HH24:mi') <
to_date(t.e_time, 'yyyy/mm/dd HH24:mi'))
AND
(to_date('2016/03/15 19:10', 'yyyy/mm/dd HH24:mi') between
to_date(t.S_time, 'yyyy/mm/dd HH:mi') and
to_date(t.e_time, 'yyyy/mm/dd HH24:mi')))
OR
((to_date('2016/03/15 19:03', 'yyyy/mm/dd HH24:mi')<
to_date(t.S_time, 'yyyy/mm/dd HH:mi')
AND
(to_date('2016/03/15 19:10', 'yyyy/mm/dd HH24:mi') >
to_date(t.e_time, 'yyyy/mm/dd HH24:mi'))))
then
'1'
else
'0'
end) cc
from (select (to_char(l.applierdate, 'yyyy/mm/dd') || ' ' || l.starttime) S_time,
(to_char(l.applierdate, 'yyyy/mm/dd') || ' ' || l.endtime) e_time
from line l
where l.applierdate = to_date('2016/03/15', 'yyyy/mm/dd')
and l.empno = '9409872') t) M
WHERE M.CC='1'
其中 '2016/03/15'是參數DATE,
'2016/03/15 19:03'為開始時間,
'2016/03/15 19:10'為結束時間,
l.empno =:參數-- '9409872'
共分別為4種情況: