Sql Query for Time In/Out Attendance

MS Access SQL Query for time In/Out attendance

This fancy query will return the lunch breaks:

SELECT 
    dbo_UserInfo.UserId, 
    DateValue([CheckTime]) AS [Date], 
    TimeValue(Min([CheckTime])) AS LogIn, 

    (Select Max(TimeValue(T.CheckTime)) 
    From dbo_UserInfo As T 
    Where T.UserId = dbo_UserInfo.UserId 
    And DateValue(T.CheckTime) = DateValue(dbo_UserInfo.CheckTime) 
    And T.CheckTime >
        (Select Min(S.CheckTime) 
        From dbo_UserInfo As S 
        Where S.UserId = dbo_UserInfo.UserId 
        And DateValue(S.CheckTime) = DateValue(dbo_UserInfo.CheckTime))) As LogOut,

    (Select Min(TimeValue(T.CheckTime)) 
    From dbo_UserInfo As T 
    Where T.UserId = dbo_UserInfo.UserId 
    And DateValue(T.CheckTime) = DateValue(dbo_UserInfo.CheckTime)
    And T.CheckTime > 
        (Select Min(S.CheckTime) 
        From dbo_UserInfo As S 
        Where S.UserId = dbo_UserInfo.UserId 
        And DateValue(S.CheckTime) = DateValue(dbo_UserInfo.CheckTime))
    And T.CheckTime <
        (Select Max(S.CheckTime) 
        From dbo_UserInfo As S 
        Where S.UserId = dbo_UserInfo.UserId 
        And DateValue(S.CheckTime) = DateValue(dbo_UserInfo.CheckTime))
    Having Count(*) > 1) As LBIn,

    (Select Max(TimeValue(T.CheckTime)) 
    From dbo_UserInfo As T 
    Where T.UserId = dbo_UserInfo.UserId 
    And DateValue(T.CheckTime) = DateValue(dbo_UserInfo.CheckTime) 
    And T.CheckTime > 
        (Select Min(S.CheckTime) 
        From dbo_UserInfo As S 
        Where S.UserId = dbo_UserInfo.UserId 
        And DateValue(S.CheckTime) = DateValue(dbo_UserInfo.CheckTime))
    And T.CheckTime <
        (Select Max(S.CheckTime) 
        From dbo_UserInfo As S 
        Where S.UserId = dbo_UserInfo.UserId 
        And DateValue(S.CheckTime) = DateValue(dbo_UserInfo.CheckTime))
    Having Count(*) > 1) As LBOut
FROM 
    dbo_UserInfo
GROUP BY 
    dbo_UserInfo.UserId, 
    DateValue([CheckTime]);

From this you can easily calculate the work hours.

Revised output:

SQL Query for In/Out time attendance

SELECT *
FROM [dbo].[TIMEIN] i
FULL JOIN [dbo].[TIMEOUT] o ON i.IINDX = o.OINDX
ORDER BY oINDX

+-------+--------+------+------------+-------------------------+-------+--------+------+------------+-------------------------+---+
| IINDX | USERID | Date | CHECKTIME  |        CHECKTYPE        | OINDX | USERID | Date | CHECKTIME  |        CHECKTYPE        |   |
+-------+--------+------+------------+-------------------------+-------+--------+------+------------+-------------------------+---+
|       | 1      | 60   | 2018-02-07 | 2018-02-07 06:58:48.000 | I     |      1 |   60 | 2018-02-07 | 2018-02-07 15:59:32.000 | O |
|       | 3      | 60   | 2018-02-08 | 2018-02-08 06:01:09.000 | I     |      3 |   60 | 2018-02-08 | 2018-02-08 15:00:32.000 | O |
|       | NULL   | NULL | NULL       | NULL                    | NULL  |      4 |   60 | 2018-02-08 | 2018-02-08 15:00:34.000 | O |
|       | 6      | 60   | 2018-02-09 | 2018-02-09 06:57:43.000 | I     |      6 |   60 | 2018-02-09 | 2018-02-09 19:00:03.000 | O |
|       | 8      | 60   | 2018-02-10 | 2018-02-10 06:34:28.000 | I     |      8 |   60 | 2018-02-10 | 2018-02-10 15:31:16.000 | O |
|       | 10     | 60   | 2018-02-11 | 2018-02-11 05:59:38.000 | I     |     10 |   60 | 2018-02-11 | 2018-02-11 15:01:47.000 | O |
|       | 12     | 60   | 2018-02-12 | 2018-02-12 06:02:14.000 | I     |     12 |   60 | 2018-02-12 | 2018-02-12 15:03:06.000 | O |
|       | 14     | 60   | 2018-02-13 | 2018-02-13 06:00:25.000 | I     |     14 |   60 | 2018-02-13 | 2018-02-13 15:01:40.000 | O |
|       | 16     | 60   | 2018-02-14 | 2018-02-14 06:01:59.000 | I     |     16 |   60 | 2018-02-14 | 2018-02-14 15:00:34.000 | O |
|       | 18     | 60   | 2018-02-15 | 2018-02-15 06:01:56.000 | I     |     18 |   60 | 2018-02-15 | 2018-02-15 15:02:55.000 | O |
|       | 20     | 60   | 2018-02-22 | 2018-02-22 13:58:31.000 | I     |     20 |   60 | 2018-02-22 | 2018-02-22 22:20:42.000 | O |
|       | 22     | 60   | 2018-02-23 | 2018-02-23 18:57:01.000 | I     |     22 |   60 | 2018-02-24 | 2018-02-24 06:03:39.000 | O |
|       | 24     | 60   | 2018-02-24 | 2018-02-24 21:54:01.000 | I     |     24 |   60 | 2018-02-25 | 2018-02-25 07:04:37.000 | O |
|       | 26     | 60   | 2018-02-25 | 2018-02-25 21:53:58.000 | I     |     26 |   60 | 2018-02-26 | 2018-02-26 07:00:16.000 | O |
|       | 28     | 60   | 2018-02-26 | 2018-02-26 22:03:14.000 | I     |     28 |   60 | 2018-02-27 | 2018-02-27 07:04:08.000 | O |
|       | 30     | 60   | 2018-02-27 | 2018-02-27 21:57:51.000 | I     |     30 |   60 | 2018-02-27 | 2018-02-27 21:58:48.000 | O |
|       | NULL   | NULL | NULL       | NULL                    | NULL  |     31 |   60 | 2018-02-28 | 2018-02-28 07:00:18.000 | O |
+-------+--------+------+------------+-------------------------+-------+--------+------+------------+-------------------------+---+

Get one Clock In and one clock out time from a record employee table

Looks like you want to group by the employee and date, then use conditional aggregation to split out the clock-ins and -outs.

select
    employeeId,
    convert(date,addat) Date, 
    Min(CASE WHEN LogType = 0 THEN addat END) ClockIn,
    Max(CASE WHEN LogType = 1 THEN addat END) ClockOut
from #MyTempTable
Group by employeeId, convert(date,addat)

SQL Select Statement for Time and attendance for a month

Try this. I was not sure what time format would satisfy your system, so I put both:

SELECT * INTO #Tbl3 FROM (VALUES
(3221,'IT','2017-01-29 11:12:00.000',1),
(5565,'IT','2017-01-29 12:28:06.000',1),
(5565,'IT','2017-01-29 12:28:07.000',1),
(3221,'IT','2017-01-29 13:12:00.000',2),
(5565,'IT','2017-01-29 13:28:06.000',2),
(3221,'IT','2017-01-30 07:42:15.000',1),
(3221,'IT','2017-01-30 16:16:15.000',2),
(3221,'IT','2017-01-31 09:05:00.000',1),
(3221,'IT','2017-01-31 11:05:00.000',2),
(3221,'IT','2017-01-31 13:20:00.000',1),
(3221,'IT','2017-01-31 16:10:00.000',2))
x (Empid,Department,Timestamp,Read_ID)

;With cte as (
    SELECT t1.Empid, t1.Department
        , [Year] = Year(t1.Timestamp)
        , [Month] = Month(t1.Timestamp)
        , Seconds = SUM(DATEDIFF(second, t1.Timestamp, t2.Timestamp))
    FROM #Tbl3 as t1
    OUTER APPLY (
        SELECT Timestamp = MIN(t.Timestamp) 
        FROM #Tbl3 as t
        WHERE t.Department = t1.Department and t.Empid = t1.Empid
            and t.Timestamp > t1.Timestamp and t.Read_ID = 2
    ) as t2
    WHERE t1.Read_ID = 1
    GROUP BY t1.Empid, t1.Department, Year(t1.Timestamp), Month(t1.Timestamp))
SELECT *, TotalHours = Seconds / 3600., TotalTime =
    RIGHT('0'+CAST(Seconds / 3600 as VARCHAR),2) + ':' +
    RIGHT('0'+CAST((Seconds % 3600) / 60 as VARCHAR),2) + ':' +
    RIGHT('0'+CAST(Seconds % 60 as VARCHAR),2)
FROM cte;

---

Related Topics