Join table on a date between two dates in the target table

I have two tables

tbl_TimeEntries 
   EmployeeID int,
   StartDateTime datetime,
   EndDateTime datetime

tbl_Crew_Employees
   CrewID,
   EmployeeID,
   StartDate,
   EndDate

I also have a query that produces the number of hours worked per employee per day, but I also want to include the crew the employee was on for that day.

  • Content download from within a Windows client - Best Practices
  • Design principles for designing database architecture of financial transaction system?
  • Anyway to decrypt an encrypted sql server stored procedure?
  • Finding strings with duplicate letters inside
  • How do I join the first row of a subquery?
  • Create temp table with column names from another tables column data
  • SELECT  tbl_TimeEntries.EmployeeID, 
            SUM(DATEDIFF(SECOND, StartDateTime, EndDateTime) / 60.0
                         / 60.0) as Hours,
            CAST(StartDateTime AS date) as WorkDate
    FROM    tbl_TimeEntries 
    GROUP BY tbl_TimeEntries.EmployeeID, CAST(StartDateTime AS date)
    ORDER BY CAST(StartDateTime AS date)
    

    I’m not sure how to include the CrewID in this query because the tbl_Crew_Employees uses a StartDate and EndDate (meaning the employee was on this crew from StartDate to EndDate). I would either need to expand the StartDate/EndDate range or use some sort of SQL magic of which I am unaware.

    Here is a sample of the data from the tbl_Crew_Employees, tbl_TimeEntries and the current query with the desired column data added. EmployeeID 88 is represented on two different crews in the sample.

    CrewID  EmployeeID  StartDate   EndDate
    
    13      11          2013-03-30  2013-05-12
    12      88          2013-01-02  2013-04-18
    12      66          2013-01-02  2013-06-30
    13      88          2013-04-19  2013-04-21
    11      111         2013-01-02  2013-04-28
    
    EmployeeID  StartDateTime       EndDateTime
    11          2013-04-18 08:00    2013-04-18 12:00
    11          2013-04-18 12:30    2013-04-18 18:30
    111         2013-04-18 10:00    2013-04-18 12:00
    111         2013-04-18 12:30    2013-04-18 18:30
    88          2013-04-18 11:00    2013-04-18 12:00
    88          2013-04-18 12:30    2013-04-18 19:30
    66          2013-04-18 10:00    2013-04-18 12:00
    66          2013-04-18 12:30    2013-04-18 18:30
    11          2013-04-20 08:00    2013-04-20 12:00
    11          2013-04-20 12:30    2013-04-20 18:00
    111         2013-04-20 10:00    2013-04-20 12:00
    111         2013-04-20 12:30    2013-04-20 18:30
    88          2013-04-20 11:00    2013-04-20 12:00
    88          2013-04-20 12:30    2013-04-20 19:30
    66          2013-04-20 10:00    2013-04-20 12:00
    66          2013-04-20 12:30    2013-04-20 17:00
    
    EmployeeID  Hours   WorkDate        CrewID(desired)
    11          10.00   2013-04-18      13
    88          8.00    2013-04-18      12
    66          8.00    2013-04-18      12
    111         8.00    2013-04-18      11
    11          7.50    2013-04-20      13
    88          8.00    2013-04-20      13
    66          6.50    2013-04-20      12
    111         8.00    2013-04-20      11
    

  • Group by Week of year (Week number) in Linq to SQL
  • Same query, same DB, different execution plans & dramatically different times to execute
  • Regarding delete a record
  • SQL table partition on same criteria as clustered index
  • The right connection string for Remote SQL server for C#
  • How to connect PHP with SQLServer
  • 2 Solutions collect form web for “Join table on a date between two dates in the target table”

    Try this:

    SELECT 
        tbl_TimeEntries.employeeid
        ,SUM(DATEDIFF(SECOND, StartDateTime, EndDateTime) / 60.0 / 60.0) AS HOURS
        ,CAST(StartDateTime AS DATE) AS WorkDate
        ,tbl_Crew_Employees.crewid
    FROM tbl_TimeEntries 
    INNER JOIN tbl_Crew_Employees ON tbl_timeentries.employeeid = tbl_Crew_Employees.employeeid 
        AND startdatetime >= startdate 
        AND enddatetime <= enddate
    GROUP BY tbl_TimeEntries.employeeid
            ,tbl_Crew_Employees.crewid
            ,CAST(tbl_TimeEntries.StartDateTime AS DATE)
    ORDER BY WorkDate
    

    Should be a simple join.

    declare @tbl_Crew_Employees table(CrewID int, EmployeeID int, StartDate date, EndDate date)
    insert into @tbl_Crew_Employees
    values
    (13,11,'2013-03-30','2013-05-12'),
    (12,88,'2013-01-02','2013-04-18'),
    (12,66,'2013-01-02','2013-06-30'),
    (13,88,'2013-04-19','2013-04-21'),
    (11,111,'2013-01-02','2013-04-28')
    
    declare @tbl_TimeEntries table (EmployeeID int, StartDateTime datetime, EndDateTime datetime)
    insert into @tbl_TimeEntries
    values
    (11,'2013-04-18 08:00','2013-04-18 12:00'),
    (11,'2013-04-18 12:30','2013-04-18 18:30'),
    (111,'2013-04-18 10:00','2013-04-18 12:00'),
    (111,'2013-04-18 12:30','2013-04-18 18:30'),
    (88,'2013-04-18 11:00','2013-04-18 12:00'),
    (88,'2013-04-18 12:30','2013-04-18 19:30'),
    (66,'2013-04-18 10:00','2013-04-18 12:00'),
    (66,'2013-04-18 12:30','2013-04-18 18:30'),
    (11,'2013-04-20 08:00','2013-04-20 12:00'),
    (11,'2013-04-20 12:30','2013-04-20 18:00'),
    (111,'2013-04-20 10:00','2013-04-20 12:00'),
    (111,'2013-04-20 12:30','2013-04-20 18:30'),
    (88,'2013-04-20 11:00','2013-04-20 12:00'),
    (88,'2013-04-20 12:30','2013-04-20 19:30'),
    (66,'2013-04-20 10:00','2013-04-20 12:00'),
    (66,'2013-04-20 12:30','2013-04-20 17:00')
    
    SELECT  
        t.EmployeeID, 
        c.CrewID,
            SUM(DATEDIFF(SECOND, t.StartDateTime, t.EndDateTime) / 60.0
                         / 60.0) ,
            CAST(t.StartDateTime AS date)
    FROM    @tbl_TimeEntries t
    INNER JOIN 
        @tbl_Crew_Employees c on 
        c.EmployeeID = t.EmployeeID
        and c.StartDate <= cast(t.StartDateTime as date)
        and c.EndDate >= cast(t.EndDateTime as date)
    GROUP BY t.EmployeeID, CAST(t.StartDateTime AS date), c.CrewID
    ORDER BY CAST(t.StartDateTime AS date)
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.