Help...

[kaleria]

I am using a SQL SERVER 2000...

My problem is that I would like to transfer my data in AttendanceLog Table To Attendance Table. Insert the first IN of the employee and first or last OUT of the employee. The EmpID should be non-occuring meaning only EmpID 1, 2, 3 will be transferred.

Conditions are the following:
1. Transfer the first login of the day and first/last logout of the day or the first logout next day.
2. Flexible Time - Render 9hrs or record the last logout.

I just want to transfer the all the logs from Table 1 to Table 2...

From AttendanceLog table

Date EmpID TransactionType DateTime
11/11/2008 1 IN 11/11/2008 8:00 AM // IN
11/11/2008 3 IN 11/11/2008 8:15 AM // IN
11/11/2008 2 IN 11/11/2008 8:30 AM // IN
11/11/2008 1 IN 11/11/2008 10:00 AM // Break
11/11/2008 1 IN 11/11/2008 12:01 PM // Lunch Break
11/11/2008 2 IN 11/11/2008 12:15 PM // Lunch Break
11/11/2008 3 IN 11/11/2008 1:30 PM // Lunch Break
11/11/2008 3 OUT 11/11/2008 5:30 PM // OUT
11/11/2008 4 IN 11/11/2008 5:35 PM // Night Shift
11/11/2008 1 OUT 11/11/2008 5:36 PM // OUT
11/11/2008 2 OUT 11/11/2008 5:38 PM //OUT
11/12/2008 4 OUT 11/12/2008 3:15 PM // OUT


TO Attendance Table

Date EmpID DateTimeIN DateTimeOut
11/11/2008 1 11/11/2008 8:00 AM 11/11/2008 5:36 PM
11/11/2008 2 11/11/2008 8:30 AM 11/11/2008 5:38 PM
11/11/2008 3 11/11/2008 8:15 AM 11/11/2008 5:30 PM
11/12/2008 4 11/11/2008 5:35 PM 11/12/2008 3:15 PM

Hope you would help me to implement this... I'm not good in sql

[Delerna]

The solution will be something like this hint.

[code=sql]
select [Date],EmpID,min(Date Time),max(DateT ime)
from AttendanceLog
group by [Date],EmpID
[/code]