Join SQL without Unique Identifier

I have two tables:

Table A:

EmployeeID	DailyHour	Date
ID001	2	2021-02-01
ID001	4	2021-02-03
ID001	4	2021-02-04
ID001	4	2021-02-10
ID001	8	2021-02-15
ID002	8	2021-02-20

Table B:

EmployeeID	Week	StandardHours
ID001	2021-02-05	40
ID001	2021-02-12	40
ID001	2021-02-19	40
ID002	2021-02-12	40

How can I get the sum of DailyHour, StandardHours per EmployeeID (Date within range of Week)

Expected Output:
For EmployeeID = ID001 and Date Between Feb 1 2021 and Feb 12 2021

EmployeeID	DailyHours	StandardHours
ID001	14	80

Thank you in advance.

Answer

You’ll need to aggregate first, and then JOIN the data, based on the Employee’s ID:

WITH Daily AS(
    SELECT EmployeeID,
           SUM(DailyHour) AS DailyHours
    FROM dbo.TableA
    WHERE [Date] >= '20200201'
      AND [Date] <= '20200212'
    GROUP BY EmployeeID),
Standard AS(
    SELECT EmployeeID,
           SUM(StandardHours) AS StandardHours
    FROM dbo.TableB
    WHERE [Week] >= '20200201'
      AND [Week] <= '20200212'
    GROUP BY EmployeeID)
SELECT D.EmployeeID,
       D.DailyHours,
       S.StandardHours
FROM Daily D
     JOIN Standard S ON D.EmployeeID = S.EmployeeID;

If an Employee might not have Standard or Daily hours, you’ll need to use a LEFT JOIN or FULL OUTER JOIN, along with ISNULL (in the SELECT).