newssa
SELECT
PART_DTLS.PARTICIPANT_ID,
PART_HDR.PARTICIPANT_NAME,
CPB.PERIOD_ID,
-- This lists the countries involved, e.g., "US, FR"
LISTAGG(PART_DTLS.COUNTRY_CODE, ', ') WITHIN GROUP (ORDER BY PART_DTLS.START_DATE) AS COUNTRIES,
-- Showing the specific date ranges to identify the overlap
MIN(PART_DTLS.START_DATE) AS EARLIEST_START,
MAX(PART_DTLS.END_DATE) AS LATEST_END,
COUNT(DISTINCT PART_DTLS.COUNTRY_CODE) AS DISTINCT_COUNTRY_COUNT
FROM CN_SRP_PARTICIPANT_DETAILS_ALL PART_DTLS
JOIN CN_SRP_PARTICIPANTS_ALL PART_HDR
ON PART_DTLS.PARTICIPANT_ID = PART_HDR.PARTICIPANT_ID
AND PART_DTLS.ORG_ID = PART_HDR.ORG_ID
JOIN CN_PERIODS_B CPB
ON PART_DTLS.START_DATE <= CPB.END_DATE
AND NVL(PART_DTLS.END_DATE, TO_DATE('9999-12-31', 'YYYY-MM-DD')) >= CPB.START_DATE
GROUP BY
CPB.PERIOD_ID,
PART_HDR.PARTICIPANT_NAME,
PART_DTLS.PARTICIPANT_ID
HAVING COUNT(DISTINCT PART_DTLS.COUNTRY_CODE) > 1
ORDER BY CPB.PERIOD_ID, PART_HDR.PARTICIPANT_NAME
Comments
Post a Comment