Roll up logic

rjhe221 · November 6, 2019, 4:46pm

Hi

I have the following sql logic

[code]

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(32,9),
@lnShares2 numeric(32,9),
@lnMarketValue1 Decimal(32,9),
@lnMarketValue2 Decimal(32,9),
@lnCoupRate1 float,
@lnCoupRate2 float,
@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' OR 
	INV_SECTYPE_COD = '56'
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
		SELECT @PK_ID
		SELECT @PK_ID2
		
		SELECT @lcCusip2_8
		SELECT @lcCusip1_8
		SELECT @lcCusip2_7
		
		SELECT @lcCusip1_7
		
		
				-- 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 AS INSID1
							SELECT @lcCusip2 AS INSID2
							
							-- Update the cusip
							-- Create a numeric @variable for abosulte value 
							DECLARE @FinalShares numeric
							DECLARE @FinalCouponRate float
			
							-- 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
			select  @PK_ID
							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

<\code>

it rolls up these two together


image32766×88 156 KB

 FUND	REC_TYPE	FUND_DT	ASSET_ID	POS_TYP	ISS_LONG_NM	SHARS	BASE_MKT_VAL	LOC_MKT_VAL	BASE_ID_COST	LOC_ID_COST	BASE_AVG_COST	MKT_PRC_BASE	MKT_PRC_LOC	BASE_AMORT	ORG_COST_BASE_ST	MAT_DT	INT_RT	INT_RAT_II	VAR_RT_IND	DENOM_CURR	MKT_PRC_CURR_COD	LOC_CURR	BASE_CURR	MAJ_IND	MNR_IND	COUNTRY	ISS_COUNTRY	INV_SECTYPE_COD	ISS_CLASS_COD	CURR_XRATE	XRATE_ON_SETTLEMENT	ORG_STRIKE_PRC	CRNT_STRIKE_PRC	UNREALIZED_APP_DEP_BASE	UNREALIZED_APP_DEP_LOC	NO_CONTRACTS	ORIG_MULTIPLIER	MULTIPLIER	OPTION_TYP	INIT_MGN	SEC_ON_LOAN	ON_LOAN_QTY	MKT_VAL_SEC_ON_LOAN_BASE	MKT_VAL_SEC_ON_LOAN_LOC	FWD_AMT_BASE	FWD_AMT_LOC	CURR_BOUGHT	FWD_AMT_SOLD_BASE	FWD_AMT_SOLD_LOC	CURR_SOLD	CONTRACT_TYP	SWP_CROSS_IND	FWD_SSBTRD_ID	DATED_DT	ACCRD_BTL	ACCRD_LTL	ORG_FACE_POS_QTY	REC_RT_TYPE_CD	PAY_RT_TYPE_CD	REC_UNDRLNG_DESC	PAY_UNDRLNG_DESC	REC_UNDRLNG_ASSET_TYP	PAY_UNDRLNG_ASSET_TYP	PRICE_UNDERLYING	BANKING_COUNTERPARTY	SNP_RATE_CTRPTY	UNDRG_ISIN	UNDRG_CUSIP	RED_CD	BROKER_ID	ASSET_GRP_CD	ASSET_GRP_NM	BASE_CRNCY_NM	FASPRC_SRC_CD	INVEST_TYPE_NM	ISSUE_SHORT_NM	TRD_CNTRY_NM	TRD_CRNCY_NM	PAY_FREQ	ACCRUED_SWAPINT_AMOUNT	SHARS_LN	BASE_MKT_VAL_LN	LOC_MKT_VAL_LN	BASE_ID_COST_LN	LOC_ID_COST_LN	MKT_PRC_BASE_LN	MKT_PRC_LOC_LN	BASE_AMORT_LN	UNREALIZED_APP_DEP_BASE_LN	UNREALIZED_APP_DEP_LOC_LN	ACCRD_BTL_LN	ACCRD_LTL_LN	ORG_FACE_POS_QTY_LN	BRKR_NM	XCH_CD	UNDRLNG_ASSET_ID	UNDRLNG_DESC_LONG_NM	ID_ISIN	INCM_FREQ_CD	TRD_CNTRY_CD	LAST_INCM_DT

GS5C	HL	20190628	99S0R6NO3	L	GIB007RRI TRS GBP R V 01MFEDL0CFD 3092725 GSIL GBP L COST	326237.53	415202.57	326237.53	414416.04	326237.53	414416.04	127.27002	100	0	414416.04	20991231	1	1		GBP	GBP	GBP	USD	SW	SN	GB	GB	54		0.785731	0	0	0	786.53	0	0	0	0		0		0	0	0	0	0		0	0				9999999	20160225	0	0	0							0						501										0	326237.53	415202.57	326237.53	414416.04	326237.53	127.27002	100	0	786.53	0	0	0	0	GOLDMAN SACHS INTERNATIONAL		IDX000247	BBA LIBOR GBP 1 MONTH INDEX		AM	GB	20170124

GS5C	HL	20190628	99S0R6NP0	S	GIB007RRI TRS GBP P E         CFD 3092725 GSIL GBP S PRICE	-11404	-370103.76	-290802	-414416.01	-326237.51	-414416.01	32.453855	25.5	0	-414416.01	20991231	0	0		GBP	GBP	GBP	USD	SW	SO	GB	GB	55		0.785731	0	0	0	44312.25	35435.51	0	0	0		0		0	0	0	0	0		0	0				9999999	19010101	0	0	0							0						501										0	-11404	-370103.76	-290802	-414416.01	-326237.51	32.453855	25.5	0	44312.25	35435.51	0	0	0	GOLDMAN SACHS INTERNATIONAL						GB	

<\code>

but not



image32766×59 15.9 KB
 GS5C		HL		20190628		99S0S7P91		L		GIO0098WK TRS USD R V 01MFEDL0CFD BQXZP64 GSIL USD L COST		386158.14		386158.14		386158.14		386158.14		386158.14		386158.14		100		100		0		386158.14		20991231		1		1				USD		USD		USD		USD		SW		SN		US		US		54				1		0		0		0		0		0		0		0		0				0				0		0		0		0		0				0		0								9999999		20160513		0		0		0														0												501																				0		386158.14		386158.14		386158.14		386158.14		386158.14		100		100		0		0		0		0		0		0		GOLDMAN SACHS INTERNATIONAL				IDX000825		FED FUNDS EFFECTIVE RATE US INDEX				AM		US		20190624

GS5C		HL		20190628		99S0S7PA8		S		GIO0098WK TRS USD P E         CFD BQXZP64 GSIL USD S PRICE		-2517		-367482		-367482		-386158.14		-386158.14		-386158.14		146		146		0		-386158.14		20991231		0		0				USD		USD		USD		USD		SW		SO		US		US		55				1		0		0		0		18676.14		18676.14		0		0		0				0				0		0		0		0		0				0		0								9999999		19010101		0		0		0														0												501																				0		-2517		-367482		-367482		-386158.14		-386158.14		146		146		0		18676.14		18676.14		0		0		0		GOLDMAN SACHS INTERNATIONAL												US		

<\code>
I think its due to this part with the +1

((ASCII(@lcCusip2_8) = ASCII(@lcCusip1_8)+1)
but dont know why it is in the logic

ScottPletcher · November 6, 2019, 4:56pm

That's a hack way of seeing if the 2nd character is one more than the 1st char, for example, 'b' instead of 'a', or '8' vs '7'. Of course certain chars are not in ascii order, so that could be a matching issue. Hard to tell without specific data.

rjhe221 · November 6, 2019, 5:15pm

Hi

I didnt write the code. Finding it hard to follow.
so if its 9 and you add the plus 1 you get 10. maybe the person thought you would get A.
or does it go 1-9 then a -z

ScottPletcher · November 6, 2019, 6:33pm

You don't get 10 because 9 isn't 9 in ascii. 9 is 57 in ascii. Adding 1 is 58, which is a colon (:). That could very well be an issue ... or not, depending on how exactly the values in the table are originally assigned.

rjhe221 · November 7, 2019, 10:50am

is there a way i can find out what the ascii values are while the sql is running?.so i can compare one with the other

ScottPletcher · November 7, 2019, 4:16pm

Yes. The code is already SELECTing them, but they're not labeled and that would be hard to follow.

I suggest creating a table you can INSERT into that will show you for each iteration what the values of all the variables were.

Roll up logic

-- Loop thru distinct list of funds

CLOSE cDisFunds; DEALLOCATE cDisFunds;

CLOSE cDisFunds;
DEALLOCATE cDisFunds;