Skip to content
Advertisement

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.

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

Thank you in advance!

Advertisement

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.

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

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement