Country transfer v1
SELECT
PART_DTLS.PARTICIPANT_ID,
PART_HDR.PARTICIPANT_NAME,
CPB.PERIOD_ID,
PART_HDR.ORG_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 (select * from CN_SRP_PARTICIPANTS_ALL where ORG_ID = (SELECT bu_id FROM fun_all_business_units_v WHERE bu_name = 'ICM_DIRECT_INCTV')) 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 1 =1
--and PART_HDR.PARTICIPANT_ID = 300000472435021
and CPB.Period_id = nvl(:Period_id, CPB.Period_id)
GROUP BY
PART_DTLS.PARTICIPANT_ID,
PART_HDR.PARTICIPANT_NAME,
PART_HDR.ORG_ID,
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
Post a Comment