November 17, 2011 at 7:32 am
I have created a stored procedure that accepts a date as parameter and then goes on to produce an output of trade instruments, instrumentIDs (ISIN) and their associated assetclasscodes.
The information required to produce this output is based on 3 tables.
tbltradeA, tblTradeB and tblInstrumentType. TblTradeA and TblTradeB contain data on all trades from various systems. Within these tables are fields pertaining to the date a trade was made, the instrument traded (tradeType), and the instrumenteID. tblInstrumentType contains the assetclasscodes for every instrument traded. However an instrument can have a different assetclass code depending on the source system the instrument is traded through.
The procedure I have works but as the number of trade tables increases, which will happen, my code will become ever more expansive. I just wanted to know if you have any ideas on how to anyway to write this code in a much cleaner way.
The code is shown below
--- STORED PROCEUDRE
CREATE PROCEDURE sp_TradeISIN_List
@Period char(6) -- YYYYMM of the trade
,@Debug int = 0 -- For debugging
AS
SET NOCOUNT ON
SELECT F.ISIN
,F.InstrumentName
,F.AssetClassCode
FROM
--FOR CS SOURCE SYSTEM
(SELECT B.ISIN
,B.InstrumentName
,IT.AssetClassCode
from
(
SELECT ISIN
,(SELECT TOP 1 SecurityName FROM
(SELECT DISTINCT ISIN AS ISIN, SecurityName AS SecurityName FROM #tblTradeA WHERE ISIN IS NOT NULL AND left(TradeDate,6) > DATEADD(MM,-12,@Period)
) N
WHERE I.ISIN = N.ISIN
ORDER BY ISIN ) InstrumentName
,
(SELECT TOP 1 InstrumentType FROM
(SELECT DISTINCT ISIN AS ISIN, InstrumentType FROM #tblTradeA WHERE ISIN IS NOT NULL aND left(TradeDate,6) > DATEADD(MM,-12,@Period)
)M
where I.ISIN = M.ISIN
ORDER BY ISIN) InstrumentType
FROM (
SELECT DISTINCT ISIN AS ISIN FROM #tblTradeA WHERE ISIN IS NOT NULL AND left(TradeDate,6) > DATEADD(MM,-12,@Period)
) I
)B INNER JOIN #tblInstrumentType IT on
b.InstrumentType = it.SourceInstrumentCode
WHERE IT.DataSourceName = 'CS'
UNION
----for SCD SOURCE SYSTEM
SELECT B.ISIN
,B.InstrumentName
,IT.AssetClassCode
from
(
SELECT ISIN
,(SELECT TOP 1 SecurityName FROM
(
SELECT DISTINCT ISIN AS ISIN, SecurityName AS SecurityName FROM #tblTradeB WHERE ISIN IS NOT NULL AND left(TradeDate,6) > DATEADD(MM,-12,@Period)
) N
WHERE I.ISIN = N.ISIN
ORDER BY ISIN ) InstrumentName
,
(SELECT TOP 1 InstrumentType FROM
(
SELECT DISTINCT ISIN AS ISIN, InstrumentType FROM #tblTradeB WHERE ISIN IS NOT NULL AND left(TradeDate,6) > DATEADD(MM,-12,@Period)
)M
where I.ISIN = M.ISIN
ORDER BY ISIN) InstrumentType
FROM (
SELECT DISTINCT ISIN AS ISIN FROM #tblTradeB WHERE ISIN IS NOT NULL AND left(TradeDate,6) > DATEADD(MM,-12,@Period)
) I
)B INNER JOIN #tblInstrumentType IT on
b.InstrumentType = it.SourceInstrumentCode
WHERE IT.DataSourceName = 'SCD'
)F
I will just highlight some points it the code
SELECT TOP 1 SecurityName FROM (
SELECT DISTINCT ISIN AS ISIN, SecurityName AS SecurityName FROM hst.TDSD
WHERE ISIN IS NOT NULL AND left(TradeDate,6) > DATEADD(MM,-12,@Period)
This is here because depending on the source system an instrument can have many different security names due to how the user entered the data. I only need one of the names hence the select top 1
WHERE IT.DataSourceName = 'CS'
This refers to the datasource code used . CS is for tradeA and SCD is for tradeB. As the system grows there will many more trade tables.
I have included some sample data
CREATE TABLE #tblTradeA
(
TradeID varchar(12)
,InstrumentType varchar(20)
,SecurityName varchar(20)
,TradeDate varchar(10)
,ISIN varchar(12)
)
INSERT INTO #tblTradeA
SELECT '1220996322', 'GB', ' KINGDOM_NAME', ' 20110324','US545745AF36' UNION ALL
SELECT '1220996324', 'GB', ' KINGD_NAME', ' 20110325','US545745AF36' UNION ALL
SELECT '1220996325', 'CT', ' JUPITER_NAME', ' 20110325','US545745AF33' UNION ALL
SELECT '1220996326', 'CT', ' JUPT_NAME', ' 20110325','US545745AF33' UNION ALL
SELECT '1220996327', 'DR', ' MARS_NAME', ' 20110326','US545745AF39' UNION ALL
SELECT '1220996377', 'DR', ' MARRS_NAME', ' 20110324','US545745AF39' UNION ALL
SELECT '1220996329', 'CO', ' SATURN_NAME', ' 20110327','US545745AF31' UNION ALL
SELECT '1220996349', 'CO', ' SAT_NAME', ' 20110325','US545745AF31' UNION ALL
SELECT '1220996829', 'OP', ' MUREX_NAME', ' 20110325','US545745AF32' UNION ALL
SELECT '1220990322', 'OP', ' MREX_NAME', ' 20110321','US545745AF32'
CREATE TABLE #tblTradeB
(
TradeID varchar(12)
,InstrumentType varchar(20)
,SecurityName varchar(20)
,TradeDate varchar(10)
,ISIN varchar(12)
)
INSERT INTO #tblTradeB
SELECT '2220996321', 'COM', ' GILDER', ' 20110318','UB545745AF36' UNION ALL
SELECT '2220996322', 'COM', ' GILD', ' 20110316','UB545745AF36' UNION ALL
SELECT '2220996323', 'CTO', ' JAMOP', ' 20110319','UB545745AF37' UNION ALL
SELECT '2220996324', 'CTO', ' JAM_OP_NAME', ' 20110324','UB545745AF37' UNION ALL
SELECT '2220996325', 'JAM', ' NS_NAM', ' 20110325','UB545745AF35' UNION ALL
SELECT '2220996326', 'JAM', ' NS_NAME', ' 20110326','UB545745AF35' UNION ALL
SELECT '2220996327', 'GB', ' URANUS_NAME', ' 20110326','UB545745AF34' UNION ALL
SELECT '2220996328', 'GB', ' URA_NAME', ' 20110326','UB545745AF34' UNION ALL
SELECT '2220996329', 'MIC', ' FRANK_NAME', ' 20110326','UB545745AF33' UNION ALL
SELECT '2220996330', 'MIC', ' FRAN_NAME', ' 20110326','UB545745AF33'
--#tblTradeA
CREATE TABLE #tblInstrumentType
(
[SourceInstrumentCode] varchar(12)
,InstrumentType varchar(20)
,AssetClassCode varchar(1)
)
INSERT INTO #tblInstrumentType
SELECT 'CS', 'GB', 'F' UNION ALL
SELECT 'CS', 'CT', 'E' UNION ALL
SELECT 'CS', 'DR', 'D' UNION ALL
SELECT 'CS', 'OP', 'A' UNION ALL
SELECT 'CS', 'CO', 'A' UNION ALL
SELECT 'SCD', 'GOM', 'F' UNION ALL
SELECT 'SCD', 'CTO', 'E' UNION ALL
SELECT 'SCD', 'JAM', 'D' UNION ALL
SELECT 'SCD', 'GB', 'A' UNION ALL
SELECT 'SCD', 'MIC', 'A'
The Output should be
ISIN InstrumentName AssetCodeClass
--------------------------------------------------------------------------
US545745AF36 KINGDOM_NAME F
US545745AF33 JUPITER_NAME E
US545745AF39 MARS_NAME D
US545745AF31 SATURN_NAME A
US545745AF32 MUREX_NAME A
UB545745AF36 GILDER F
UB545745AF37 JAM_OP_NAME E
UB545745AF35 NS_NAME D
UB545745AF34 URANUS_NAME A
UB545745AF33 FRAN_NAME A
November 17, 2011 at 11:36 am
OK, this one took a little bit to comprehend. There were a lot of subquery nesting levels. I interpreted some of the code to fix the "missing" columns of DataSourceName to SourceInstrumentCode, based upon the create table scripts provided.
Is the TradeDate column an actual VARCHAR(10) as listed in the sample scripts or a DATE? There were DATETIME conversion errors in the stored procedure script provided: " LEFT(TradeDate,6) > DATEADD(MM,-12,@Period)". I converted it to something else, but kept the logic the same: LEFT(TradeDate,6) > CONVERT(CHAR(6), DATEADD(MM, -12, (CONVERT(DATE,@Period+'01'))))
The date conversion section should be changed. Take for example the scenario below:
DECLARE @Period CHAR(6) = '201003' -- YYYYMM of the trade
select DATEADD(MM,-12,@Period)
---- returns 2019-10-03 00:00:00.000
Here is my attempt of helping to reduce the code bloat of all the nested subqueries. I utilized the ROW_NUMBER() function and picked some columns for the order by. This will allow you to select the "TOP 1" without so many nested subqueries.
----prefilled variables for test
DECLARE @Period CHAR(6) = '201103' -- YYYYMM of the trade
,@Debug int = 0 -- For debugging
; WITH TradeA (ISIN, SecurityName, AssetClassCode, RowNbr) AS
(
SELECT ta.ISIN, ta.SecurityName, IT.AssetClassCode, RowNbr=ROW_NUMBER() OVER (PARTITION BY ta.ISIN ORDER BY ta.TradeDate, ta.SecurityName DESC)
FROM #tblTradeA ta
JOIN #tblInstrumentType IT
ON ta.InstrumentType = it.InstrumentType
AND IT.SourceInstrumentCode = 'CS'
WHERE ta.ISIN IS NOT NULL
AND LEFT(ta.TradeDate,6) > CONVERT(CHAR(6), DATEADD(MM, -12, (CONVERT(DATE,@Period+'01'))))
)
, TradeB (ISIN, SecurityName, AssetClassCode, RowNbr) AS
(
SELECT tb.ISIN, tb.SecurityName, IT.AssetClassCode, RowNbr=ROW_NUMBER() OVER (PARTITION BY tb.ISIN ORDER BY tb.TradeDate, tb.SecurityName DESC)
FROM #tblTradeB tb
JOIN #tblInstrumentType IT
ON tb.InstrumentType = it.InstrumentType
AND IT.SourceInstrumentCode = 'SCD'
WHERE tb.ISIN IS NOT NULL
AND LEFT(tb.TradeDate,6) > CONVERT(CHAR(6), DATEADD(MM, -12, (CONVERT(DATE,@Period+'01'))))
)
SELECT ISIN, SecurityName
FROM TradeA ta
WHERE ta.RowNbr=1
UNION
SELECT ISIN, SecurityName
FROM TradeB tb
WHERE tb.RowNbr=1
Let me know if this works for you. This was my best guess based on the DDL provided.
November 18, 2011 at 1:17 am
Hey John,
That works fine and is certainly a lot easier to read/decipher. The date is varchar form the original source but kept your conversion in to cover for those mistakes. When I ran it on a the actuall the timing was also faster than nested query approach.
cheers
November 18, 2011 at 4:03 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply