How to compare 2 row data value in single column in mysql

Question

╔═══════╦═══════════╦═══════╦══════╦═══════════════╗
║ EMPID ║   DATE    ║ TIME  ║ TYPE ║    STATUS     ║
╠═══════╬═══════════╬═══════╬══════╬═══════════════╣
║     5 ║ 2012-03-1 ║ 09:30 ║ in   ║ Ontime        ║
║     5 ║ 2012-03-1 ║ 14:00 ║ out  ║ Early Leaving ║
║     6 ║ 2012-03-1 ║ 09:30 ║ in   ║ Ontime        ║
║     6 ║ 2012-03-1 ║ 17:40 ║ out  ║ Ontime        ║
║     7 ║ 2012-03-1 ║ 10:30 ║ in   ║ LateComing    ║
║     7 ║ 2012-03-1 ║ 13:00 ║ out  ║ EarlyLeaving  ║
╚═══════╩═══════════╩═══════╩══════╩═══════════════╝

I'm having employee checkin and check out status now what i need is, i want to compare value of status column and display the result

Example using case statement in select:

when Min(status)='Ontime' and max(status)='Ontime' then 'Present'

when Min(status)='Ontime' and max(status)='Early Leaving' then 'Left Early'

I need final output as:

╔═══════╦═══════════╦═══════╦══════╦═══════════════╦════════════╗
║ EMPID ║   DATE    ║ TIME  ║ TYPE ║    STATUS     ║ DAYSTATUS  ║
╠═══════╬═══════════╬═══════╬══════╬═══════════════╬════════════╣
║     5 ║ 2012-03-1 ║ 09:30 ║ in   ║ Ontime        ║            ║
║     5 ║ 2012-03-1 ║ 14:00 ║ out  ║ Early Leaving ║ Left Early ║
║     6 ║ 2012-03-1 ║ 09:30 ║ in   ║ Ontime        ║            ║
║     6 ║ 2012-03-1 ║ 17:40 ║ out  ║ Ontime        ║ present    ║
║     7 ║ 2012-03-1 ║ 10:30 ║ in   ║ LateComing    ║            ║
║     7 ║ 2012-03-1 ║ 13:00 ║ out  ║ EarlyLeaving  ║ Absent     ║
╚═══════╩═══════════╩═══════╩══════╩═══════════════╩════════════╝

Answer 1

Try it using case and variables

     select @empId:=EmpId as EmpId,@date:=date as Date, time, type,@status:=status as status,
   case type 
     when 'out'
        then
         case(select status from mytable where empid = @empId AND date = @date AND type = 'in')
         when 'Ontime' then
            case(@status)
              when 'EarlyLeaving' then 'EarlyLeaving'
              when 'ontime'       then 'Present'
             end
          when 'LateComing' then
             case(@status)
              when 'EarlyLeaving' then 'Absent'
             end
         end 
     when 'in' then ''
  end as daystatus    
from mytable

SQL FIDDLE DEMO

OUTPUT

╔═══════╦═══════════╦═══════╦══════╦══════════════╦══════════════╗
║ EMPID ║   DATE    ║ TIME  ║ TYPE ║    STATUS    ║  DAYSTATUS   ║
╠═══════╬═══════════╬═══════╬══════╬══════════════╬══════════════╣
║     5 ║ 2012-03-1 ║ 09:30 ║ in   ║ Ontime       ║              ║
║     5 ║ 2012-03-1 ║ 14:00 ║ out  ║ EarlyLeaving ║ EarlyLeaving ║
║     6 ║ 2012-03-1 ║ 09:30 ║ in   ║ Ontime       ║              ║
║     6 ║ 2012-03-1 ║ 17:40 ║ out  ║ Ontime       ║ Present      ║
║     7 ║ 2012-03-1 ║ 10:30 ║ in   ║ LateComing   ║              ║
║     7 ║ 2012-03-1 ║ 13:00 ║ out  ║ EarlyLeaving ║ Absent       ║
╚═══════╩═══════════╩═══════╩══════╩══════════════╩══════════════╝

Answer 2

Try this logic

when TYPE = 'out' and STATUS ='Early Leaving' then 'Left Early'

when TYPE = 'out' and STATUS ='Ontime' then 'present'

Else 'Absent'

Answer 3

SELECT  a.*,
        CASE WHEN a.type = 'IN' THEN ''
            ELSE
            CASE WHEN b.IN = 'Ontime' AND b.Out = 'Ontime' THEN 'Present'
                WHEN b.IN = 'Ontime' AND b.Out = 'Early Leaving' THEN 'Left Early'
                ELSE 'Absent'
            END
        END DAYSTATUS
FROM    tableName a
        INNER JOIN
        (
            SELECT  EmpID,
                    MAX(CASE WHEN Type = 'IN' THEN status END) `IN`,
                    MAX(CASE WHEN Type = 'OUT' THEN status END) `OUT`
            FROM    TableName
            GROUP   BY EmpID
        ) b ON a.EmpID = b.EmpID 

• SQLFiddle Demo

OUTPUT

╔═══════╦═══════════╦═══════╦══════╦═══════════════╦════════════╗
║ EMPID ║   DATE    ║ TIME  ║ TYPE ║    STATUS     ║ DAYSTATUS  ║
╠═══════╬═══════════╬═══════╬══════╬═══════════════╬════════════╣
║     5 ║ 2012-03-1 ║ 09:30 ║ in   ║ Ontime        ║            ║
║     5 ║ 2012-03-1 ║ 14:00 ║ out  ║ Early Leaving ║ Left Early ║
║     6 ║ 2012-03-1 ║ 09:30 ║ in   ║ Ontime        ║            ║
║     6 ║ 2012-03-1 ║ 17:40 ║ out  ║ Ontime        ║ Present    ║
║     7 ║ 2012-03-1 ║ 10:30 ║ in   ║ LateComing    ║            ║
║     7 ║ 2012-03-1 ║ 13:00 ║ out  ║ EarlyLeaving  ║ Absent     ║
╚═══════╩═══════════╩═══════╩══════╩═══════════════╩════════════╝