Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Location
    pune
    Posts
    16

    Question Unanswered: Need sql help !!

    hii guys...
    Please help me on this issue ..
    This is my query...

    $qry="SELECT id,WorkID,GROUP_CONCAT(Recordtime) as times,Recorddate FROM attendance_logs GROUP BY WorkID ORDER BY id ";
    $output=mysql_query($qry,$conn);
    while($employee_details=mysql_fetch_assoc($output) )
    {
    $details[]=$employee_details;
    }
    output looks like this..
    Array
    (
    [0] => Array
    (
    [id] => 2
    [WorkID] => 849
    [times] => 09:39:16,09:32:00,09:29:45,09:24:07
    [Recorddate] => 2012-01-21
    )

    [1] => Array
    (
    [id] => 5
    [WorkID] => 845
    [times] => 09:09:43
    [Recorddate] => 2012-01-02
    )

    [2] => Array
    (
    [id] => 6
    [WorkID] => 844
    [times] => 09:49:17
    [Recorddate] => 2012-01-02
    )

    [3] => Array
    (
    [id] => 11
    [WorkID] => 109
    [times] => 09:42:49,14:13:00,09:43:40,09:12:37,09:58:53,09:42 :17
    [Recorddate] => 2012-01-02
    )

    I want to calculate times( from times key, daily punches of an employee) of every employee inside office and outside office like ...

    (first + second) punch times == inside office
    (second + third) punch times = outside office

    i want these calculations to show on report page...
    I hope you guys understand....Pleease help me how to do these calcualtions in php... !!

    Thanks in advance .
    Kiran

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Do you want to do this in PHP or SQL? Also looking at your data above 09:39:16,09:32:00,09:29:45,09:24:07 and 09:42:49,14:13:00,09:43:40,09:12:37,09:58:53,09:42 :17 should the times be ordered? The rule you provided does not appear to work on this data.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jan 2012
    Location
    pune
    Posts
    16
    Hii Ronan,

    I want to do this on PHP and all those data (times) should be in ordered ...


    Thanks,

    Kiran

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Firstly to order the times use GROUP_CONCAT(Recordtime ORDER BY Recordtime). This should return the data ordered in the list for you. Secondly you will need to clearly explain what happens if there is more than 4 entries and how these should be interpreted? Also what happens if someone checks in on one day and then checks out just after midnight. This will cause problems in your logic as you will not know whether the person checked in the next day or checked out first.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Jan 2012
    Location
    pune
    Posts
    16

    Arrow

    Yes Ronan, i used group_concat(Recordtime) to get times in order and there's no limit for an employee, he can punch as many as he can ( Like, first punch means he's inside and second punch means outside office and so on)..

    Now i want to calculate how much time he's inside and outside office ...

    (For every punch, Recordtime wil get inserted in attendance table.... now i want to get that data from table to show in report page every punch( Recordtime ) in seperate column ) ...


    Also what happens if someone checks in on one day and then checks out just after midnight. This will cause problems in your logic as you will not know whether the person checked in the next day or checked out first.

    Ahh actually we are developing code for general shift at present (Our client company has general shift only).... and yes we are developing code for that also ..

    Cheers

Posting Permissions

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