>I have a table with userid, swipedate, swipetime.. this is a database for our door access.. which records all the door out and in of our employees.. this will also be the daily time record for our time attendance..
>
>I'd like to select from this table:
>
>1. The employee's first swipe as her time-in in the morning..
>2. The employee's last swipe as her time-out in the afternoon..
>3. Also, the employee's swipe in-out after 12 noon as his time-out-in during lunch break..
>
>I'd like to save this into another table.. with fields userid, swipedate, time1, time2, time3, time4.. to record her attendance for the day..
>
>How can I achieve this?
Evelyn,
Try
select UserID, min(SwipeTime) as MorningTime, max(SwipeTime) as EverningTime, 1 as TimePeriod
from RecordedInfo where SwipeDate = PassedDate group by userID into cursor c_InOut
select UserID min(SwipeTime) as MorningTime, max(SwipeTime) as EverningTime, 2 as TimePeriod
from RecordedInfo where SwipeDate = PassedDate and SwipeTime between
StartTime and EndTime group by UserID into cursor c_LunchBreak
insert into (RecordedTimes) (...)
(two inserts).
I was thinking if this is possible with one select - but could not figure this out. It is possible, I guess, using derived tables, but it would be essentially the same as two cursors.
If it's not broken, fix it until it is.
My Blog