BB for Big query
BB_AMT AS (
SELECT P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID "CREDITED_PARTICIPANT_ID",
P.ORG_ID "ORG_ID",
PB.PAY_PERIOD_ID "PAY_PERIOD_ID",
SUM(P.PAY_AMT_PAY_CURR) B_BALANCE
FROM CN_TP_PARTICIPANT_PAY_ALL P
JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = P.PAYRUN_ID
AND PB.ORG_ID = P.ORG_ID
JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD
ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID
AND P.ORG_ID = PD.ORG_ID
WHERE (( P.SOURCE_EVENT_DATE IS NOT NULL AND P.SOURCE_EVENT_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE,P.SOURCE_EVENT_DATE))
OR ( P.SOURCE_EVENT_DATE IS NULL AND PB.PAY_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE,PB.PAY_DATE)) )
AND P.PAY_PERIOD_ID <> PB.PAY_PERIOD_ID
--AND P.CREDITED_PARTICIPANT_ID = 134135
--AND P.ORG_ID = 300000008656040
--AND PAY_VW.PAY_PERIOD_ID = 2025007
GROUP BY P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
PB.PAY_PERIOD_ID
),
Comments
Post a Comment