Pls Help In Login And Logout

[kaleria]

Can somebody help me with my problem in sql... I have an attendancelog table where i have 3 columns Date, EmpID, IN/OUT, Date and Time. I would like to transfer this data in another column where it has Date, EmpID, IN Date and Time Column, and OUT Date and Time Column. I'm just new in sql i dont know how to implement it.

I am using sql server 2000. I have already a table with data in the first table. I need to transfer the data in the second table

For example:

1st Table Contain Logs
Column
Date EmpID IN/OUT Date and Time
10/11/2007 20 IN 10/11/2007 11:00 AM
10/11/2007 1 IN 10/11/2007 11:10 AM
10/11/2007 1 OUT 10/11/2007 10:00 PM
10/12/2007 20 OUT 10/12/2007 6:00 AM

I would like to transfer the IN and OUT of the the same EmpID to a single row

2nd Table
Date EmpID DateTime IN DateTime OUT
10/11/2007 20 10/11/2007 11:00 AM 10/12/2007 6:00 AM
10/11/2007 1 10/11/2007 11:10 AM 10/11/2007 10:00 PM

[ck9663]

Can somebody help me with my problem in sql... I have an attendancelog table where i have 3 columns Date, EmpID, IN/OUT, Date and Time. I would like to transfer this data in another column where it has Date, EmpID, IN Date and Time Column, and OUT Date and Time Column. I'm just new in sql i dont know how to implement it.

I am using sql server 2000. I have already a table with data in the first table. I need to transfer the data in the second table

For example:

1st Table Contain Logs
Column
Date EmpID IN/OUT Date and Time
10/11/2007 20 IN 10/11/2007 11:00 AM
10/11/2007 1 IN 10/11/2007 11:10 AM
10/11/2007 1 OUT 10/11/2007 10:00 PM
10/12/2007 20 OUT 10/12/2007 6:00 AM

I would like to transfer the IN and OUT of the the same EmpID to a single row

2nd Table
Date EmpID DateTime IN DateTime OUT
10/11/2007 20 10/11/2007 11:00 AM 10/12/2007 6:00 AM
10/11/2007 1 10/11/2007 11:10 AM 10/11/2007 10:00 PM

try:

select in.date, in.empid, in.datetimeIN, out.datetimeout
from
(select date, empid, datetime as datetimein from FirstTable where [in/out] ='IN') full outer join 
(select date, empid, datetime as datetimeout from FirstTable where [in/out] ='OUT') out on in.empid = out.empid
 
 

Happy Coding!

-- CK

[kaleria]

It wont to work...

The following are the fullname of the tables:
wherein the [LogTransType] = [LogTransDesc]
0 IN
1 OUT

From this data
[LogDate] [LogEmpID] [LogTransType] [LogTransDesc] [LogDateTime]
10/11/2007 20 0 IN 10/11/2007 11:00 AM
10/11/2007 10 0 IN 10/11/2007 11:10 AM
10/11/2007 10 1 OUT 10/11/2007 10:00 PM
10/12/2007 20 1 OUT 10/12/2007 6:00 AM

To this data
[LogDate] [LogEmpID] [LogIN] [LogOUT]
10/11/2007 20 10/11/2007 11:00 AM 10/12/2007 6:00
10/11/2007 10 10/11/2007 11:10 AM 10/11/2007 10:00


Please help again...

[kaleria]

Thanks... for the code. The code really works its just it was missing an in alias before the FULL OUTER JOIN.

Thanks for helping