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!
CodePudding user response:
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
CodePudding user response:
You can use COUNT() OVER ()
analytic function such as
SELECT outing_id, tour_id, outing_start, guide
FROM (SELECT COUNT(*) OVER (PARTITION BY outing_start,guide) AS cnt, a.*
FROM a2_outing a)
WHERE cnt > 1