Left join subquery using calcs

Databases
1

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

You could do something like:


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
3

This will select the min anotherDate per custID, which is incorrect. I need it to select the min anotherDate per custId [B]that is > someDate

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

4

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

Ooops. Missed that criteria.

Try this instead


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
6

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

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:


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:


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?

8

That second query should do it… (needs an Order by, but w/e)

9

Your right guys, sorry, I was using a bad example locally when building it out

10

I knew this. Just one of those days :slight_smile: