I have two tables: one with Salespeople monthly targets and the other with deals they closed.
I need to consolidate the amount closed for each salesperson and month, and join with the first table. Find the expected result below.
I've tried the query below but it won't join the months if no deal was closed.
SELECT
S."Salesperson",
month(S."Date"),
year(S."Date"),
S."Date",
S."Target",
sum(D."Amount") AS 'Amount',
FROM "SalesQuotes" AS S
LEFT JOIN "Deals" AS D ON D."Salesperson" = S."Salesperson"
AND (year(D."Date") = year(S."Date")
AND month(D."Date") = month(S."Date"))
GROUP BY S."Salesperson",
month(S."Date"),
year(S."Date"),
S."Target",
S."Date"
MREs:
First table
CREATE TABLE SalesQuotes(
Date VARCHAR(6) NOT NULL
,Salesperson VARCHAR(6) NOT NULL
,Target INTEGER NOT NULL
);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('jan/21','John',12);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('feb/21','John',14);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('mar/21','John',16);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('jan/21','Mary',20);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('feb/21','Mary',25);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('mar/21','Mary',30);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('jan/21','George',50);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('feb/21','George',60);
INSERT INTO SalesQuotes(Date,Salesperson,Target) VALUES ('mar/21','George',70);
Second table
CREATE TABLE Deals(
Date VARCHAR(6) NOT NULL
,Salesperson VARCHAR(6) NOT NULL
,Amount INTEGER NOT NULL
,Deal VARCHAR(1) NOT NULL
);
INSERT INTO Deals(Date,Salesperson,Amount,Deal) VALUES ('jan/21','John',5,'A');
INSERT INTO Deals(Date,Salesperson,Amount,Deal) VALUES ('jan/21','John',6,'B');
INSERT INTO Deals(Date,Salesperson,Amount,Deal) VALUES ('feb/21','Mary',10,'C');
INSERT INTO Deals(Date,Salesperson,Amount,Deal) VALUES ('feb/21','George',15,'D');
INSERT INTO Deals(Date,Salesperson,Amount,Deal) VALUES ('mar/21','George',40,'E');


