April 10, 2015 at 2:58 am
EmpCode..........Time...............In/Out
001----2013-10-02 06:54:00---False
001----2013-10-02 11:35:00---True
001----2013-10-02 13:29:00---False
001----2013-10-02 17:03:00---True
001----2013-10-02 20:50:00---False
001----2013-10-12 06:02:00---True
001----2013-10-12 11:32:00---False
001----2013-10-12 13:17:00---False
001----2013-10-12 17:00:00---False
001----2013-10-22 06:57:00---True
001----2013-10-22 11:46:00---True
001----2013-10-22 13:21:00---False
001----2013-10-22 17:01:00---True
What I want to be displayed ?
Emp.....TimeIn......................................TimeOut
001----NULL-------------------2013-10-02 06:54:00
001----2013-10-02 11:35:00---2013-10-02 13:29:00
001----2013-10-02 17:03:00---2013-10-02 20:50:00
001----2013-10-12 06:02:00---2013-10-12 17:00:00
001----2013-10-22 06:57:00---2013-10-22 13:21:00
001----2013-10-22 17:01:00---NULL
Thank you
April 10, 2015 at 9:47 am
This will get you most of the way.
SELECT
t.EmpCode,
oa.TimeIn,
t.Time AS [TimeOut]
FROM
#TEST t
OUTER APPLY(
SELECT MAX(Time) AS TimeIn FROM #TEST WHERE [TIME] < t.TIME AND [In/OUT] = 'TRUE'
) oa
WHERE
t.[In/Out] = 'FALSE'
AND EmpCode = '001'
It looks for an 'OUT' and then grabs the previous 'IN' because of this you won't see the last row as you had hoped.
April 10, 2015 at 10:18 am
...or you can cheese it a bit and do
SELECT * FROM #TEST
SELECT
t.EmpCode,
oa.TimeIn,
t.Time AS [TimeOut]
FROM
#TEST t
OUTER APPLY(
SELECT MAX(Time) AS TimeIn FROM #TEST WHERE [TIME] < t.TIME AND [In/OUT] = 'TRUE'
) oa
WHERE
t.[In/Out] = 'FALSE'
AND EmpCode = '001'
UNION ALL
SELECT
EmpCode,
MAX([Time]) AS TimeIn,
NULL AS [TimeOut]
FROM
#TEST
WHERE
EmpCode = '001' AND [In/Out] = 'TRUE'
GROUP BY
EmpCode
April 10, 2015 at 12:33 pm
Slightly off topic here but I have to ask. When you say In/Out what does True represent?
This is wrong on a couple of levels. The first is that the naming is totally ambiguous. The second is using strings to represent true and false. I would suggest something more like IsIn as a column name and use bit (not null) as the datatype. That immediately makes it clear what the column is for and what the values represent.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 10, 2015 at 12:51 pm
Sean Lange (4/10/2015)
Slightly off topic here but I have to ask. When you say In/Out what does True represent?This is wrong on a couple of levels. The first is that the naming is totally ambiguous. The second is using strings to represent true and false. I would suggest something more like IsIn as a column name and use bit (not null) as the datatype. That immediately makes it clear what the column is for and what the values represent.
I totally agree with you here. I was also confused when trying to come up with a solution. I myself used a bit data type when testing the query but used 'FALSE' rather than 0 to keep as close as possible for the OP.
April 10, 2015 at 12:58 pm
yb751 (4/10/2015)
Sean Lange (4/10/2015)
Slightly off topic here but I have to ask. When you say In/Out what does True represent?This is wrong on a couple of levels. The first is that the naming is totally ambiguous. The second is using strings to represent true and false. I would suggest something more like IsIn as a column name and use bit (not null) as the datatype. That immediately makes it clear what the column is for and what the values represent.
I totally agree with you here. I was also confused when trying to come up with a solution. I myself used a bit data type when testing the query but used 'FALSE' rather than 0 to keep as close as possible for the OP.
And you did a great job of cobbling together a workable solution from a horrible data structure. I would not have separate rows for In and Out assuming that In should always be First and Out is next. I am guessing this a timeclock app of some sort. I would do something more like:
create table TimeCardPunch
(
TimeCardPunchID int identity primary key clustered
, EmployeeID int not null
, InPunchTime datetime not null default getdate()
, OutPunchTime datetime null
)
Now adding a punch is super painless. If there is a row for the employee where OutPunchTime is null you update it to the current datetime. Otherwise you insert a new row. And getting the results the OP is looking for is about as simple as it gets.
Proof positive that if you build the ddl correctly, the dml is simple.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply