How to find multiple records(attendance) of same date with alias of same table in MySql

Question

I have a table named rjs_attendance with following four column

_________________________________________________
|attenedance_id | admin_id | note | created_date|
-------------------------------------------------

A user can make attendance several times in a day. Odd entry is assumed as sign in and even entry is assumed as sign out. The output I need looks something like this.

_______________________________________________________________________________  
|admin_id   | time_in  | time_in_note  | time_out | time_out_note | date      |
-------------------------------------------------------------------------------
|1          |10:00     | none          | 11:00    | none          | 2015-12-24|
-------------------------------------------------------------------------------
|1          |11:30     |none           |12:15     |none           | 2015-12-24|
-------------------------------------------------------------------------------

I'm not able to fetch all record of the same date, but I'm able to fecth one record of the same date. The query I have run is as follows:

SELECT 
    `atd_in`.`admin_id` AS `admin_id`,
    CAST(MIN(`atd_in`.`created_date`) AS TIME) AS `time_in`,
    `atd_in`.`note` AS `time_in_note`,
    CAST(MAX(`atd_out`.`created_date`) AS TIME) AS `time_out`,
    `atd_out`.`note` AS `time_out_note`,
    CAST(`atd_in`.`created_date` AS DATE) AS `date_on` 
FROM 
    `zf2`.`rjs_attendance` `atd_in` 
    LEFT JOIN `zf2`.`rjs_attendance` `atd_out` 
        ON
            `atd_in`.`admin_id` = `atd_out`.`admin_id`
            AND CAST(`atd_in`.`created_date` AS DATE) = CAST(`atd_out`.`created_date` AS DATE) 
            AND `atd_in`.`attendance_id` <> `atd_out`.`attendance_id`
GROUP BY 
    CAST(`atd_in`.`created_date` AS DATE), `atd_in`.`admin_id`

Any help is greatly appreciated.

provide some sample data (rjs_attendance) it should have both odd and even numbers of entries to cater for your use cases (but it does not have to be a large volume of data) — Paul Maxwell, Dec 24 '15 at 11:25
It looks to me like it should return all pairs of rows on the same date. BTW, you can use `DATE(created_date)` and `TIME(created_date)` to get the time and date out of a datettime. — Barmar, Dec 24 '15 at 11:27
@Used_By_Already please find the some record in image: http://s11.postimg.org/ke2dkduqr/Untitled.png — Ahmad Asjad, Dec 24 '15 at 11:31
The problem is with using `MIN()` and `MAX()`. That just gives you one row for each `admin_id` and date. — Barmar, Dec 24 '15 at 11:34
@Barmar Please find this link for some data and structure: http://sqlfiddle.com/#!9/14ff5/1 — Ahmad Asjad, Dec 24 '15 at 11:36

Saharsh Shah · Accepted Answer · 2015-12-24T11:42:17.017

3

Try this:

SELECT A.admin_id, 
       MAX(IF(A.ID % 2 = 1, CAST(A.created_date AS TIME), NULL)) AS time_in, 
       MAX(IF(A.ID % 2 = 1, note, NULL)) AS time_in_note, 
       MAX(IF(A.ID % 2 = 0, CAST(A.created_date AS TIME), NULL)) AS time_out, 
       MAX(IF(A.ID % 2 = 0, note, NULL)) AS time_out_note, 
       CAST(A.created_date AS DATE) AS date_on 
FROM (SELECT IF(@adminId=@adminId:=A.admin_id, @id:=@id+1, @id:=1) AS ID, 
             A.admin_id, A.note, A.created_date 
      FROM zf2.rjs_attendance A, (SELECT @id:=1, @adminId:=0) AS B
      ORDER BY A.admin_id, A.attendance_id
    ) AS A
GROUP BY A.admin_id, CAST(A.created_date AS DATE), CEILING(A.ID / 2);

sqlfiddle

edited Dec 24 '15 at 11:42

answered Dec 24 '15 at 11:29

Saharsh Shah

28,687
8
48
83

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS time_in, MAX(IF(A.ID % 2 = 1, note, NULL) AS time_in_note, ' at line 2 – Ahmad Asjad Dec 24 '15 at 11:40
1

I do it all the time, too. – Barmar Dec 24 '15 at 11:47
1

Thanks @SaharshShah and Barmar – Ahmad Asjad Dec 24 '15 at 11:48

score 1 · Answer 2 · answered Dec 24 '15 at 11:31

1

before seeing any code - if you assume odd and even as entry and exit => join on this condition -

on A.id +1 = B.id

or

`atd_in`.`attendance_id` +1 = `atd_out`.`attendance_id`

you can even leave it left joined for non exit entries (ids should be numerical of course)

if your records are not sorted this way, just reissue the ids column after sorting with order by and make new id column

answered Dec 24 '15 at 11:31

Zahiro Mor

1,708
1
16
30

1

This table contains multiple user, so it is possible that all the entries for admin user not in sequence – Saharsh Shah Dec 24 '15 at 11:33
Anyway I go the answer of @SaharshShah. But thanks for your effort – Ahmad Asjad Dec 24 '15 at 11:49
1

Fine :) @SaharshShah answer is far more complete of course, although i would suggest to try it after re-issuing ids... it will make the code a lot clearer and compact imho – Zahiro Mor Dec 24 '15 at 11:56

How to find multiple records(attendance) of same date with alias of same table in MySql

2 Answers2