> I had a table with 02 fields containing the in and out time of
>employee store as DateTime data type.
>
>1) How do I extract out the first in time and last out time , tried using the SQL MAX() and MIN() didn't work like what I want
>
>2) add in the missing date records
>
>like the one show below using SQL command.
>Any advise or help is very much appreciated. Thank you.
>
> example of data
>
> in out
>============== ==============
>01/10/98 08:00 01/10/98 10:00
>01/10/98 13:00 01/10/98 15:00
>01/10/98 16:00 01/10/98 20:00
>02/10/98 09:00 02/10/98 11:00
>02/10/98 13:00 02/10/98 18:00
>04/10/98 09:00 04/10/98 13:00
>06/10/98 09:00 06/10/98 17:00
>
>
>1) To be like
>
> First in Last Out
> ============== ==============
> 01/10/98 08:00 01/10/98 20:00
> 02/10/98 09:00 02/10/98 18:00
>
>
>And
>
>
>2) Sorted Data
> ===========
>
> 01/10/98 08:00 01/10/98 20:00
> 02/10/98 09:00 02/10/98 18:00
> 03/10/98 00:00 03/10/98 00:00 or EMPTY but must with date
> 04/10/98 00:00 04/10/98 00:00 or Empty but must with date
> 05/10/98 00:00 05/10/98 00:00
> 06/10/98 09:00 06/10/98 17:00


For the first problem I would use something like

SELECT EMPLOYEE,MIN(INTIME),MAX(OUTTIME) FROM ... GROUP BY 1

For the second one, you would need to set up a cursor with all the dates (say, from 1/10/98 to 6/10/98); JOIN it with a cursor with all the employees, and then LEFT OUTER JOIN it with your table.

HTH!