Post reply

Filtering by Max date in a joined Query

  • July 31, 2018 at 4:02 pm

    #374022

    I am joining two tables and only want to display rows that are from the most recent data input. "MarkDate" represents the date the data was inputted, so I only want to display the max MarkDate. How would I do this with the following code    (the bold/underlined code is what I am trying to fix to only show rows with the max date):

    select t2.LongName, t2.tickercode, t1.observationvalue, t1.observationdate, t1.inventoryid, t1.markdate
    from DataWarehouse.CurveMartV2.GTSICEcurve as t1
    left join DataWarehouse.CurveMartV2.Inventory as t2 on t1.inventoryid = t2.inventoryid
    where
    t2.tickercode in ('MCO', 'ONE', 'CON', 'SON', 'STA', 'TRZ', 'STX', 'DMR', 'TEB', 'TWB', 'TFL', 'TSB', 'TZZ', 'NTO', 'WAH', 'TBP', 'DKR', 'TMT', 'TZS', 'h')
    AND t1.observationdate >= '2018-08-01'
    and t1.markdate = MAX(t1.markdate)
    and t1.observationyear <= 2025
    order by t2.longname

  • July 31, 2018 at 4:15 pm

    #1999801

    jakegordon1997 - Tuesday, July 31, 2018 4:02 PM

    I am joining two tables and only want to display rows that are from the most recent data input. "MarkDate" represents the date the data was inputted, so I only want to display the max MarkDate. How would I do this with the following code    (the bold/underlined code is what I am trying to fix to only show rows with the max date):

    select t2.LongName, t2.tickercode, t1.observationvalue, t1.observationdate, t1.inventoryid, t1.markdate
    from DataWarehouse.CurveMartV2.GTSICEcurve as t1
    left join DataWarehouse.CurveMartV2.Inventory as t2 on t1.inventoryid = t2.inventoryid
    where
    t2.tickercode in ('MCO', 'ONE', 'CON', 'SON', 'STA', 'TRZ', 'STX', 'DMR', 'TEB', 'TWB', 'TFL', 'TSB', 'TZZ', 'NTO', 'WAH', 'TBP', 'DKR', 'TMT', 'TZS', 'h')
    AND t1.observationdate >= '2018-08-01'
    and t1.markdate = MAX(t1.markdate)
    and t1.observationyear <= 2025
    order by t2.longname

    I think you want something like this:
    select t2.LongName, t2.tickercode, t1.observationvalue, t1.observationdate, t1.inventoryid, t1.markdate
    from DataWarehouse.CurveMartV2.GTSICEcurve as t1
    left join DataWarehouse.CurveMartV2.Inventory as t2 on t1.inventoryid = t2.inventoryid
    cross apply(SELECT MAX(t3.markdate) markdate FROM GTSICEcurve as t3 /*WHERE t3.inventoryid = t1.inventoryid*/) t3
    where t2.tickercode in ('MCO', 'ONE', 'CON', 'SON', 'STA', 'TRZ', 'STX', 'DMR', 'TEB', 'TWB', 'TFL', 'TSB', 'TZZ', 'NTO', 'WAH', 'TBP', 'DKR', 'TMT', 'TZS', 'h')
    AND t1.observationdate >= '2018-08-01'
    and t1.markdate = t3.markdate
    and t1.observationyear <= 2025
    order by t2.longname

  • July 31, 2018 at 4:40 pm

    #1999805

    Wow thanks that's perfect!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply