Results 1 to 2 of 2

Thread: Time range help

  1. #1
    Join Date
    Mar 2013
    Posts
    25

    Unanswered: Time range help

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Not tested on MySQL.
    (tested on DB2)

    Code:
    SELECT employee_no
         , clock_date
         , in_clock_time
         , out_clock_time
     FROM  (SELECT employee_no
                 , clock_date
                 , clock_time  AS in_clock_time
                 , (SELECT MIN(clock_time)
                       FROM  clock_time AS ot
                       WHERE ot.clock_type  = 'O'
                        AND  ot.employee_no = ip.employee_no
                        AND  ot.clock_time  > ip.clock_time
                     ) AS out_clock_time
             FROM  clock_time AS ip
             WHERE clock_type  = 'I'
              AND  employee_no = 1
           )
     WHERE '2014-05-15 09:15:45'
           BETWEEN in_clock_time
               AND out_clock_time
    ;

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •