employee_id logdatetime         tkstype
  PH120013  2012-08-25 08:30:00.000 0
  PH120013  2012-08-25 13:00:00.000 1
  PH120013  2012-08-26 02:30:00.000 0
  PH120013  2012-08-27 15:00:00.000 1
  PH120013  2012-08-27 17:00:00.000 0
  PH120013  2012-08-27 21:00:00.000 1
  PH120013  2012-08-28 08:00:00.000 0
  PH120013  2012-08-28 19:00:00.000 1
  PH120013  2012-08-29 03:30:00.000 0
  PH120013  2012-08-29 21:00:00.000 1
  PH120013  2012-08-30 05:30:00.000 0
  PH120013  2012-08-30 19:00:00.000 1
  PH120013  2012-08-31 05:30:00.000 0
  PH120013  2012-08-31 20:30:00.000 1
  PH120013  2012-09-01 01:00:00.000 1
  PH120013  2012-09-01 21:00:00.000 0

IN                              OUT

;With Attendance_CTE As
(

        SELECT employee_id,CONVERT(VARCHAR(10), logdatetime, 120) logdate,
               CONVERT(VARCHAR(12), logdatetime, 114) logtime,logdatetime,tkstype
        FROM MSTR_ATTENDANCE WHERE CONVERT(VARCHAR(10), logdatetime, 120) >='2012-08-26'

),
Employee_CTE As
(
    SELECT employee_id,
       '2012-08-26' + CONVERT(datetime,tks_ofc_in,108) as Official_IN,
       '2012-08-26' + CONVERT(datetime,tks_ofc_out,108) as Official_OUT,
       hireddate + '00:00:00.000' as HiredDate_DateTime,
       '2012-08-26' + ' 00:00:00.000' as TKS_DateTime
    FROM vwu_Employee_Contract_Info
),
Min_Max_Attendance As
(
    SELECT t_in.employee_id,
           min(isnull(t_in.logdatetime,'1753-01-01 00:00:00.000')) min_in,
           max(isnull(t_out.logdatetime,'1753-01-01 00:00:00.000')) max_out
    FROM
        (SELECT employee_id,logdatetime FROM Mstr_Attendance WHERE tkstype=0) t_in
    LEFT JOIN
        (SELECT employee_id,logdatetime FROM Mstr_Attendance WHERE tkstype=1) t_out
    ON
        t_in.employee_id=t_out.employee_id
    GROUP BY
        t_in.employee_id
)
SELECT Attendance_CTE.Employee_ID,logdatetime,
       CASE WHEN TKSType=0 THEN
           CASE Min_Max_Attendance.min_in
                -- first day
                WHEN '1753-01-01 00:00:00.000' THEN
                    CASE
                        -- check if current logdatetime is within the hireddate and current date query
                        WHEN Attendance_CTE.LogDateTime BETWEEN HiredDate_DateTime AND TKS_DateTime
                        THEN
                            CASE
                                WHEN Attendance_CTE.LogDateTime BETWEEN Employee_CTE.Official_In AND
                                     Employee_CTE.Official_Out THEN Attendance_CTE.LogDateTime
                            END
                        ELSE
                        -- no time in?
                                '1753-01-01 00:00:00.000'
                    END
                ELSE
                    -- regular days
                    CASE
                        -- check whether no out
                        WHEN Min_Max_Attendance.max_out = '1753-01-01 00:00:00.000' THEN Attendance_CTE.Logdatetime
                    ELSE
                        -- but with previous out
                        CASE
                            -- check whether logdatetime is not greater than the TKS_DateTime
                            WHEN  Convert(varchar(10),Attendance_CTE.logdatetime,120) !> TKS_DateTime THEN
                                  CASE
                                    -- when logdatetime range on employee official time including late
                                    WHEN Attendance_CTE.logdatetime BETWEEN Employee_CTE.Official_In AND Employee_CTE.Official_Out THEN Attendance_CTE.Logdatetime
                                    -- when logdatetime is less than the max l_out on the records
                                    WHEN Attendance_CTE.LogDateTime <= Min_Max_Attendance.max_out THEN Attendance_CTE.LogDateTime
                                    -- when logdatetime is on range between the min l_in and max l_out on the records
                                    WHEN Attendance_CTE.LogDateTime BETWEEN Min_Max_Attendance.min_in AND Min_Max_Attendance.max_out THEN Attendance_CTE.LogDateTime
                                    -- when logdatetime is on range between current query date to official in
                                    WHEN Attendance_CTE.LogDateTime BETWEEN TKS_DateTime AND Employee_CTE.Official_In THEN Attendance_CTE.LogDateTime
                                    -- when logdatetime is not greater than equal to current query date
                                    WHEN Attendance_CTE.LogDateTime < TKS_DateTime THEN Attendance_CTE.LogDateTime
                                  END
                            /*ELSE
                                -- no time in?
                                '1753-01-01 00:00:00.000'*/
                        END
                    END
            END
        END ActualLogIn,
        CASE WHEN TKSType=1 THEN
             CASE
                -- check whether logdatetime is less than  official_out and greater than official_in, undertime
                WHEN Attendance_CTE.LogDateTime > Employee_CTE.Official_Out AND Attendance_CTE.LogDateTime !> Employee_CTE.Official_In
                THEN    Attendance_CTE.LogDateTime
                -- logdatetime is greater than official out and not greater than the next log in
                WHEN Attendance_CTE.LogDateTime >= Employee_CTE.Official_Out AND Attendance_CTE.LogDateTime !> '2012-08-27 17:00:00.000'
                    THEN Attendance_CTE.LogDateTime
                WHEN Attendance_CTE.LogDateTime !> '2012-08-27 17:00:00.000'
                THEN Attendance_CTE.LogDateTime
             END
        END ActualLogOut
FROM Employee_CTE
LEFT JOIN Attendance_CTE
ON
    Employee_CTE.Employee_ID=Attendance_CTE.Employee_ID
LEFT JOIN Min_Max_Attendance
ON
    Employee_CTE.Employee_ID=Min_Max_Attendance.Employee_ID
AND
    Attendance_CTE.Employee_ID=Min_Max_Attendance.Employee_ID
WHERE
    Employee_CTE.Employee_ID IN ('PH120012','PH120013')

select * from (
select logi_in.logdatetime as 'IN' , logi_out.logdatetime as 'OUT'
from MSTR_ATTENDANCE logi_in
left outer join MSTR_ATTENDANCE logi_out
on logi_in.employee_id=logi_out.employee_id
and logi_in.tkstype=0
and logi_out.logdatetime =(
select     MIN(logdatetime)
             from       MSTR_ATTENDANCE MSTR
             where      MSTR.employee_id = logi_out.employee_id
             and        MSTR.tkstype = 1
             and        MSTR.logdatetime > logi_in.logdatetime
       )
and logi_in.employee_id='PH120013'
and logi_in.logdatetime='2012-08-26 02:30:00.000')t
where OUT is not null