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

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika