querie from start
1. SELECT
P.PAYRUN_ID,
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID,
P.HOME_CURRENCY_CODE
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
where 1=1
and payee_participant_id = 106000
GROUP BY
P.PAYRUN_ID,
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID,
P.HOME_CURRENCY_CODE
- Output
PAYRUN_ID | PAYMENT_BATCH_PERIOD | PAYEE_PARTICIPANT_ID | HOME_CURRENCY_CODE | PAY_PERIOD_ID |
300000494369227 | 2026001 | 106000 | USD | 2026001 |
300000494369243 | 2026002 | 106000 | INR | 2026002 |
300000494369227 | 2026001 | 106000 | USD | 2025007 |
300000494369243 | 2026002 | 106000 | USD | 2026002 |
300000494369243 | 2026002 | 106000 | USD | 2026001 |
query 2
SELECT
P.PAYRUN_ID,
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID,
CASE
WHEN COUNT(DISTINCT P.HOME_CURRENCY_CODE) = 2 THEN 2
ELSE 1
END AS pay_2
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE 1 =1
and payee_participant_id = 106000
GROUP BY
P.PAYRUN_ID,
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID
PAYRUN_ID | PAYMENT_BATCH_PERIOD | PAYEE_PARTICIPANT_ID | PAY_2 |
300000494369243 | 2026002 | 106000 | 2 |
300000494369227 | 2026001 | 106000 | 1 |