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