I am trying to compare account security weights with benchmark security weights but struggling to get the results correct. I want a result set where for each account and date there is only 1 row for each SEC_ID so that I can see the holding weight for the account and benchmark side by side.

How can I do that?

In my test, the account has 52 holdings, the benchmark has 869. There are 47 securities that are shared between the two, so the combined output should be 874 SEC_IDs.

I have tried FULL OUTER JOIN and that produced 45,000+ (52 x 869) rows with every combination of ACCT/BM possible.

I am now trying UNION but running into an issue when SEC_ID that are held by both the account and the benchmark. Those are showing up as two separate rows and I want them to be on one row. As an example, SEC_ID 100129 is held by the account and benchmark

Next steps after getting this working: calculate weight difference; expand universe of accounts and dates

declare @BeginDate DATE = '2025-01-03';
declare @EndDate DATE = '2025-01-03';

DROP TABLE IF EXISTS #TempResults;

WITH BUSINESS_DATES AS (
    SELECT
        CONVERT(date, DATE) AS DATE_AS_OF
    FROM
        holdings.dbo.dates_us_trading D
    WHERE
    DATE BETWEEN @BeginDate AND @EndDate
)
-- 
SELECT 
    D.ACCT_ID,
    D.ACCT_SHORTNAME,
    D.ACCT_NAME,
    CONVERT(date,D.INCEPTION_DATE) as INCEPTION_DATE,
    D.BENCHMARK,
    L.ID as BENCHMARK_ID,
    BD.DATE_AS_OF
INTO #TempResults
FROM 
    BUSINESS_DATES BD
    CROSS JOIN VWACCOUNT_DETAILS D
    JOIN LKP_LOOKUPDETAIL L on D.BENCHMARK = L.DESCRIPTION
WHERE
    D.ACCT_ID = 123456


--Account holdings
SELECT 
    TR.*,
    AP.SEC_ID as SEC_ID,
    AP.MKT_PCT as AP_WEIGHT,
    null as BP_WEIGHT
FROM 
    #TempResults TR
    JOIN ACCT_POSITION AP ON AP.ACCT_ID = TR.ACCT_ID and AP.DATE_AS_OF = TR.DATE_AS_OF

-------------------------------------------
UNION
-------------------------------------------

--Benchmark holdings
SELECT 
    TR.*,
    BP.SEC_ID as SEC_ID,
    null as AP_WEIGHT,
    BP.WEIGHT as BP_WEIGHT
FROM 
    #TempResults TR
    JOIN BENCHMARK_POSITION BP ON BP.BENCHMARK_ID = TR.BENCHMARK_ID and BP.DATE_AS_OF = TR.DATE_AS_OF

I am trying to compare account security weights with benchmark security weights but struggling to get the results correct. I want a result set where for each account and date there is only 1 row for each SEC_ID so that I can see the holding weight for the account and benchmark side by side.

How can I do that?

In my test, the account has 52 holdings, the benchmark has 869. There are 47 securities that are shared between the two, so the combined output should be 874 SEC_IDs.

I have tried FULL OUTER JOIN and that produced 45,000+ (52 x 869) rows with every combination of ACCT/BM possible.

I am now trying UNION but running into an issue when SEC_ID that are held by both the account and the benchmark. Those are showing up as two separate rows and I want them to be on one row. As an example, SEC_ID 100129 is held by the account and benchmark

Next steps after getting this working: calculate weight difference; expand universe of accounts and dates

declare @BeginDate DATE = '2025-01-03';
declare @EndDate DATE = '2025-01-03';

DROP TABLE IF EXISTS #TempResults;

WITH BUSINESS_DATES AS (
    SELECT
        CONVERT(date, DATE) AS DATE_AS_OF
    FROM
        holdings.dbo.dates_us_trading D
    WHERE
    DATE BETWEEN @BeginDate AND @EndDate
)
-- 
SELECT 
    D.ACCT_ID,
    D.ACCT_SHORTNAME,
    D.ACCT_NAME,
    CONVERT(date,D.INCEPTION_DATE) as INCEPTION_DATE,
    D.BENCHMARK,
    L.ID as BENCHMARK_ID,
    BD.DATE_AS_OF
INTO #TempResults
FROM 
    BUSINESS_DATES BD
    CROSS JOIN VWACCOUNT_DETAILS D
    JOIN LKP_LOOKUPDETAIL L on D.BENCHMARK = L.DESCRIPTION
WHERE
    D.ACCT_ID = 123456


--Account holdings
SELECT 
    TR.*,
    AP.SEC_ID as SEC_ID,
    AP.MKT_PCT as AP_WEIGHT,
    null as BP_WEIGHT
FROM 
    #TempResults TR
    JOIN ACCT_POSITION AP ON AP.ACCT_ID = TR.ACCT_ID and AP.DATE_AS_OF = TR.DATE_AS_OF

-------------------------------------------
UNION
-------------------------------------------

--Benchmark holdings
SELECT 
    TR.*,
    BP.SEC_ID as SEC_ID,
    null as AP_WEIGHT,
    BP.WEIGHT as BP_WEIGHT
FROM 
    #TempResults TR
    JOIN BENCHMARK_POSITION BP ON BP.BENCHMARK_ID = TR.BENCHMARK_ID and BP.DATE_AS_OF = TR.DATE_AS_OF

• sql
• sql-server

• 1 Please provide example records from holdings.dbo.dates_us_trading, VWACCOUNT_DETAILS, LKP_LOOKUPDETAIL, ACCT_POSITION, and BENCHMARK_POSITION – Bart McEndree Commented Feb 7 at 18:16
• Consider grouping by SEC_ID and using the max of AP_WEIGHT and BP_Weight – Bart McEndree Commented Feb 7 at 18:17
• 2 stackoverflow.com/help/someone-answers – Dale K Commented Feb 7 at 19:22
• 1 As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Commented Feb 8 at 3:23

1 Answer 1

You can group by date and SEC_ID and then take the sum of the other columns:

SELECT
    DATE_AS_OF,
    SEC_ID,
    SUM(x.AP_WEIGHT) AS AP_WEIGHT,
    SUM(x.BP_WEIGHT) AS BP_WEIGHT,
    SUM(x.WEIGHT_DIFF) AS WEIGHT_DIFF
FROM
    (<your union query here>) x
GROUP BY
    DATE_AS_OF,
    SEC_ID
ORDER BY
    DATE_AS_OF,
    SEC_ID;