Better written query

  • 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

    (

    SELECTISIN

    ,(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

    (

    SELECTISIN

    ,(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

  • 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.

  • 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

  • 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