final bb query step 3

-- Step 3: Aggregate all currencies into single row per participant BB_AMT AS ( SELECT PAYRUN_ID, CREDITED_PARTICIPANT_ID, PAY_PERIOD_ID, -- Aggregate home currencies LISTAGG(HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_HOME_CURRENCIES, -- Latest home currency (same for all rows) MAX(LATEST_HOME_CURRENCY) AS LATEST_HOME_CURRENCY, -- Old period IDs MAX(OLD_PAY_PERIOD_IDS) AS OLD_PAY_PERIOD_IDS, -- Total BB in Latest Home Currency (sum across all currencies) SUM(BB_IN_LATEST_HC_NUM) AS BB_IN_LATEST_HC_NUM, -- Formatted total TO_CHAR( SUM(BB_IN_LATEST_HC_NUM), '0000000000000.0000000000000' ) AS BB_IN_LATEST_HC, -- Average conversion rate (for reference) TO_CHAR( AVG(CONVERSION_RATE_USD_TO_LC), '0000000000000.0000000000000' ) AS BB_CONVERSION_RATE_AVG, -- Breakdown by currency (for debugging) LISTAGG( HOME_CURRENCY_CODE || ':' || TO_CHAR(BB_AMOUNT_HOME_CURR, '999999999.99'), ' | ' ) WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_BREAKDOWN_BY_CURRENCY FROM BB_AMT_BY_CURRENCY GROUP BY PAYRUN_ID, CREDITED_PARTICIPANT_ID, PAY_PERIOD_ID )

Comments

Popular posts from this blog

Child query new

child query latest -

final simple where