Post reply

Join tables with criteria

  • January 16, 2017 at 3:55 pm

    #401402

    Hello all,

    I need to find all OrderIDs (Needles) in Orders 2 that come within 3 days (and only the earliest if there are more than 1) of the TransactionDate of Orders1 (Tubes) linked by CustomerID. For example, CustomerID 111 has one Needle Order (1/03/2017) within three days of a Tube order (01/03/2017)...I assume I'd have to look first link the CustomerID then Transaction Date, but running into a mental block. I've included a spreadsheet of examples of two tables and the desired output. Any help is appreciated. Thanks. 

    Orders1
    Select O.OrderID, O.CustomerID, O.CustomerVisitID, O.ItemID, O.ItemDesc, O. TransactionDate
    From Orders 0
    Where O.Transaction Date >='2017-01-01'
        and O.ItemID = 1
    Order by O.CustomerID, O.CustomerVisitID, O.TransactionDate

    Orders2
    Select O.OrderID, O.CustomerID, O.CustomerVisitID, O.ItemID, O.ItemDesc, O. TransactionDate
    From Orders 0
    Where O.Transaction Date >='2017-01-01'
        and O.ItemID = 2
    Order by O.CustomerID, O.CustomerVisitID, O.TransactionDate

  • January 16, 2017 at 10:14 pm

    #1923099

    Please read this article to learn how to post so that people can help you more easily.

    Forum Etiquette: How to post data/code on a forum to get the best help

    If you post the CREATE TABLE and INSERT scripts, you stand a much better chance of getting tested and working code....

  • January 17, 2017 at 3:05 am

    #1923139

    With no DDL and sample data to test, this is what I could come up with.

    SELECT O.OrderID, O.CustomerID, O.CustomerVisitID, O.ItemID, O.ItemDesc, O.TransactionDate,
       F.OrderID AS NOrderId, F.CustomerID AS NCustomerID, F.CustomerVisitID AS NCustomerVisitID,
       F.ItemID AS NItemID, F.ItemDesc AS NItemDesc, F.TransactionDate AS NTransactionDate
    FROM Orders AS O
    CROSS APPLY (
         SELECT TOP 1 *
         FROM Orders AS N
         WHERE N.TransactionDate >='2017-01-01'
         AND  N.ItemID = 2
         AND  DATEDIFF( DAY, O.TransactionDate, N.TransactionDate ) <= 3
         ORDER BY N.TransactionDate DESC ) AS F
    WHERE O.TransactionDate >='2017-01-01'
    AND  O.ItemID = 1
    ORDER BY O.CustomerID, O.CustomerVisitID, O.TransactionDate;

    Please post additional details as mentioned in the previous post in case you dont get the desired output using the above query.

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • January 17, 2017 at 7:48 am

    #1923172

    Kingston Dhasian - Tuesday, January 17, 2017 3:05 AM

    With no DDL and sample data to test, this is what I could come up with.

    SELECT O.OrderID, O.CustomerID, O.CustomerVisitID, O.ItemID, O.ItemDesc, O.TransactionDate,
       F.OrderID AS NOrderId, F.CustomerID AS NCustomerID, F.CustomerVisitID AS NCustomerVisitID,
       F.ItemID AS NItemID, F.ItemDesc AS NItemDesc, F.TransactionDate AS NTransactionDate
    FROM Orders AS O
    CROSS APPLY (
         SELECT TOP 1 *
         FROM Orders AS N
         WHERE N.TransactionDate >='2017-01-01'
         AND  N.ItemID = 2
         AND  DATEDIFF( DAY, O.TransactionDate, N.TransactionDate ) <= 3
         ORDER BY N.TransactionDate DESC ) AS F
    WHERE O.TransactionDate >='2017-01-01'
    AND  O.ItemID = 1
    ORDER BY O.CustomerID, O.CustomerVisitID, O.TransactionDate;

    Please post additional details as mentioned in the previous post in case you dont get the desired output using the above query.

    Shouldn't that be OUTER APPLY as there may not be any needle orders within range

    Far away is close at hand in the images of elsewhere.
    Anon.

  • January 17, 2017 at 8:53 pm

    #1923320

    David Burrows - Tuesday, January 17, 2017 7:48 AM

    Kingston Dhasian - Tuesday, January 17, 2017 3:05 AM

    With no DDL and sample data to test, this is what I could come up with.

    SELECT O.OrderID, O.CustomerID, O.CustomerVisitID, O.ItemID, O.ItemDesc, O.TransactionDate,
       F.OrderID AS NOrderId, F.CustomerID AS NCustomerID, F.CustomerVisitID AS NCustomerVisitID,
       F.ItemID AS NItemID, F.ItemDesc AS NItemDesc, F.TransactionDate AS NTransactionDate
    FROM Orders AS O
    CROSS APPLY (
         SELECT TOP 1 *
         FROM Orders AS N
         WHERE N.TransactionDate >='2017-01-01'
         AND  N.ItemID = 2
         AND  DATEDIFF( DAY, O.TransactionDate, N.TransactionDate ) <= 3
         ORDER BY N.TransactionDate DESC ) AS F
    WHERE O.TransactionDate >='2017-01-01'
    AND  O.ItemID = 1
    ORDER BY O.CustomerID, O.CustomerVisitID, O.TransactionDate;

    Please post additional details as mentioned in the previous post in case you dont get the desired output using the above query.

    Shouldn't that be OUTER APPLY as there may not be any needle orders within range

    Based on the OP's description, I wasn't sure if the OP needed such orders to be displayed.
    But, as you said OUTER APPLY would be safer and would cover all scenarios.

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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