SAMPLE TIMEIN TIME OU SQL QUERY

on





SELECT EmpNo, MIN(`time`) AS intime, MAX(`time`) AS outtime, SEC_TO_TIME(SUM( TIME_TO_SEC( time ) )) AS timeSum, MAX(`time`)-MIN(`time`)as total, dates, IF(TIME_TO_SEC(SUBTIME('8:00:00',MIN(`time`))) < 0, 'late', 'not late') AS late, IF(TIME_TO_SEC(SUBTIME('15:00:00',MAX(`time`))) < 0, 'extra', 'no extra') AS extra FROM `inout` GROUP BY EmpNo, dates

Comments

  1. RelaxBroSeptember 21, 2020 at 11:29 AM

    SELECT
    id,
    Employee,
    TimeStop,
    TimeStart,
    TIMEDIFF(`TimeStart`, `TimeStop`) AS TotalTime,
    IF(TIME_TO_SEC(SUBTIME('9:00:00',MIN(`TimeStart`))) < 0, 'late', 'not late') AS late,
    IF(TIME_TO_SEC(SUBTIME('15:00:00',MAX(`TimeStop`))) < 0, 'extra', 'no extra') AS extra,
    Dates
    FROM attendance
    GROUP BY Employee, Dates

    ReplyDelete
  2. RelaxBroSeptember 22, 2020 at 6:58 AM

    SELECT
    id,
    Employee,
    TimeStop,
    TimeStart,
    TIMEDIFF(`TimeStart`, `TimeStop`) AS TotalTime,
    IF(TIME_TO_SEC(SUBTIME('9:00:00',MIN(`TimeStart`))) < 0, 'late', 'not_late') AS late,
    IF(TIME_TO_SEC(SUBTIME('15:00:00',MAX(`TimeStop`))) < 0, 'extra', 'not_extra') AS extra,
    Dates
    FROM attendance
    GROUP BY Employee, Dates

    ReplyDelete
  3. RelaxBroSeptember 23, 2020 at 7:17 AM

    SELECT
    id,
    Employee,
    TimeStop,
    TimeStart,
    TIMEDIFF(`TimeStop`, `TimeStart`) AS TotalTime,
    IF(TIME_TO_SEC(SUBTIME('8:00:00',MIN(`TimeStart`))) < 0, 'late', 'not_late') AS late,



    IF(TIME_TO_SEC(SUBTIME('15:00:00',MAX(`TimeStop`))) < 0, 'extra', 'not_extra') AS extra,
    Dates,
    dayname(Dates) as Days,
    Monthname(Dates) as Month,
    year(Dates) as Years
    FROM attendance
    GROUP BY Employee, Dates
    ORDER BY id DESC

    ReplyDelete

Post a Comment