Filter returned records based on MAX/Last date found

  • Hi,

    My original query contains many table joins where I need to filter on the returned values. This example contains just 2 tables. Basically from the query below, I need to only return the Last/Greatest/Max Entry_Date for each group-set if found from Table_2 within the last 7 days.

    This is a MUCH simplified version, but your appreciated answers will no doubt assist me.

    NOTE: The date values are in mdy format. I reset these to dmy before I execute it.

    Table_1

    Name Last_LO_Date

    Neal 10/10/2011 12:00:00 AM

    Paul 10/5/2011 12:00:00 AM

    Table_2

    Name Entry_Date

    Neal 10/8/2011 12:00:00 AM

    Neal 10/9/2011 12:00:00 AM

    Neal 10/6/2011 12:00:00 AM

    Paul 10/4/2011 12:00:00 AM

    Paul 9/9/2011 12:00:00 AM

    My extract query is:

    SET DATEFORMAT DMY

    SELECT

    T1.[Name],

    CASE

    WHEN T1.Last_LO_Date = T2.Entry_Date --mpk.Activity_Date = (DATEADD(Day, -7, pt.VCTContractDate)

    THEN T2.Entry_Date

    WHEN DATEADD(Day, -1, T1.Last_LO_Date) = T2.Entry_Date

    THEN T2.Entry_Date

    WHEN DATEADD(Day, -2, T1.Last_LO_Date) = T2.Entry_Date

    THEN T2.Entry_Date

    WHEN DATEADD(Day, -3, T1.Last_LO_Date) = T2.Entry_Date

    THEN T2.Entry_Date

    WHEN DATEADD(Day, -4, T1.Last_LO_Date) = T2.Entry_Date

    THEN T2.Entry_Date

    WHEN DATEADD(Day, -5, T1.Last_LO_Date) = T2.Entry_Date

    THEN T2.Entry_Date

    WHEN DATEADD(Day, -6, T1.Last_LO_Date) = T2.Entry_Date

    THEN T2.Entry_Date

    WHEN DATEADD(Day, -7, T1.Last_LO_Date) = T2.Entry_Date

    THEN T2.Entry_Date

    END AS Last_Entry

    FROM dbo.Table_1 T1 left join dbo.Table_2 T2 ON

    T1.[Name] = T2.[Name]

    Output:

    Name Last_Entry

    Neal 2011-10-08 00:00:00

    Neal 2011-10-09 00:00:00

    Neal 2011-10-06 00:00:00

    Paul 2011-10-05 00:00:00

    Paul NULL

    In this example I only need to return 2 rows, which are:

    Neal 2011-10-09 00:00:00

    Paul 2011-10-05 00:00:00

    NOTE: There are millions of rows to join/search on in several of the tables

    Any ideas please?

    Thanks in advance,

  • SELECT

    t1.[Name]

    ,MAX(t2.Entry_Date) Last_Entry

    FROM

    Table_1 t1

    LEFT JOIN

    Table_2 t2 ON t1.[Name] = t2.[Name]

    GROUP BY

    t1.Name

    John

  • Thanks John.

    I couldn't see the wood-for-the-trees in the larger query.

    Regards,

  • You don't need to join on Table_1 for this particular query, but that could just be an artifact of the simplified data.

    In any case, you might want to group your data BEFORE joining to table_1 rather than after.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • That's worth considering. Maybe the query optimizer is smart enough to do that automatically; maybe it isn't. The only way you'll know is by comparing the two actual execution plans when you run the queries against your real data.

    John

Viewing 5 posts - 1 through 5 (of 5 total)

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