Join on a date column?

Question

I'm not sure if I'm thinking about this in the right way, but I want to join on 2 date columns (which are months really), here is my query:

with EmployeeGoals as (
    select Employees.id,
        convert(date, dateadd(m, datediff(m, 0, EmployeeSalesGoals.goalDate), 0)) as 'Goal Date',
        EmployeeSalesGoals.amount as 'Goal Amount'
    from EmployeeSalesGoals
    join Employees on EmployeeSalesGoals.employeeId = Employees.id
), EmployeeRevenue as (
    select Employees.id,
        convert(date, dateadd(m, datediff(m, 0, Invoices.date), 0)) as 'Date',
        sum(cast(Invoices.amount as float)) 'Revenue'
    from Invoices
    join Employees on j.ownerID = Employee.id
    group by cast(format(Invoices.date,'MM') as int),
        convert(date, dateadd(m, datediff(m, 0, Invoices.date), 0)),
        Employees.id)
select *
from EmployeeGoals
join EmployeeRevenue on EmployeeGoals.employeeId = EmployeeRevenue.employeeId 
  and Invoices.date = EmployeeGoals.goalDate

The problem is that this doesn't return any results, but if I remove the end Invoices.date = EmployeeGoals.goalDate I do get results, but the dates aren't matched, how can I join the date columns?

employee.id	Goal Date	Date	Goal Amount	Revenue
23	2023-01-01	2023-07-01	20000	19000
23	2023-02-01	2023-03-01	20000	22000
23	2023-03-01	2023-04-01	20000	21000
23	2023-04-01	2023-05-01	20000	30000
23	2023-05-01	2023-02-01	20000	12000
23	2023-06-01	2023-01-01	20000	20000
23	2023-07-01	2023-06-01	20000	19000

EDIT: Here is a Fiddle with this mostly in the right state, I appreciate any guidance: https://dbfiddle.uk/SqCXbsVI

(for some reason the CTE would not execute, and I'm scratching my head about why, but this is the bulk of the issue, so my question is then how do I join both the employee ID and the dates (by month)?

Answer 1

When I use your fiddle, and combine it with the CTE above, it works (after changing some column names). Or is this not what you would like to achieve?

CREATE TABLE Employees
(
   id INT,
   name VARCHAR(40),
);

CREATE TABLE EmployeeSalesGoals
(
   id INT,
   employeeId INT,
   amount INT,
   goalDate DATE,
);

CREATE TABLE Invoices
(
   id INT,
   employeeId INT,
   amount INT,
   invoiceDate DATE,
);

--
INSERT INTO Employees
VALUES
(
   1,
   'JP'
);
INSERT INTO Employees
VALUES
(
   2,
   'Jim'
);

INSERT INTO EmployeeSalesGoals
VALUES
(
   1,
   1,
   20000,
   '2023-01-01'
);
INSERT INTO EmployeeSalesGoals
VALUES
(
   2,
   2,
   20000,
   '2023-01-01'
);
INSERT INTO EmployeeSalesGoals
VALUES
(
   3,
   1,
   20000,
   '2023-02-01'
);
INSERT INTO EmployeeSalesGoals
VALUES
(
   4,
   2,
   20000,
   '2023-02-01'
);

INSERT INTO Invoices
VALUES
(
   5,
   1,
   2000,
   '2023-01-11'
);
INSERT INTO Invoices
VALUES
(
   6,
   1,
   10000,
   '2023-01-15'
);
INSERT INTO Invoices
VALUES
(
   7,
   1,
   6000,
   '2023-01-20'
);
INSERT INTO Invoices
VALUES
(
   8,
   2,
   7000,
   '2023-01-15'
);
INSERT INTO Invoices
VALUES
(
   9,
   2,
   8000,
   '2023-02-02'
);
INSERT INTO Invoices
VALUES
(
   20,
   2,
   4000,
   '2023-02-06'
);

-- Invoices
SELECT
         Employees.id,
         CONVERT(DATE, DATEADD(m, DATEDIFF(m, 0, Invoices.invoiceDate), 0)) AS 'Date',
         SUM(CAST(Invoices.amount AS FLOAT)) 'Revenue'
FROM     Invoices
         JOIN Employees ON Invoices.employeeId = Employees.id
GROUP BY CAST(FORMAT(Invoices.invoiceDate, 'MM') AS INT),
         CONVERT(DATE, DATEADD(m, DATEDIFF(m, 0, Invoices.invoiceDate), 0)),
         Employees.id;

-- Goals
SELECT
     Employees.id,
     CONVERT(DATE, DATEADD(m, DATEDIFF(m, 0, EmployeeSalesGoals.goalDate), 0)) AS 'Goal Date',
     EmployeeSalesGoals.amount AS 'Goal Amount'
FROM EmployeeSalesGoals
     JOIN Employees ON EmployeeSalesGoals.employeeId = Employees.id;

WITH EmployeeGoals
AS (SELECT
         Employees.id AS employeeId,
         CONVERT(
                   DATE,
                   DATEADD(m, DATEDIFF(m, 0, EmployeeSalesGoals.goalDate), 0)
                ) AS 'Goal Date',
         EmployeeSalesGoals.amount AS 'Goal Amount'
    FROM EmployeeSalesGoals
         JOIN Employees ON EmployeeSalesGoals.employeeId = Employees.id),
     EmployeeRevenue
AS (SELECT
             Employees.id AS employeeId,
             CONVERT(DATE, DATEADD(m, DATEDIFF(m, 0, Invoices.invoiceDate), 0)) AS 'Date',
             SUM(CAST(Invoices.amount AS FLOAT)) 'Revenue'
    FROM     Invoices
             JOIN Employees ON Invoices.employeeId = Employees.id
    GROUP BY CAST(FORMAT(Invoices.invoiceDate, 'MM') AS INT),
             CONVERT(
                       DATE,
                       DATEADD(m, DATEDIFF(m, 0, Invoices.invoiceDate), 0)
                    ),
             Employees.id)
SELECT *
FROM   EmployeeGoals
       JOIN EmployeeRevenue ON EmployeeGoals.employeeId = EmployeeRevenue.employeeId
                               AND EmployeeRevenue.Date = EmployeeGoals.[Goal Date];

Will return: