new aasas

SELECT PART_DTLS.PARTICIPANT_ID, PART_HDR.PARTICIPANT_NAME, CPB.PERIOD_ID, -- This helps you see the transition: LISTAGG(DISTINCT PART_DTLS.COUNTRY_CODE, ' -> ') WITHIN GROUP (ORDER BY PART_DTLS.START_DATE) AS COUNTRY_TRANSITION, COUNT(DISTINCT PART_DTLS.COUNTRY_CODE) AS DISTINCT_COUNTRIES FROM CN_SRP_PARTICIPANT_DETAILS_ALL PART_DTLS JOIN CN_SRP_PARTICIPANTS_ALL PART_HDR ON PART_DTLS.PARTICIPANT_ID = PART_HDR.PARTICIPANT_ID JOIN ( SELECT * FROM CN_PERIODS_B WHERE CALENDAR_ID = ( SELECT CALENDAR_ID FROM CN_CALENDARS_TL WHERE CALENDAR_NAME = 'Feb to Jan Fiscal Calendar' AND LANGUAGE = 'US' ) ) CPB ON TRUNC(PART_DTLS.START_DATE) <= TRUNC(CPB.END_DATE) AND TRUNC(NVL(PART_DTLS.END_DATE, TO_DATE('9999-12-31', 'YYYY-MM-DD'))) >= TRUNC(CPB.START_DATE) -- Apply the filter to all participants or keep your specific ID filter WHERE PART_HDR.PARTICIPANT_ID = 300000472435021 GROUP BY PART_DTLS.PARTICIPANT_ID, PART_HDR.PARTICIPANT_NAME, CPB.PERIOD_ID -- This ensures only periods with multiple countries are returned HAVING COUNT(DISTINCT PART_DTLS.COUNTRY_CODE) > 1 ORDER BY CPB.PERIOD_ID

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika