Finding out min tn time and max out time 1

[Zoom1234]

Hi,
I have a table in following format

empcode, trans_date (datetime), trans_time(datetime), Inout (int) 0 - in, 1 - out
and following data
empcode trans_date trans_time Inout
100 2006-03-28 09:40 0
100 2006-03-28 10:40 1
100 2006-03-28 10:50 0
100 2006-03-28 18:40 1
100 2006-03-29 09:45 0
100 2006-03-29 10:45 1
100 2006-03-29 11:40 0
100 2006-03-29 18:50 1
101 2006-03-28 10:50 0
101 2006-03-28 11:40 1
101 2006-03-28 11:50 0
101 2006-03-28 17:40 1
101 2006-03-29 09:50 0
101 2006-03-29 10:50 1
101 2006-03-29 11:40 0
101 2006-03-29 15:50 1
WHat i want to extract is empcode, date and his min in time and max out time.
I have tried many cominations but not getting it right.
note that trans_date and trans_time are two diff fields..

Can someone help pls.

 

[r937]

note that trans_date and trans_time are two diff fields

why???

select empcode
     , trans_date
     , min(case when Inout=0
                then trans_time end) as min_in
     , max(case when Inout=1
                then trans_time end) as max_out
  from daTable
group
    by empcode
     , trans_date

r937.com | rudy.ca

 

[Zoom1234]

Hi r937,

why???

I asked the same question to the programmer who designed it )

Anyway i will try it and get back to u.
Thanks

 

[Zoom1234]

Hi r937,
You rock!!