Putting related Two records to one row

[Praveen 123]

Hello everybody, I have a Attendance table, which has columns Date. ShiftInTime, ShiftOutTime, EventTime, Login_Type. Now for every EventCode= "I" or "O" there are two records. That means for a day say 07/10/2006 as Workdate there are two records like, EmpID Date EventTime EventCode 1 07/10/2006 9:00 I 1 07/10/2006 17:15 O NOW.. I want a report in Table or Crystal report which will have a single record for a single Workdate..as folllows EmpID WorkDate ShiftInTime ShiftOutTime InTime OutTime 1 07/10/2006 09:00 17:00 9:00 17:15 Can you please help me out..I'm usng a table in ASp

Praveen.K System Engineer Graviton Technologies Pvt.Ltd

[Sylvester george]

select * from table t1 inner join table t2 on t1.date = t2.date

Regards, Sylvester G Senior Software Engineer Xoriant Solutions sylvester_g_m@yahoo.com

[Praveen 123]

Sir, U'r suggestion is not enough...because i am using onlu only table ,,,my quear gave me theattendance report like i shown above...but Can i simply make the inner join with the same table? i need the out put as follows ---------------------------------- ID |Name |InTime |OutTime| 1 |xxxxxxxxxxx| 09:05 | 06:01 2 |sdsssdddddd| 06:15 | 06:15 ----------------------------------- Like that..But at present they are displaying as i explainned in my first question.

Praveen.K System Engineer Graviton Technologies Pvt.Ltd

[Pete OHanlon]

Try this:

SELECT main.EmpId, main.Date, 
(SELECT i.EventTime FROM Attendance i 
WHERE i.EmpID = main.EmpId AND 
i.Date = main.Date AND i.Login_Type = 'I') AS ShiftInTime, 
(SELECT o.EventTime FROM Attendance o
WHERE o.EmpID = main.EmpID AND
o.Date = main.Date AND o.Login_Type = 'O') AS ShiftOutTime
FROM Attendance main

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.

[Praveen 123]

thanks for u'r advide, But I am using only one table namedv History with following tables EmpId,EmpName,ShiftIn,ShiftOut,EventTime,LoginType From Event Time I am extracting the values based on the LoginType,,Sir u got me...That is only one table is there....(History)....One doubt regarding that u specified the main,i,o as defrent tables or not?

Praveen.K System Engineer Graviton Technologies Pvt.Ltd

[Pete OHanlon]

i, main and o are not different tables. They are all table aliases. If you look at the syntax carefully, you will see that this is a way to use items from the same table in sub-items. Change the references from Attendance to History, but leave the table aliases alone.

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.

[Praveen 123]

Sir, I tried but i am getting the error message as "At most one record can be returned by this sub query" ? This is the code thai i tried SELECT main.UserName, (SELECT i.EventTime FROM History i WHERE i.FuncCode="0") AS InTime, (SELECT o.EventTime FROM History o WHERE o.FuncCode="10") AS OutTime FROM History main Any chance of mistake in this code?

Praveen.K System Engineer Graviton Technologies Pvt.Ltd

[Pete OHanlon]

That'll teach me to do it away from SQL Server. Here you go:

SELECT     
  h.EmpID, 
  h.[Date], 
  h.EventTime AS ShiftInTime, 
  h2.EventTime AS ShiftOutTime
FROM 
  History h 
LEFT OUTER JOIN
  History h2 
ON 
  h.EmpID = h2.EmpID AND 
  h.[Date] = h2.[Date] AND 
  h2.EventCode = 'O'
WHERE
  h.EventCode = 'I'

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.

[Pete OHanlon]

Hmm. You and this guy Clickety[^] seem to be trying to solve the same problem. Coincidence? Homework?

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.

[Praveen 123]

Sir, Some changes i made from u'r query ..any way u'r concept worked here,,,Thanks for u'r assistance sir This is the code i used..... SELECT h.UserName, h.EventDate, h.EventTime AS InTime, h2.EventTime AS OutTime, h.Dept FROM History AS h LEFT JOIN History AS h2 ON (h.UserName = h2.UserName) AND (h.EventDate = h2.EventDate) WHERE (((h.EventDate)=#2/24/2006#) AND ([h.FuncCode]="0") AND ([h2.FuncCode]="10") AND ((h.Dept)="Engg")); Thanks and Regards

Praveen.K System Engineer Graviton Technologies Pvt.Ltd

[bernie_011]

i have 3 columns in my table the room,timeStart,timeEnd. For example i have inserted this data room: room1 timeStart: 2007-03-02 8:00 AM timeEnd: 2007-03-02 3:00 PM Here's the data that will not be accepted if try to insert room: room1 timeStart: 2007-03-02 9:00 AM timeEnd: 2007-03-02 4:00 PM because the timestart is between the timeStart and the timeEnd. Room1 will only be occupied after 3:00 PM. Can you help me with this?. Thank you very Much Bernie