Find Guide who work on more than one tour on the same day

OUTING_ID	TOUR_ID	OUTING_START	GUIDE
21000167	100345	30-APR-21	982354
21000168	100345	16-MAY-21	982354
21000170	100348	17-JUN-21	183476
21000200	100411	21-MAY-21	982354
21000202	100411	03-JUN-21	183476
21000205	100419	21-MAY-21	982354
21000207	100437	07-MAY-21	208655

Above define table describe the data of outing table.

I need guide who are working on more than one tour on the same day. I need the below data through query.

OUTING_ID	TOUR_ID	OUTING_START	GUIDE
21000200	100411	21-MAY-21	982354
21000205	100419	21-MAY-21	982354

I tried with many solutions but I didn’t get what I want. I tried with below query too but didn’t work for me as I data want.

SELECT 
    "A2O".GUIDE 
FROM 
    A2_OUTING "A2O" 
WHERE 
    OUTING_START IN (SELECT OUTING_START 
                     FROM A2_OUTING 
                     GROUP BY OUTING_START 
                     HAVING COUNT(*) > 1);

I tried with this whole day and now I am frustrated doing this, please help me with this.

Thank you in advance!

Answer

For your solution, you need to be grouping by both outing_start and guide, and it looks like you want to project more than just the guide column.

As mathguy pointed out in another comment, this is only valid if the time component to your outing_start is all the same. You can use the trunc function to do this if needs be.

SELECT outing_id, tour_id, outing_start, guide FROM A2_OUTING "A2O" WHERE (OUTING_START, guide) IN (
    SELECT OUTING_START, guide FROM A2_OUTING GROUP BY OUTING_START, guide HAVING COUNT(*)>1
);

An alternative solution would be to use an analytic count so that you don’t need the join.

SELECT outing_id, tour_id, outing_start, guide 
FROM (
SELECT outing_id, tour_id, outing_start, guide
,count(*) over (partition by outing_start, guide) cnt
 FROM A2_OUTING ) sq
Where cnt > 1