January 4, 2016 at 10:13 pm
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!
January 4, 2016 at 11:18 pm
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.
January 10, 2016 at 9:04 pm
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!
January 10, 2016 at 11:52 pm
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 ?
January 11, 2016 at 12:50 am
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.....
January 11, 2016 at 4:11 am
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