Rob774 Rob774 - 10 months ago 34
SQL Question

single-row subquery returns more than one row Help Please

I am trying to build a simple quary for working out times when staff clock in and clock out. The DB records the person's name, if their clocking in or out and the time. It records more then that but I don't need that info.

So an example DB would be:

Id | User | In_out | Date_Time
2 | Bob | In | 13/Oct/16 9:30:35AM
3 | Ken | In | 13/Oct/16 9:34:27AM
4 | Jon | In | 13/Oct/16 9:34:46AM
5 | Billy | In | 13/Oct/16 9:52:06AM
6 | Bob | Out | 13/Oct/16 4:30:05PM
7 | Jon | Out | 13/Oct/16 4:32:55PM

The result I want to bring back in a SQL is:

User | Time_In | Time_Out
Bob | 9:30:35AM | 4:30:05PM
Jon | 9:34:46AM | 4:32:55PM

The SQL I am trying is not right I know that. But what do I need to change to get the result I want?

Select Clock.Checkin_Checkout.User,
(SELECT TO_CHAR(Clock.Checkin_Checkout.DATETIME, 'hh:mi:ss AM')
FROM Clock.Checkin_Checkout
Where Clock.Checkin_Checkout.In_Out = 'In'
) As "Time In",

To_Char(Clock.Checkin_Checkout.Create_Datetime, 'hh:mi:ss AM') As "Time Out"
From Clock.Checkin_Checkout
Where Clock.Checkin_Checkout.In_Out = 'Out'
And Clock.Checkin_Checkout.Datetime >= '13/Oct/2016'

Any help would be appreciate.
Thanks Rob.

Answer Source

I don't think you need a sub-query. Something like the following may work...

select c1.User, c1.Date_Time as "Time_In", c2.Date_Time as "Time Out"
from Clock.Checkin_Checkout as c1
inner join Clock.Checkin_Checkout as c2 on c2.User = c1.User
where c1.In_Out = 'In'
and c2.In_Out = 'Out'

Please excuse possible syntax errors. I haven't used Oracle for ages, and I don't have any way to test this.