Trying to join tables using datetime fields

I have a query that returns a datetime field开发者_开发问答 and several other fields along with it. I want to add a field from another table that also has a datetime field. I cannot inner join these two tables since the datetime fields aren't related, so instead want to join the first row in the second table that occurs just after the datetime field in the first table. Here's a non-functional pseudo query of what I am trying to go for:

SELECT DISTINCT 
       TripID AS 'ID', 
       @CURRDATE = CurrDate, 
       @GROUPID = GroupID, 
       UserEmail AS 'User', 
       RouteID AS 'Route', 
       (SELECT TOP (1) PatternNum 
          FROM tblMOEHistory 
         WHERE (GroupID = @GROUPID) 
           AND (TimeStamp > @CURRDATE) 
      ORDER BY TimeStamp) AS 'Pattern' 
  FROM tblMobileTrips 
ORDER BY 'Time';

I'm not sure if there is a way to do this but I was hoping someone might be able to help me out.

Using CROSS APPLY, you can "parameterise" the call to tblMOEHistory (which I assume you were trying with @GROUPID and @CURRDATE per row)

Something like:

SELECT DISTINCT
  M.TripID AS 'ID', 
  M.CurrDate, 
  M.GroupID, 
  M.UserEmail AS 'User', 
  M.RouteID AS 'Route',
  MH.PatternNum  AS 'Pattern' 
FROM
  tblMobileTrips M
  CROSS APPLY
  (SELECT TOP (1) PatternNum
    FROM tblMOEHistory  MH
    WHERE MH.GroupID = M.GroupID AND MH.TimeStamp > M.CurrDate
    ORDER BY TimeStamp
  ) MH
ORDER BY 'Time';

You can indeed use a join... give this a shot:

SELECT DISTINCT a.TripID AS 'ID',  
     a.CurrDate,  
     a.GroupID,  
     a.UserEmail AS 'User',  
     a.RouteID AS Route',  
     b.PatternNum
from someTable a
join tblMOEHistory b on a.GroupID = b.GroupID
     and b.TimeStamp = (select MIN(TimeStamp) 
                        from tblMOEHistory 
                        where GroupID = a.GroupID 
                        and TimeStamp > a.CurrDate)