SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF

GO
-- Declare @variables
DECLARE 
@NextFund NVARCHAR(4),
		@PK_ID int,
		@PK_ID2 int,
		@lcCusip1 NVARCHAR(9),
		@lcCusip1_6 NVARCHAR(6),
		@lcCusip1_7A NVARCHAR(7),	
		@lcCusip1_7 NVARCHAR(1),
		@lcCusip1_8 NVARCHAR(1),
		@lcCusip2 NVARCHAR(9),
		@lcCusip2_6 NVARCHAR(6),
		@lcCusip2_7A NVARCHAR(7),	
		@lcCusip2_7 NVARCHAR(1),
		@lcCusip2_8 NVARCHAR(1),
		@lnShares1 numeric,
		@lnShares2 numeric,
		@lnMarketValue1 numeric,
		@lnMarketValue2 numeric,
		@lnCoupRate1 numeric,
		@lnCoupRate2 numeric,
		@WorkingDataloadTable NVARCHAR(256)

SELECT @WorkingDataloadTable = 'SSCIREWorkingDataloadFile'
			
	-- Select all swaps (non - OTC Hub) from working table.
	SELECT *, LEFT(ASSET_ID,6) AS ASSET_ID_6, SUBSTRING(ASSET_ID,7,1) AS ASSET_ID_7A, 
				LEFT(ASSET_ID,7) AS ASSET_ID_7, SUBSTRING(ASSET_ID,8,1) AS ASSET_ID_8
	INTO #Swaps
	FROM SSCIREWorkingDataloadFile
	WHERE INV_SECTYPE_COD = '50' OR
		INV_SECTYPE_COD = '51' OR
		INV_SECTYPE_COD = '52' OR
		INV_SECTYPE_COD = '53' OR
		INV_SECTYPE_COD = '54' OR 
		INV_SECTYPE_COD = '55'
	ORDER BY FUND, ASSET_ID
	
	-- Get list of distict funds, that have swaps
	DECLARE cDisFunds CURSOR FOR
	SELECT DISTINCT FUND FROM #Swaps sw
	
-- Loop thru distinct list of funds 
---
OPEN cDisFunds
FETCH NEXT FROM cDisFunds INTO @NextFund  

WHILE @@FETCH_STATUS = 0   
BEGIN   
		
		DECLARE cSwapsInTransit SCROLL CURSOR FOR
		SELECT PK_ID, ASSET_ID, ASSET_ID_6, ASSET_ID_7A, ASSET_ID_7, ASSET_ID_8, SHARS, BASE_MKT_VAL,ROUND(INT_RT,6)
		FROM #Swaps
		WHERE FUND = @NextFund
		ORDER BY FUND, ASSET_ID
       
        OPEN cSwapsInTransit
		-- Get current records details 
		FETCH NEXT FROM cSwapsInTransit INTO @PK_ID, @lcCusip1, @lcCusip1_6, @lcCusip1_7A, @lcCusip1_7, @lcCusip1_8,@lnShares1, @lnMarketValue1, @lnCoupRate1
		WHILE @@FETCH_STATUS = 0 
		BEGIN 
			
			FETCH PRIOR FROM cSwapsInTransit INTO @PK_ID, @lcCusip1, @lcCusip1_6, @lcCusip1_7A, @lcCusip1_7, @lcCusip1_8,@lnShares1, @lnMarketValue1, @lnCoupRate1
			
			-- Get next records details
			FETCH NEXT FROM cSwapsInTransit INTO @PK_ID2, @lcCusip2, @lcCusip2_6, @lcCusip2_7A, @lcCusip2_7, @lcCusip2_8, @lnShares2, @lnMarketValue2, @lnCoupRate2
		
			--SELECT @lcCusip1
			--SELECT @lcCusip2
			
					-- Check to see if there is a roll up match
					IF ((ASCII(@lcCusip2_8) = ASCII(@lcCusip1_8) + 1) AND (LTRIM(@lcCusip1_7) = LTRIM(@lcCusip2_7)) OR
						(ASCII(@lcCusip1_8) = 90 AND ASCII(@lcCusip2_8) = 48) AND (ASCII(@lcCusip1_7A) = ASCII(@lcCusip2_7A) - 1) OR
							(ASCII(@lcCusip1_8) = 90 AND ASCII(@lcCusip2_8) = 48) AND (ASCII(@lcCusip1_7A) = 56 AND ASCII(@lcCusip2_7A) = 65))
						BEGIN
						
								--SELECT @lcCusip1
								--SELECT @lcCusip2
								
								-- Update the cusip
								-- Create a numeric @variable for abosulte value 
								DECLARE @FinalShares numeric
								DECLARE @FinalCouponRate numeric
				
								-- Get absolute value
								IF (ABS(@lnShares1) > ABS(@lnShares2))
									SET @FinalShares = ABS(@lnShares1)
								ELSE
									SET @FinalShares = ABS(@lnShares2)
									
								-- Get absolute value Market Value 
								-- Check to see which leg should take Coup Rate
								IF (ABS(@lnMarketValue1) > ABS(@lnMarketValue2))
									SET @FinalCouponRate = @lnCoupRate1
								ELSE
									SET @FinalCouponRate = @lnCoupRate2
			
								-- Create rolled up (summed) swap record
								SELECT SUM(BASE_MKT_VAL) AS TotBaseMktVal, 
									SUM(BASE_AVG_COST) AS TotCost, 
									SUM(BASE_AVG_COST) AS TotYield ,
									SUM(UNREALIZED_APP_DEP_BASE) AS TotUnreal,
									SUM(OptionPremium) AS TotOptPrem,
									SUM(NotionalAmount) AS TotNotAmt
								INTO #SwapsTemp
								FROM #Swaps
								WHERE PK_ID = @PK_ID OR PK_ID = @PK_ID2
								GROUP BY FundCode
				
								-- Update the swaps in the working table
								UPDATE SSCIREWorkingDataloadFile
								SET  UnrealizedValue = (SELECT TotUnreal FROM #SwapsTemp), 
									Cost = (SELECT TotCost FROM #SwapsTemp), 
									Yield = (SELECT TotYield FROM #SwapsTemp),
									MarketValue = (SELECT TotBaseMktVal FROM #SwapsTemp), 
									OptionPremium = (SELECT TotNotAmt FROM #SwapsTemp),
									Shares = @FinalShares,
									CouponRate=@FinalCouponRate,
									NotionalAmount = (SELECT TotOptPrem FROM #SwapsTemp)
								WHERE PK_ID = @PK_ID2
			    
								-- Delete out the duplicate
								DELETE FROM SSCIREWorkingDataloadFile WHERE PK_ID = @PK_ID 
								-- Kill the temp table	
								DROP TABLE #SwapsTemp	
						END
			
			FETCH NEXT FROM cSwapsInTransit INTO @PK_ID, @lcCusip1, @lcCusip1_6, @lcCusip1_7A, @lcCusip1_7, @lcCusip1_8,@lnShares1, @lnMarketValue2, @lnCoupRate2
						
		END
		CLOSE cSwapsInTransit;
		DEALLOCATE cSwapsInTransit;

  FETCH NEXT FROM cDisFunds INTO @NextFund
  
END   

CLOSE cDisFunds;
DEALLOCATE cDisFunds;
----

DROP TABLE #Swaps

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO