I am trying to find a group account and populate the aggregation of its child accounts QTY and Market value using CTE and Recursive CTE.. it gives me the correct result 3 times.. Not sure what i am missing here.
Scenario: Example Composite account CMP_1 contains the following account memberships. DIM_ACCOUNT_CONSTITUENT PARENT_ACCT_CD CHILD_ACCT_CD CMP_1 FND_A CMP_1 FND_B CMP_1 FND_C The holding for each account asof 11/13/2022 for all sources of data is shown below. FCT_POSITION_SECURITY_LEVEL SERVICE_ID POSITION_DATE ACCT_CD SEC_ID LONG_SHT_CD STRATEGY_ID QTY 1111 11/13/2022 FND_A 101 L ~NA~ 1000 1111 11/13/2022 FND_A 201 S ~NA~ 2000 1111 11/13/2022 FND_A 301 L ~NA~ 3000 1111 11/13/2022 FND_B 201 L ~NA~ 2000 1111 11/13/2022 FND_B 301 L ~NA~ 3000 1111 11/13/2022 FND_C 101 L ~NA~ 1000 1111 11/13/2022 FND_D 401 S ~NA~ 4000 2222 11/13/2022 FND_A 401 L ~NA~ 4000 2222 11/13/2022 FND_A 501 S ~NA~ 5000 2222 11/13/2022 FND_A 601 L ~NA~ 6000 2222 11/13/2022 FND_C 401 L ~NA~ 4000 2222 11/13/2022 FND_D 501 S ~NA~ 5000 When aggregation is applied, the following new data is created for the composite account. Notice the aggregation is based on the position business key POSITION_ID which is POSITION_DATE, ACCT_CD, SEC_ID, LONG_SHT_CD, and STRATEGY_ID. Not shown in this example is aggregation across any FCT_POSITION_SECURITY_LEVEL extension (_EXT) tables. Aggregation would work in the same way. SERVICE_ID POSITION_DATE ACCT_CD SEC_ID LONG_SHT_CD STRATEGY_ID QTY 1111 11/13/2022 CMP_1 101 L ~NA~ 2000 1111 11/13/2022 CMP_1 201 L ~NA~ 2000 1111 11/13/2022 CMP_1 201 S ~NA~ 2000 1111 11/13/2022 CMP_1 301 L ~NA~ 6000 1111 11/13/2022 CMP_1 401 S ~NA~ 4000 2222 11/13/2022 CMP_1 401 L ~NA~ 8000 2222 11/13/2022 CMP_1 501 S ~NA~ 10000 2222 11/13/2022 CMP_1 601 L ~NA~ 6000
Query:
WITH CTE AS (
SELECT
PS.SERVICE_ID,
PS.POSITION_DATE,
PARENT_ACCT_CD AS ACCT_CD,
PS.SEC_ID,
PS.LONG_SHT_CD,
PS.STRATEGY_ID,
PS.QTY,
PS.MKT_VAL
FROM
DIM_ACCOUNT_CONSTITUENT AC
INNER JOIN
FCT_POSITION_SECURITY_LEVEL PS
ON
AC.CHILD_ACCT_CD = PS.ACCT_CD
WHERE
AC.PARENT_ACCT_CD = 'CMP_1' AND
PS.POSITION_DATE =CURRENT_DATE()
),
REC_CTE AS (
SELECT
SERVICE_ID,
POSITION_DATE,
ACCT_CD,
SEC_ID,
LONG_SHT_CD,
STRATEGY_ID,
QTY,
MKT_VAL
FROM
CTE
UNION ALL
SELECT
CTE.SERVICE_ID,
CTE.POSITION_DATE,
DIM_ACCOUNT_CONSTITUENT.PARENT_ACCT_CD AS ACCT_CD,
CTE.SEC_ID,
CTE.LONG_SHT_CD,
CTE.STRATEGY_ID,
CTE.QTY,
CTE.MKT_VAL
FROM
CTE
INNER JOIN
DIM_ACCOUNT_CONSTITUENT
ON
CTE.ACCT_CD = DIM_ACCOUNT_CONSTITUENT.CHILD_ACCT_CD
WHERE
DIM_ACCOUNT_CONSTITUENT.PARENT_ACCT_CD <> 'CMP_1'
AND CTE.POSITION_DATE=current_date()
)
SELECT *
FROM REC_CTE;
