By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,787 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Pls Help In Login And Logout

P: 5
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
Nov 5 '08 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
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:

Expand|Select|Wrap|Line Numbers
  1. select in.date, in.empid, in.datetimeIN, out.datetimeout
  2. from
  3. (select date, empid, datetime as datetimein from FirstTable where [in/out] ='IN') full outer join 
  4. (select date, empid, datetime as datetimeout from FirstTable where [in/out] ='OUT') out on in.empid = out.empid
  5.  
  6.  
Happy Coding!

-- CK
Nov 5 '08 #2

P: 5
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...
Nov 7 '08 #3

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

Thanks for helping
Nov 10 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.