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.

  • Cannot connect to database (free host somee.com)
  • How to rename database and files with entity framework
  • How to concatenate two strings in SQL Server 2005
  • Sql Server Management Studio Object Explorer dissapeared - missing - vanished
  • SQL Join and concatenate rows
  • Using sp_HElpText to view stored procedure
  • 2 Solutions collect form web for “Trying to join tables using datetime fields”

    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)
    

    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';
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.