SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)

    Left join subquery using calcs

    I know this can be done in a WITH clause, but that isn't at my disposal:

    table1
    -----
    custId, someDate
    1 , 2011-04-28 19:40:47
    1 , 2011-07-07 13:27:57

    table2
    ------
    custId, anotherDate
    1 , 2012-07-23 12:53:24
    1 , 2012-10-16 14:33:37
    1 , 2012-11-16 14:33:37

    I need to create a start end range. I need to left join table2 onto table1 where min(anotherDate) > someDate for the same custId. Notice 2012-11-16 14:33:37 was excluded because it was not the min for someDate: 2011-07-07 13:27:57

    Result:
    custId, someDate, anotherDate
    1 , 2011-04-28 19:40:47 , 2012-07-23 12:53:24
    1 , 2011-07-07 13:27:57 , 2012-10-16 14:33:37

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,283
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    You could do something like:

    Code:
    SELECT T1.CustID
         , T1.SomeDate
         , T2.MinDate
      FROM Table1 T1
     INNER JOIN (SELECT CustID
              , MIN(AnotherDate) as MinDate
                   FROM Table2
                  GROUP BY CustID) T2 ON T1.CustID = T2.CustID
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    You could do something like:

    Code:
    SELECT T1.CustID
         , T1.SomeDate
         , T2.MinDate
      FROM Table1 T1
     INNER JOIN (SELECT CustID
              , MIN(AnotherDate) as MinDate
                   FROM Table2
                  GROUP BY CustID) T2 ON T1.CustID = T2.CustID
    This will select the min anotherDate per custID, which is incorrect. I need it to select the min anotherDate per custId that is > someDate

    Essentially think of table1 as service start dates, and table2 of service end dates. I need to create active service date ranges.

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Essentially this with the obvious min() being placed somewhere where it is allowed:

    left join table2 t2 on t1.custId = t2.custId and t2.anotherDate > min(t1.someDate)

    this join without the min() gives me ALMOST what I want, except there are dupes. It isn't returning one instance of t2's date. And throwing a simple min() on t2's date will make it return only one date per custId instead of a valid date range for all rows.

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,283
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    Ooops. Missed that criteria.

    Try this instead

    Code:
    SELECT T1.CustID
         , T1.SomeDate
         , MIN(T2.AnotherDate)
      FROM Table1 T1
      LEFT JOIN Table2 T2 ON T1.CustID = T2.CustID 
                         AND T2.AnotherDate >= T1.SomeDate
     GROUP BY T1.CustID
            , T1.SomeDate
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Unfortunately that produces the same undesirable results. Remove the min and group by and it's almost there, but it shows all possibilities rather than just the next occurrence. Throwing a min on the whole select will produce the same date for all columns

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,283
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    That doesn't make sense. I created test tables with your data. Based on your data, the results for the last query I gave you (though I changed it to inner join - though left join results match in this case) are:

    Code:
    CustID      SomeDate     MinDate
    1             4/8/2011      7/23/2011
    1             7/7/2011      7/23/2011
    which would be correct - it shows the minimum another date from table two on each some date. If you change the some date on the 2nd row of table 1 to 8/1/12, the results change to:
    Code:
    CustID      SomeDate     MinDate
    1             4/8/2011      7/23/2011
    1             8/1/2012      10/16/2012
    which would be correct - that's the minimum record on table 2 that's greater than the some date.

    What am I missing?
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  8. #8
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    That second query should do it... (needs an Order by, but w/e)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  9. #9
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    Your right guys, sorry, I was using a bad example locally when building it out

  10. #10
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    67 Post(s)
    Tagged
    2 Thread(s)
    I knew this. Just one of those days


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •