CODEWITHSUNDEEP
sqloracle-databasesubquery
Rob774
Rob774

Reputation: 3

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'

Upvotes: 0

Views: 215

Answers (3)

user1751825
user1751825

Reputation: 4309

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.

Upvotes: 0

user5683823
user5683823

Reputation:

It is not clear why you don't want to show Ken and Billy in your output (showing they clocked in but never clocked out), but if that's your requirement, you could do something like this:

select   user,  min(date_time) as time_in,  max(date_time) as time_out
from     checkin_checkout
group by user
having   count(*) = 2;

This should be more efficient than any join-based solution. This is what the query does: First it groups by user; then it keeps the groups that have two rows (one for IN and the other for OUT); then it selects the user, the earlier time (out of the two for that group) and calls it time_in, and the later time and it calls it time_out. I don't think it can be simpler than this.

Remarks:

(1) This assumes the table is already "grouped" by DATE (in your table all dates are the same). Otherwise you would need to also group by trunc(date_time) and to include trunc(date_time) among the result columns as well; other than that the query will work exactly the same way.

(2) You have the user column; I hope in your real-life data you don't have names, but some id or code that is unique to each user. "Bob" is not a unique identifier.

(3) The solution assumes the data is validated as it is inserted into the base table; so a "user" can have at most two rows, there is no "Out" row if there isn't an "In" row, the "Out" time is after the "In" time, etc.

Upvotes: 1

Insac
Insac

Reputation: 810

I've created an example here.

In the naive assumption that in the table there will be alwas one "In" and at most one "Out" for a single name, the following query should work:

Select c1.user, 
       to_char(MAX(CASE WHEN c1.In_Out='In' then c1.Date_Time end), 'hh:mi:ss AM') time_in,
       to_char(MAX(CASE WHEN c1.In_Out='Out' then c1.Date_Time end), 'hh:mi:ss AM') time_out 
  From Clock.Checkin_Checkout as c1
 group by c1.user
having MAX(CASE WHEN c1.In_Out='Out' then c1.Date_Time end) is not null;

If you want also the cases where there is no 'Out', just omit the "having" clause

Edited: more realistic scenario

If you want the query to be able to recognize multiple shifts from the same person, you might use this query:

select c1.user, c1.Date_Time as Time_In, c2.Date_Time as Time_Out
 from Clock.Checkin_Checkout c1
      join Clock.Checkin_Checkout c2 
        on c2.user= c1.user
 where c1.In_Out= 'In'
   and c2.In_Out= 'Out'
   and c1.Date_Time <c2.Date_Time 
   and not exists (select 1 from Clock.Checkin_Checkout c3
                    where c1.user=c3.user
                      and c1.Date_Time <c3.Date_Time 
                      and c3.Date_Time <c2.Date_Time )

I've also tested this solution on rextester.

Disclaimer: I started from @user1751825 query for this second scenario as it was simple to arrive to this solution from his query rather than from my previous one.

Upvotes: 2

Related Questions