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 |
|
pnasz
Posting Yak Master
101 Posts |
 Posted - 2010-11-24 : 08:33:43
|
| i have a table with employee details for 10 daysentrydate EmpID firstIn FirstOut2010-09-01 51 NULL 16:25:31.00000002010-09-02 51 00:00:00.0000000 00:00:00.00000002010-09-03 51 00:00:00.0000000 00:00:00.00000002010-09-04 51 09:40:24.0000000 16:27:00.00000002010-09-05 51 09:36:43.0000000 16:33:29.00000002010-09-05 51 09:36:43.0000000 16:33:30.00000002010-09-06 51 10:10:00.0000000 16:30:01.00000002010-09-06 51 10:10:01.0000000 16:30:01.00000002010-09-07 51 09:38:17.0000000 NULL2010-09-08 51 00:00:00.0000000 00:00:00.00000002010-09-09 51 00:00:00.0000000 00:00:00.00000002010-09-10 51 00:00:00.0000000 00:00:00.0000000i want to display all the record where entry date should not repeat.I tried using distinct but its not working |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-24 : 08:38:14
|
| select * from(select *, seq = row_number() over (partition by entrydate, EmpID order by firstin)from tbl) awhere seq = 1or maybeselect entrydate, empid, firstin = min(firstin) firstout = max(firstout)from tblgroup by entrydate, empid==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-11-24 : 09:15:32
|
| with that record what are in and out times you want to return?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
entrydate should not repeat