So I have two tables that look like this:
Table_A
| Product | start_date | end_Date |
|---|---|---|
| apple | 07-23-2022 | 09-23-2022 |
| apple | 04-12-2022 | 06-24-2022 |
| orange | 01-01-2023 | 12-11-2023 |
Table_B
| Product | date_sold | quantity_sold |
|---|---|---|
| apple | 08-23-2022 | 5 |
| apple | 05-12-2022 | 10 |
| apple | 05-13-2022 | 12 |
| orange | 03-01-2023 | 24 |
| orange | 04-01-2024 | 45 |
I want to find the total sales for each product in Table_A between the start date and end date.
So by the end I have a table like this:
| Product | start_date | end_Date | total_sales |
|---|---|---|---|
| apple | 07-23-2022 | 09-23-2022 | 5 |
| apple | 04-12-2022 | 06-24-2022 | 22 |
| orange | 01-01-2023 | 12-11-2023 | 24 |
1
You’ll join these tables on the Product and whether the date_sold is in the date range in table_a or not, using the BETWEEN operator:
SELECT t1.Product, t1.start_date, t1.end_date, SUM(t2.quantity_sold) as total_sales
FROM table_a t1
INNER JOIN table_b t2
ON t1.Product = t2.Product
AND t2.date_sold BETWEEN t1.start_date and t1.end_date
GROUP BY t1.Product, t1.start_date, t1.end_date