Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQL1234
Starting Member
3 Posts |
 Posted - 2005-03-21 : 11:17:55
|
I have a table similiar to this:EmpID In/Out Time----- ------ ----00001 IN 1:00PM00001 OUT 1:30PM00001 IN 2:00PM00001 OUT 5:00PM00002 IN 12:00PM00003 IN 1:00PM Each EmpID has multiple IN/OUT records. How would I go about getting the first IN and the last OUT times and display them on a single line. So it would look like this:EmpID First-In-Time Last-Out-Time----- ------------- -------------00001 1:00PM 5:00PM |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-03-21 : 12:17:33
|
| select EmpID, min(case when [In/Out] = 'IN' then Time else '00:00' end), max(case when [In/Out] = 'OUT' then Time else '00:00' end)from tblgroup by EmpID==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
SQL1234
Starting Member
3 Posts |
Posted - 2005-03-24 : 09:55:43
|
Thanks, that got me pointed in the right direction. On a similiar note is it possible to get the data out so it matches up an IN time with the corresponding OUT time? So each IN/OUT pair would have a separate line.EmpID IN OUT------- ----- -----00001 1:00 1:3000001 2:00 5:00ETC... I also noticed that when using the MIN/MAX function with a Date column the MAX works correctly but the MIN always returns 'Jan 1900' instead of the actual MIN of the "group by" set. Any idea why that would happen? Finally, is it possible to set a DateTime datatype to something like an empty string or a space so nothing will be outputed in the case of the ELSE statement? If I try that I get an error about an incorrect datatype so I was just using NULL instead but I wasn't sure if thats the best way to handle it. |
|
|
|
|
|
|
Query Help