Date
CardNo
TransactionType
Time

so that when you check in in the morning, a record is generated like so:

Date: 4/4/2000
CardNo: 00001
TransactionType: 1
Time: 08:00

and that night you check out like so:

Date: 4/4/2000
CardNo: 00001
TransactionType: 2
Time: 17:00

I need a SELECT statement that selects all transactions between a date range
for a particular Card, returning data as follows:

Date        Card        TimeIn        TimeOut
4/4/2000    00001    08:00        17:00
5/5/2000    00001    08:14        17:22

Is it possible in a SQL query to do this? I think it is something to do with
outer joins using the table (Transactions) twice.

Thanks for any help.
Sean

If we try to handle all possible cases this is a nightmare.  By all
possible cases I include missing entries, shifts that cross over day
boundries, multiple entires, etc.

If we take the simplistic approach that the date is always the same
for in and out, and that both in and out each occur once a day, this
becomes simpler.

Something like the following may get you started.  I deals with
missing entries, but not double entries or shifts that cross date
boundries.

SELECT convert(datetime,
               convert(char(8),time,isnull(C1.time,C2.time),112)),
       isnull(C1.Cardno, C2.Cardno),
       convert(char(5),C1.time,108),
       convert(char(5),C2.time,108)
  FROM Clocks as C1
  FULL OUTER JOIN Clocks as C2
    ON C1.CardNo = C2.CardNo
   AND C1.type = 1
   AND C2.type = 2
   AND convert(char(8),time,C1.time,112) =
       convert(char(8),time,C2.time,112)

Roy