I have only one Table TableA like below :

I want to achieve output as below :

I have the query as below but I'm not seeing any output :

Select ID from TableA 
WHERE 
(DATE BETWEEN '13-APR-22' AND '11-MAY-22') 
AND
(
(TYPE = 'BS' AND TYPE = 'GH') 
OR
(TYPE = 'GH' AND TYPE = 'MPN') 
OR
(TYPE = 'BS' AND TYPE = 'MPN')

);

I have also tried the following but I'm getting an error in the output :

Select ID from TableA WHERE (DATE BETWEEN '13-APR-22' AND '11-MAY-22') AND (TYPE = 'BS')

INTERSECT

Select ID from TableA WHERE (DATE BETWEEN '13-APR-22' AND '11-MAY-22') AND (TYPE = 'GH')

OR

Select ID from TableA WHERE (DATE BETWEEN '13-APR-22' AND '11-MAY-22') AND (TYPE = 'GH')

INTERSECT

Select ID from TableA WHERE (DATE BETWEEN '13-APR-22' AND '11-MAY-22') AND (TYPE = 'MPN')

OR

Select ID from TableA WHERE (DATE BETWEEN '13-APR-22' AND '11-MAY-22') AND (TYPE = 'BS')

INTERSECT

Select ID from TableA WHERE (DATE BETWEEN '13-APR-22' AND '11-MAY-22') AND (TYPE = 'MPN')

Please help me in achieving the output that I'm looking for. Thanks in advance!

CodePudding user response：

I would use an aggregation approach here:

WITH cte AS (
    SELECT ID
    FROM TableA
    WHERE "DATE" BETWEEN date '2022-04-13' AND date '2022-05-11'
    GROUP BY ID
    HAVING
        (COUNT(CASE WHEN TYPE = 'BS' THEN 1 END) > 0 AND
         COUNT(CASE WHEN TYPE = 'GH' THEN 1 END) > 0)
        OR
        (COUNT(CASE WHEN TYPE = 'GH' THEN 1 END) > 0 AND
         COUNT(CASE WHEN TYPE = 'MPN' THEN 1 END) > 0)
        OR
        (COUNT(CASE WHEN TYPE = 'BS' THEN 1 END) > 0 AND
         COUNT(CASE WHEN TYPE = 'MPN' THEN 1 END) > 0)
)

SELECT *
FROM TableA
WHERE ID IN (SELECT ID FROM cte);

CodePudding user response：

Use analytic functions and conditional aggregation so you do not have to query the table twice:

SELECT id, type, "DATE"
FROM   (
  SELECT t.*,
         COUNT(
           CASE
           WHEN TYPE = 'BS'
           AND  "DATE" >= DATE '2022-04-13'
           AND  "DATE" <  DATE '2022-05-12'
           THEN 1
           END
         ) OVER (PARTITION BY id) AS num_bs,
         COUNT(
           CASE
           WHEN TYPE = 'GH'
           AND  "DATE" >= DATE '2022-04-13'
           AND  "DATE" <  DATE '2022-05-12'
           THEN 1
           END
         ) OVER (PARTITION BY id) AS num_gh,
         COUNT(
           CASE
           WHEN TYPE = 'MPN'
           AND  "DATE" >= DATE '2022-04-13'
           AND  "DATE" <  DATE '2022-05-12'
           THEN 1
           END
         ) OVER (PARTITION BY id) AS num_mpn
  FROM   tableA t
)
WHERE (num_bs > 0 AND num_gh > 0)
OR    (num_bs > 0 AND num_mpn > 0)
OR    (num_gh > 0 AND num_mpn > 0)

Which, for the sample data:

CREATE TABLE tablea (ID, Type, "DATE") AS
SELECT 'First',  'BS',  DATE '2022-04-12' FROM DUAL UNION ALL
SELECT 'First',  'GH',  DATE '2022-05-10' FROM DUAL UNION ALL
SELECT 'First',  'MPN', DATE '2022-04-15' FROM DUAL UNION ALL
SELECT 'Second', 'BS',  DATE '2022-05-10' FROM DUAL UNION ALL
SELECT 'Second', 'MPN', DATE '2022-05-10' FROM DUAL UNION ALL
SELECT 'Third',  'GH',  DATE '2022-05-10' FROM DUAL UNION ALL
SELECT 'Third',  'BS',  DATE '2022-05-10' FROM DUAL UNION ALL
SELECT 'Fourth', 'MPN', DATE '2022-05-15' FROM DUAL UNION ALL
SELECT 'Fourth', 'GH',  DATE '2022-05-15' FROM DUAL UNION ALL
SELECT 'Fifth',  'GH',  DATE '2022-05-06' FROM DUAL;

Outputs:

ID	TYPE	DATE
First	GH	10-MAY-22
First	MPN	15-APR-22
First	BS	12-APR-22
Second	BS	10-MAY-22
Second	MPN	10-MAY-22
Third	GH	10-MAY-22
Third	BS	10-MAY-22

db<>fiddle here