January 23, 2011 at 1:02 pm
the table i'm querying looks something like this:
empid name Date
1 name1 12/01/2011 8:00:00am
1 name1 12/01/2011 4:00:00pm
1 name1 12/02/2011 8:00:00pm
1 name1 12/02/2011 4:00:00pm
2 name2 12/01/2011 7:00:00am
2 name2 12/01/2011 3:00:00pm
The date field shows the time each person clocked in and out. What I'd like to do is get the following result set and only show records with a 12/01/2011 date
empid Clock_In_Time Clock_Out_Time
1 12/01/2011 8:00:00am 12/01/2011 4:00:00pm
2 12/01/2011 7:00:00am 12/01/2011 3:00:00pm
Select empid, min(date) as Clock_In_Time, max(date) as Clock_Out_Time
From table1
Group by empid
Where Date >= 12/01/2011 and Date < 12/02/2011
Would something like that return one row per person with those results?
January 23, 2011 at 1:22 pm
Two things to start. How do you know which record is the "clock in" and which is the "clock out"? You appear to have a possible data problem with one of your entries.
More importantly, please read the first article I reference below in my signature block below regarding asking for help. Please follow the instructions in that article regard what you need to post and how.
January 23, 2011 at 2:50 pm
Sorry about the cluttered table. I had the fields more spread out when I was typing it and it didn't quite look like that. I should have previewed it first. I don't have a way of telling which one is which unfortunately , so for now I was just using the min and max functions on the dates.
January 23, 2011 at 3:23 pm
With your data as given:
CREATE TABLE #Table1(empid INT, name VARCHAR(20), Date DATETIME)
INSERT INTO #Table1
SELECT 1,'name1','12/01/2011 8:00:00 am' UNION ALL
SELECT 1,'name1','12/01/2011 4:00:00 pm' UNION ALL
SELECT 1,'name1','12/02/2011 8:00:00 pm' UNION ALL
SELECT 1,'name1','12/02/2011 4:00:00 pm' UNION ALL
SELECT 2,'name2','12/01/2011 7:00:00 am' UNION ALL
SELECT 2,'name2','12/01/2011 3:00:00 pm'
Select empid,name, MIN([date]) as Clock_In_Time, MAX([date]) as Clock_Out_Time
From #table1 Where Date >= '12/01/2011 00:00:00:000'and Date < '12/02/2011 00:00:00:000'
Group by empid,name
Results:
empid name clock_in_time clock_out_time
1name12011-12-01 08:00:00.0002011-12-01 16:00:00.000
2name22011-12-01 07:00:00.0002011-12-01 15:00:00.000
From the data given all is well and correct, but what happens if an employee clocks in at say 11 PM on a given date and works until 7 AM on the following day .. the code above will not be correct If the table can not be altered to have a column which indicates wether it a clock in or a clock out as noted by Lynn Pettis this solution is what I would term "fragile"
January 23, 2011 at 3:26 pm
How does using the min/max work for you when you have multiple records for a single employee of multiple dates?
January 23, 2011 at 5:56 pm
Min and max have temporarily worked, but only if the person clocked in and out on the same day. And currently I just needed the results for a single day, where there were very few people that clocked out the following day. So those were handled manually. But the case where people clocked in on one day and out the next was going to be my second issue.
Say I was able to insert a clock_id column into my table which contained either a 1 or 2. 1 for a clock in and 2 for a clock out. This part has me a little stumped. I know I only want clock ins returned from 12/01. And if there wasn't a clock out on 12/01, then I need the first clock out on 12/02. But I'm not sure how to go about only getting clock ins from 12/01, and getting the first clock out on the following day if necessary. Would this be a start? I realize this will return clock ins for both 12/01 and 12/02 though.
Select empid,
(Select date From table1
Where clock_id=1) As clock_in_time),
(Select date From table1
Where clock_id=2) As clock_out_time)
From table1
Where date >= '12/01/2011' And date < '12/03/2011'
Group By empid, name
January 24, 2011 at 12:47 pm
Does a person only clock in and out once per work day? Can someone clock in for work, clock out for lunch, clock back in again after lunch and then clock out at the end of the day?
Todd Fifield
January 24, 2011 at 5:54 pm
tfifield (1/24/2011)
Does a person only clock in and out once per work day? Can someone clock in for work, clock out for lunch, clock back in again after lunch and then clock out at the end of the day?Todd Fifield
Or pull an "all nighter" working through Midnight?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2011 at 1:47 pm
Yes, they can do all of those. So that is also something I'd eventually like to take into account in my query.
January 25, 2011 at 2:28 pm
What you really need in your table is an event type - Clock In or Clock out - not just a time.
I've worked on a couple of time card type applications and the time card clock should give you the type of event. This also helps spot 2 clock in events with no clock out event in the middle. Something workers tend to do at lunch time.
Without an event type you will get lost as to what the times actually mean.
Todd Fifield
January 25, 2011 at 9:57 pm
Thanks for the input everyone, I'll have to look into that.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply