CODEWITHSUNDEEP
mysqlsql
Hiran
Hiran

Reputation: 287

MySQL: splitting result into two different columns

I have a result as follows

DATE        EID TIME        TYPE

2015-07-26  1   10:01:00    IN 
2015-07-26  1   15:01:00    OUT 
2015-07-26  1   18:33:00    IN 
2015-07-26  1   23:11:00    OUT

I want to split IN, OUT into different columns ORDER BY date, eid, time. expected result should be as follows

DATE        EID     IN TIME     OUT TIME

2015-07-26  1       10:01:00    15:01:00
2015-07-26  1       18:33:00    23:11:00

This is what I tried so far

SELECT `date` AS 'DATE', `eid` AS 'EID',
CASE WHEN `type` = 'IN' THEN `time` END AS 'IN TIME',
CASE WHEN `type` = 'OUT' THEN `time` END AS 'OUT TIME'
FROM `attendance`
ORDER BY `date`, `eid`, `time`;

It's fetching some ridiculous result as follows

DATE        EID     IN TIME     OUT TIME

2015-07-26  1       10:01:00    null
2015-07-26  1       null        15:01:00
2015-07-26  1       18:33:00    null
2015-07-26  1       null        23:11:00

UPDATED:

This is my table structure

Field       Type                Null    Key     Default     Extra

id      int(10) unsigned        NO      PRI     NULL        auto_increment
eid     int(10) unsigned        NO              NULL
time    time                    NO              00:00:00
date    date                    NO              0000-00-00
type    enum('IN', 'OUT')       NO              NULL
state   tinyint(1) unsigned     NO              1

Here is more tuples...

DATE        EID TIME        TYPE

2015-07-26  1   10:01:00    IN
2015-07-26  1   15:01:00    OUT
2015-07-26  1   18:33:00    IN
2015-07-26  1   23:11:00    OUT
2015-07-26  3   09:42:00    IN
2015-07-26  3   15:29:00    OUT
2015-07-26  3   18:20:00    IN
2015-07-26  3   00:34:00    OUT
2015-07-26  6   14:16:00    IN
2015-07-26  6   23:08:00    OUT
2015-07-26  8   13:32:00    IN
2015-07-26  8   23:57:00    OUT
2015-07-26  12  09:14:00    IN
2015-07-26  12  15:07:00    OUT
2015-07-26  12  17:28:00    IN
2015-07-26  12  23:53:00    OUT
2015-07-26  13  13:47:00    IN
2015-07-26  13  23:25:00    OUT
2015-07-26  15  11:07:00    IN
2015-07-26  15  19:50:00    OUT

Upvotes: 2

Views: 722

Answers (3)

Satender K
Satender K

Reputation: 581

I have written a query for you. I am hoping it will solve your problem :

SQL FOR TABLE SCHEMA

CREATE TABLE `attendance` (
  `date` date DEFAULT NULL,
  `eid` int(11) DEFAULT NULL,
  `time` time(6) DEFAULT NULL,
  `type` varchar(5) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

SQL Query FOR RESULT

I have assumed that there is an auto increment column available in your table as id

SELECT * FROM (
    SELECT a.`date` AS 'DATE', a.`eid` AS 'EID',
        CASE WHEN `type` = 'IN' THEN a.`time` END AS 'IN TIME',
        CASE WHEN `type` = 'IN' THEN 
            (select `time` from `attendance` where id > a.id AND `type` = 'OUT' LIMIT 1) END AS 'OUT TIME'
        FROM `attendance` a
        ORDER BY a.`date`, a.`eid`, a.`time`
) as t WHERE t.`IN TIME` IS NOT NULL;

Upvotes: 2

Satender K
Satender K

Reputation: 581

I have added another answer for it just to fix the issue what Taher is saying :

Query

SELECT * FROM(
SELECT a.`date` AS 'DATE', a.`eid` AS 'EID',
    CASE WHEN `type` = 'IN' THEN a.`time` END AS 'IN TIME',
    CASE WHEN `type` = 'IN' THEN 
        (select `time` from
            (SELECT `date`, `eid`, `time`,`type`, @rownum := @rownum + 1 as id FROM `attendance` cross join (select @rownum := 0) r ORDER BY `date`, `eid`, `time`) as b where (b.id > a.id) AND `type` = 'OUT' LIMIT 1)
         END AS 'OUT TIME'

    FROM(SELECT `date`, `eid`, `time`,`type`, @rownum := @rownum + 1 as id FROM `attendance` cross join (select @rownum := 0) r ORDER BY `date`, `eid`, `time`) as a
) as t WHERE t.`IN TIME` IS NOT NULL;

Upvotes: 0

Taher Rahgooy
Taher Rahgooy

Reputation: 6696

Assuming the data are valid (there is not more than one IN between two OUT), using window functions:

SELECT DATE, EID, `IN`, `OUT` FROM 
  (SELECT date AS 'DATE', eid AS 'EID', 
     (SELECT MAX(TIME) from attendance  where
            DATE = a.DATE AND TIME <= a.TIME AND TYPE = 'IN' AND EID = a.EID) as 'IN',
     (SELECT MAX(TIME) from attendance  where
            DATE = a.DATE AND TIME <= a.TIME AND TYPE = 'OUT' AND EID = a.EID) as 'OUT',
      TYPE           
   FROM attendance a
   WHERE TYPE = 'OUT'
   ORDER BY date, eid, time) t

here is a fiddle for it.

Edit: I did not check the EID, here is the new fiddle.

Upvotes: 1

Related Questions