SQLTeam.com | Weblogs | Forums

Roll up logic

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

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

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

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.

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

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.

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

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.