SQL: Join two tables by date range and group by year-CodePudding

I want to join two tables:

Table1:

Task	Hours	Client	Time
Task A	1	Client A	2023-01-01
Task A	2	Client A	2022-03-04
Task A	3	Client A	2023-01-01
Task A	4	Client A	2022-03-04
Task B	5	Client A	2023-01-01
Task B	6	Client A	2022-03-04
Task B	7	Client A	2023-01-01
Task B	8	Client A	2022-03-04

Table 2:

Task	Time Budget	Client	Start Range	End Range
Task A	50	Client A	2023-01-01	2023-12-31
Task A	60	Client A	2022-01-01	2022-12-31
Task B	80	Client A	2023-01-01	2023-12-31
Task B	70	Client A	2022-01-01	2022-12-31

I want to get such a table:

Task	Time Budget	Client	Start Range	End Range	Time spent
Task A	50	Client A	2023-01-01	2023-12-31	4
Task A	60	Client A	2022-01-01	2022-12-31	6
Task B	80	Client A	2023-01-01	2023-12-31	12
Task B	70	Client A	2022-01-01	2022-12-31	14

What I tried:

SELECT
    t2.task as task,
    t2.budget as budget,
        t1.client as client,
    t2.from_date as start_range,
    t2.to_date as end_range,
        sum(t1.hours) AS time_spent,
FROM `Table1` t1
LEFT JOIN 
    `Table2` t2 
    ON t1.task = t2.task
    AND t1.client = t2.client
    AND date(t1.time) BETWEEN t2.start_range and t2.end_range
Group by 
    task, client, start_range, end_range

However, this does not work. The best I can get is where it is joined, but for example the whole year 2022 is ignored.

Any help is so much appreciated!

With this query (and the suggested one) it leads to:

Task	Time Budget	Client	Start Range	End Range	Time spent
Task A	50	Client A	2023-01-01	2023-12-31	4
Task A	60	NULL	2022-01-01	2022-12-31	NULL
Task B	80	Client A	2023-01-01	2023-12-31	12
Task B	70	NULL	2022-01-01	2022-12-31	NULL

CodePudding user response：

The t2.budget is not included in the group by and you need to sum by the hour column.

If you want to include all of the table2 results, start with this table, then left join table1. Here is a post about table joins.

Using https://www.db-fiddle.com/

Schema (MySQL v5.7)

CREATE TABLE Table1 (
  `Task` VARCHAR(6),
  `Hours` INTEGER,
  `Client` VARCHAR(8),
  `Time` DATE
);

INSERT INTO Table1
  (`Task`, `Hours`, `Client`, `Time`)
VALUES
  ('Task A', '1', 'Client A', '2023-01-01'),
  ('Task A', '2', 'Client A', '2022-03-04'),
  ('Task A', '3', 'Client A', '2023-01-01'),
  ('Task A', '4', 'Client A', '2022-03-04'),
  ('Task B', '5', 'Client A', '2023-01-01'),
  ('Task B', '6', 'Client A', '2022-03-04'),
  ('Task B', '7', 'Client A', '2023-01-01'),
  ('Task B', '8', 'Client A', '2022-03-04');
  
  

CREATE TABLE Table2 (
  `Task` VARCHAR(6),
  `Time Budget` INTEGER,
  `Client` VARCHAR(8),
  `Start Range` DATE,
  `End Range` DATE
);

INSERT INTO Table2
  (`Task`, `Time Budget`, `Client`, `Start Range`, `End Range`)
VALUES
  ('Task A', '50', 'Client A', '2023-01-01', '2023-12-31'),
  ('Task A', '60', 'Client A', '2022-01-01', '2022-12-31'),
  ('Task B', '80', 'Client A', '2023-01-01', '2023-12-31'),
  ('Task B', '70', 'Client A', '2022-01-01', '2022-12-31');

Query #1

SELECT 
t2.`task`,
t2.`Time Budget`,
t2.client,
t2.`Start Range`,
t2.`End Range`,
SUM(t1.hours)

FROM Table2 AS t2
LEFT JOIN Table1 AS t1 
ON t2.Task=t1.task
AND t2.client=t1.client
AND t1.time between t2.`Start Range` AND t2.`End Range`
GROUP BY 1,2,3,4,5;

task	Time Budget	client	Start Range	End Range	SUM(t1.hours)
Task A	50	Client A	2023-01-01	2023-12-31	4
Task A	60	Client A	2022-01-01	2022-12-31	6
Task B	70	Client A	2022-01-01	2022-12-31	14
Task B	80	Client A	2023-01-01	2023-12-31	12

View on DB Fiddle

CodePudding user response：

Try ti use UNION and WHERE for get data from 2 selects:

    Select * from 
    (
    Select a as a1
    UNION
    Select b as b1
    )
    WHERE b1=a1