UNION Two Queries and then Aggregate

2 votes

1 answer

137 views

                          I have two different queries that I need to UNION together and then aggregate each rate type group together into a final table.  For each rate group (Rate_001mo, Rate_003mo, etc.)

    SELECT EffectiveDate, 
    SUM(CASE WHEN RateName = 'FHLBCSOFR006M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_001mo,
    SUM(CASE WHEN RateName = 'FHLBCSOFR006M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_003mo,
    SUM(CASE WHEN RateName = 'FHLBCSOFR006M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_006mo,
    SUM(CASE WHEN RateName = 'FHLBCSOFR012M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_012mo,
    SUM(CASE WHEN RateName = 'FHLBCSOFR024M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_024mo,
    SUM(CASE WHEN RateName = 'FHLBCSOFR036M' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_036mo
    FROM dbo.STAGING_FhlbRates WHERE EffectiveDate = '20250131'
    group by EffectiveDate
    
    UNION
    
    SELECT EffectiveDate,
    	SUM(CASE WHEN RateName = 'USOSFRA' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_001mo,
    	SUM(CASE WHEN RateName = 'USOSFRC' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_003mo,
    	SUM(CASE WHEN RateName = 'USOSFRF' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_006mo,
    	SUM(CASE WHEN RateName = 'USOSFR1' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_012mo,
    	SUM(CASE WHEN RateName = 'USOSFR2' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_024mo,
    	SUM(CASE WHEN RateName = 'USOSFR3' THEN InterestRate ELSE 0 END) / 100.0 AS Rate_036mo
    	FROM dbo.STAGING_SofrOisRates WHERE EffectiveDate = '20250131'
    	GROUP BY EffectiveDate

The columns for the final results table should include Effective Date and then each rate group (Rate_001mo, Rate_003mo, etc.)
                        

Asked by Daylon Hunt (27 rep)

Apr 4, 2025, 06:56 PM
Last activity: Apr 4, 2025, 07:17 PM

UNION Two Queries and then Aggregate

Related Questions