Date time help please

[vissu pangam]

Hi All,

Please help me for this.

I have a data like below.
Now I want to show the employees who are in store based on the dataetime given.
Here the emp came on 9am and left at 11 am.And again he cam on 12.30 pm and left at 19 pm .I gave datatime as 2014-05-15 09:15:45 .
For this time the
employee is in store but I am not able to fetch the data.

But I am unable to get the data.
Please help me

Empno ClockDate ClockTime ClockType
1 2014-05-15 2014-05-15 09:00:00 I
1 2014-05-15 2014-05-15 11:00:00 O
1 2014-05-15 2014-05-15 12:30:00 I
1 2014-05-15 2014-05-15 19:00:00 O


This is my query :

SELECT DISTINCT a.emp_a,clock_date_1,a.clock_time,b.clock_time FROM
(SELECT MAX(clock_time) clock_time,employee_no AS emp_a,clock_date clock_date_1 FROM clock_time WHERE
clock_type='I' GROUP BY clock_type,emp_a,clock_date )a,
(SELECT MAX(clock_time) clock_time,employee_no AS emp_b FROM clock_time WHERE clock_type='O' GROUP BY clock_type,emp_b)b
WHERE '2014-05-23 09:15:45' BETWEEN a.clock_time AND b.clock_time
AND a.emp_a=b.emp_b
AND a.emp_a=1