Max Dates and Joins

  • Hi Everyone,
         I have a query like this:

    SELECT ORDERS.GFNo,
           O
    rderCategories.Description,
          
    ORDERS.OpenDate,TO
           TO
    Main.Description AS TOMain,
           AG
    ENT.Name,
           O
    RDERS.OtherFileNum,
           OR
    DERS.CommitDate,
          
    ORDERS.FTReceived,

           --t1.max_date,
          
    --t1.notes,
          
    --d.Department,
          
    --d.ID,

    CASE
          
    WHEN AGENT.Name LIKE 'KML%' Then Dateadd(dd, 4, ORDERS.OpenDate)
          
    WHEN AGENT.Name LIKE 'Service%' Then Dateadd(dd, 2, ORDERS.OpenDate)
          
    ELSE Dateadd(dd, 5, ORDERS.OpenDate)
          
    END AS DeadlineDate

    FROM ORDERS
        
    INNER JOIN TOMain ON TOMain.TONum = ORDERS.TONum
        
    INNER JOIN OrderCategories ON ORDERS.OrderCategory = OrderCategories.OrderCategory
        
    INNER JOIN AGENT ON ORDERS.Referral = AGENT.AgentNum

         -- INNER JOIN
        
    --(select GFNo, CONVERT(nvarchar(max), Notes) AS Notes, Max(OrdNotesID) AS Ord, Max(Ndate) as max_date
        
    -- FROM ORDNOTES
        
    -- GROUP BY GFNO, CONVERT(nvarchar(max), Notes)) as t1 on ORDERS.GFNO = t1.GFNo and t1.max_date = t1.max_date

         -- INNER JOIN
         -- (SELECT GFNo, Department, MAX(IDNum) as ID
         --FROM FTLog
         --GROUP BY GFNo,Department) AS d ON ORDERS.GFNO = d.GFNo and d.ID = d.id

    --WHERE d.Department IN ('Abstracting', 'Abstract OS NJ' ,'ABSTRACT OS PA', 'Delivery', 'Examination', 'Final Abstract', 'Origination', 'Typing')

    ORDER BY ORDERS.GFNo

    Now, Commenting out the INNER JOINs in the FROM clause gives me the records I want.  Theres only 1 record  for each entry.  Now here's the problem....
    When I add the INNER JOINS to get a MAX value, I start to get multiple records for each GFNO(which is the primary key).  The other tables is a 1-many, so there could be more than one entry for the GFNo.  I only want to bring back the most recent record of each GFNo from the other tables, but it looks like i am bringing back all of them.

    Can someone look at my code and give me some insight as to why I am getting all the records and not just the most recent?  It has to be in the inner joins somewhere but I can't out my finger on it.

    thanks!

  • Please post the DDL (CREATE TABLE statement) for the table(s) involved, sample data (INSERT INTO statements) for each table that is representative of your problem domain (and should exhibit the behavior you describe), and the expected results based on the sample data you provide.  The sample data should not be more than 20 rows of data and should not be production data.

    Test all the scripts you create in an empty sandbox database to be sure they run with no problems.

    This information will allow us to setup our own sandbox and help you with your problem.  Also, you will get tested code in return.

  • Quick suggestion - try APPLY with TOP:

    FROM ORDERS

    INNER JOIN TOMain ON TOMain.TONum = ORDERS.TONum

    INNER JOIN OrderCategories ON ORDERS.OrderCategory = OrderCategories.OrderCategory

    INNER JOIN AGENT ON ORDERS.Referral = AGENT.AgentNum

    CROSS APPLY (

    SELECT TOP(1)

    CONVERT(nvarchar(max), Notes) AS Notes,

    OrdNotesID AS Ord,

    Ndate AS max_date

    FROM ORDNOTES n

    WHERE n.GFNo = ORDERS.GFNO

    ORDER BY OrdNotesID DESC

    ) t1

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work - Friday, August 4, 2017 9:25 AM

    Quick suggestion - try APPLY with TOP:

    FROM ORDERS

    INNER JOIN TOMain ON TOMain.TONum = ORDERS.TONum

    INNER JOIN OrderCategories ON ORDERS.OrderCategory = OrderCategories.OrderCategory

    INNER JOIN AGENT ON ORDERS.Referral = AGENT.AgentNum

    CROSS APPLY (

    SELECT TOP(1)

    CONVERT(nvarchar(max), Notes) AS Notes,

    OrdNotesID AS Ord,

    Ndate AS max_date

    FROM ORDNOTES n

    WHERE n.GFNo = ORDERS.GFNO

    ORDER BY OrdNotesID DESC

    ) t1

    The cross applys work perfectly!  thank you!

  • There are two main options for this, and which one performs better depends on the typical number of records on the many side of the relationship.  The CROSS APPLY works best when that number is higher, but for lower numbers, you might be better off using a CTE with a ROW_NUMBER.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

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

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