Question 20926 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL: Join two tables by date range and group by year – Mysql

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
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    Try to use UNION and WHERE to fetch data from 2 SELECT:

        Select * from 
    (Select a as a1
    UNION
    Select b as b1)
    WHERE b1=a1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search