Weighted Average Spread

gistfile1.sql

USE [clo1]
GO
/****** Object:  StoredProcedure [dbo].[spGet_CQT_WAS_AssetContribution]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/* =============================================
-- Description:	CQT_WAS_AssetContribution
************************************************
-- METHOLOGY:
-- For Each Asset:
--		Numerator: (Transaction_Outstanding * Asset_Libor_Spread)
					+ [(Transaction_Unfunded) * Asset_Unfunded_Spread]
--		Denominator: Transaction_Par_Amount
--		Contribution: Numerator / Sum of Transaction_Par_Amount for entire portfolio
************************************************
   TRACK CHANGES:
   Modifications:
   #1 --

-- =============================================*/

-- RUN STATEMENT: EXECUTE [dbo].[spGet_CQT_WAS_AssetContribution] DEFAULT, 't', DEFAULT, 308

ALTER PROCEDURE [dbo].[spGet_CQT_WAS_AssetContribution] 
	-- Add the parameters for the stored procedure here
    ( 
      @AsOfDate DATETIME = NULL
    , @TDSDBasis CHAR(1) = NULL
    , @CompositeID INT = NULL
    , @PortfolioID INT = NULL
    )
AS 
    BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
        SET NOCOUNT ON ;
        
    DECLARE @DebugMode TINYINT = 0	-- change to zero in production
    
    --===========================================================================================================================
    
    --BEGIN LOGIC
        
    -- Insert statements for procedure here
    
    IF OBJECT_ID('tempdb..#CQT_WASSumTransactionParAmount') IS NOT NULL
		DROP TABLE #CQT_WASSumTransactionParAmount;
		
	SELECT SUM(Transaction_Par_Amount)[Sum_Transaction_Par_Amount]
	INTO #CQT_WASSumTransactionParAmount
    FROM dbo.tfnMaster_AssetHoldings(@AsOfDate, @TDSDBasis, @CompositeID, @PortfolioID)
    
    IF @DebugMode <> 0 SELECT * FROM #CQT_WASSumTransactionParAmount
     
    IF OBJECT_ID('tempdb..#CQT_WASAssetFields') IS NOT NULL
		DROP TABLE #CQT_WASAssetFields;

    SELECT 
			Issuer_ID,
			Asset_ID,
			Portfolio_ID,
			Issuer_Name,
			Asset_Name,
			Portfolio_Name,
			Asset_Libor_Spread,
			Asset_Unfunded_Spread,
			Transaction_Outstanding,
			Transaction_Unfunded,
			(Transaction_Outstanding * Asset_Libor_Spread) 
			+ (Transaction_Unfunded * Asset_Unfunded_Spread) [Calculation_Numerator],
			(SELECT Sum_Transaction_Par_Amount FROM #CQT_WASSumTransactionParAmount) Sum_Transaction_Par_Amount
			
	INTO #CQT_WASAssetFields		
    FROM dbo.tfnMaster_AssetHoldings(@AsOfDate, @TDSDBasis, @CompositeID, @PortfolioID)
	ORDER BY Issuer_Name, Asset_Name
	
	IF @DebugMode <> 0 SELECT * FROM #CQT_WASAssetFields
	
	IF @PortfolioID IS NULL
	BEGIN
	   SELECT 0 [IssuerID],
	          0 [AssetID],
	          0 [PortfolioID],
	          0 [[CQTWASContributions]
	END
	ELSE
	BEGIN
	--BEWARE OF DIVIDE BY 0! ALWAYS WRAP DIVISION DENOMINATORS WITH NULLIF
	SELECT  Issuer_ID [IssuerID],
			Asset_ID [AssetID],
			Portfolio_ID [PortfolioID],
			Calculation_Numerator / NULLIF(Sum_Transaction_Par_Amount, 0) [CQTWASContributions]

	FROM #CQT_WASAssetFields
	END
	
	--END LOGIC
 
    --===========================================================================================================================
   
 END