Time Keeping

  • Hi my name is Sharon, id like to ask how to get the first and last of the in and out of each users and also with lunch in and lunch out using the TSQL.

    Here is the sample output of the data:

    Name LogDate/Time

    Erwin Lorenze P. Lubis 2015-12-03 00:04:34.000

    Erwin Lorenze P. Lubis 2015-12-03 00:17:34.000

    Erwin Lorenze P. Lubis 2015-12-03 00:23:55.000

    thank you!

  • You can achieve it using MIN() AND MAX() function for each person. Something like this

    SELECT Name, MIN(LogDateTime) AS Min_LogDateTime, MAX(LogDateTime) AS Max_LogDateTime

    FROM [YourTable]

    GROUP BY Name

    But question didnt state that you want to get the CheckIn and CheckOut for each Date for an employee or u just need the 1st CheckIn and Last CheckOut of an employee. your sample data is not quite enough for understanding your actual problem.

    Furthermore, can you share some more details about the following line.

    also with lunch in and lunch out using the TSQL.

  • Hi sorry for confuse you about the question:

    1. How can i get the Log In and Log out based on their official time (with shifting)?

    2. How to to get the Lunch Break In and Lunch Break Out?

    Here are the sample data

    Employee No DateTimeLogs Status

    00445 2014-08-03 06:43:09.000 IN

    00445 2014-08-03 09:43:41.000 OUT

    00445 2014-08-03 09:56:26.000 IN

    00445 2014-08-03 12:05:44.000 OUT

    00445 2014-08-03 13:30:56.000 IN

    00445 2014-08-03 20:40:55.000 OUT

    00445 2014-08-03 23:20:30.000 IN

    00445 2014-08-04 07:30:18.000 OUT

    Below is the desired out put:

    Employee No Date Time Status

    00445 2014-08-03 06:43:09.000 IN

    00445 2014-08-03 12:05:44.000 BREAK OUT

    00445 2014-08-03 13:30:56.000 BREAK IN

    00445 2014-08-04 07:30:18.000 OUT

    Hoping for your help.

    thanks!

  • Well well well, a person start on 6 Am an end of next date 7 am. Gosh he worked to much. be very careful of Labor law my friend :-P.

    Joke apart, what have you done so far? Any detail will be helpful.

    Few questions

    1. How can i get the Log In and Log out based on their official time (with shifting)?

    What exactly the meaning of official time? because all i can see in your sample data is IN/OUT and a person have worked in multiple shifts in 24 hrs.

    2. How to to get the Lunch Break In and Lunch Break Out?

    Is Lunch Time is fixed? as per the sample data its looks like 12 to 13 hrs belong to Lunch but is it variable. when is say variable i mean can a person go to lunch at any given time or its always be in this two hour window 12:00 to 13:59 ?

  • Hi 🙂 regarding the time yes sometimes they do work straight due to work demand...

    The Lunch break is fixed Lunch start at 12 and it ends at 1 but sometimes they do come back past 1 already. About the official time it was supposed to be 8 hours but now they are planning for shifting schedule.

    So far the only thing i have is the first in and last out of the day no break time. I'd like to learn the time comparison between the time they log in and their official time and the time for break time.

    thanks for the BIG help.....

  • Well, Following is the solution after the understanding i get from your answers.

    Please note that i have shared you SQL SERVER 2000 solution as this question is in SQL Server 7,2000.

    1st created a variable table to get the row id.

    Declare @Transaction Table

    (

    RowIDint identity(1,1),

    EmployeeCodevarchar(5),

    TransactionDate datetime,

    EventDateTimedatetime,

    EventTypevarchar(3)

    )

    2nd data prep.

    --- Expended sample data, because after spending 24 i have asked the person to come again after an hour :D

    insert into @Transaction

    SELECT a.EmployeeCode

    , Convert(datetime, convert(nvarchar(10),a.TransactionDate, 101)) AS TransactionDate

    , a.TransactionDate

    , a.TransactionType

    FROM

    (

    --------------- Day One

    Select '00445' as EmployeeCode, '2014-08-02 06:43:09.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-02 09:43:41.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-02 09:56:26.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-02 12:35:44.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-02 13:45:56.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-02 20:40:55.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-02 21:20:30.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-03 05:30:18.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    -------------- Day Two

    Select '00445' as EmployeeCode, '2014-08-03 06:43:09.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-03 09:43:41.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-03 09:56:26.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-03 12:30:44.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-03 13:50:56.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-03 20:40:55.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-03 23:20:30.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 07:30:18.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    -------------- Day Three

    Select '00445' as EmployeeCode, '2014-08-04 07:43:09.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 09:43:41.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 09:56:26.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 12:15:44.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 13:45:56.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 20:40:55.000' as TransactionDate, 'OUT'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 23:20:30.000' as TransactionDate, 'IN'as TransactionTypeunion all

    Select '00445' as EmployeeCode, '2014-08-04 23:59:18.000' as TransactionDate, 'OUT'as TransactionType

    ) A

    Order by A.EmployeeCode, A.TransactionDate

    Now the original problem was, if you have a Checkin of a person and its last check out comes in the next day, it would would have be considered at that date. so this will get you your desired result. i have extended your sample data which two difference scenarios.

    Following is the code

    SELECTM.EmployeeCode

    , adj_TransactionType

    , CASE

    WHEN adj_TransactionType = 'IN'THEN MIN(adj_EventDateTime)

    WHEN adj_TransactionType = 'Lunch OUT' THEN MIN(adj_EventDateTime)

    WHEN adj_TransactionType = 'Lunch IN'THEN MAX(adj_EventDateTime)

    WHEN adj_TransactionType = 'OUT'THEN MAX(adj_EventDateTime)

    END AS calcEventDateTime

    FROM

    (

    ---- use self join to get the next row

    SELECT

    t.EmployeeCode

    , t.TransactionDate

    , CASE

    WHEN DATEPART(hh, t.EventDateTime) IN (12,13) THEN 'Lunch ' + t.EventType

    ELSE

    (CASE WHEN a.max_RowId IS NOT NULL THEN 'OUT' ELSE t.EventType END )

    END adj_TransactionType

    , CASE WHEN a.max_RowId IS NOT NULL THEN t1.EventDateTime ELSE t.EventDateTime END as adj_EventDateTime

    FROM

    @Transaction t

    LEFT JOIN @Transaction t1on t.EmployeeCode = t1.EmployeeCode

    and t.RowID + 1 = t1.RowID

    -------------- This query to get the last element of the chain

    LEFT JOIN

    (

    SELECT

    t.EmployeeCode

    , TransactionDate

    , MAX(RowId) AS max_RowId

    FROM

    @Transaction t

    GROUP BY

    t.EmployeeCode, TransactionDate

    ) A on t.EmployeeCode= a.EmployeeCode

    and t.RowID= a.max_RowId

    ) M

    GROUP BY

    M.EmployeeCode, adj_TransactionType, m.TransactionDate

    ORDER BY

    M.EmployeeCode, m.TransactionDate

    Do test it against real data you have and if the data volume is high replace the Variable table with Temp Table.

    Hope it helps.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply