Rob774 Rob774 - 1 month ago 6
SQL Question

Single-row subquery returns more than one row in Oracle

I am trying to build a simple query 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,
Clock.Checkin_Checkout.In_Out,
(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'

Answer

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 c1
inner join Clock.Checkin_Checkout 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.