Need help for a query to be solved.

Hi, i am trying to figure out the right query to achieve a Total Targets, Total Sales summary table.

#Relitionships

- A `store` has many `sales`
- A `store` has many `targets`

- A `target` belongs to a `store`
- A `sale` belongs to a `store`

#stores table

| id | name    |
|----|---------|
| 1  | Store A |
| 2  | Store B |

#targets table

| id | store_id | apply_date | amount |
|----|----------|------------|--------|
| 1  | 1        | 2020/01/01 | 100    |
| 2  | 2        | 2020/01/01 | 150    |
| 3  | 1        | 2020/02/01 | 300    |
| 5  | 2        | 2020/01/01 | 400    |
| 6  | 1        | 2020/03/01 | 250    |

#sales table

| id | store_id | sale_date  | sub_total |
|----|----------|------------|-----------|
| 1  | 1        | 2020/01/25 | 1000      |
| 2  | 1        | 2020/01/26 | 2300      |
| 3  | 2        | 2020/01/02 | 120       |
| 4  | 1        | 2020/02/11 | 2340      |
| 5  | 2        | 2020/03/22 | 4022      |

I have a date filter applied to the query which filters the sales sale_date by range. What i am trying to do is sum targets amount multiple by days in date range. And sum sub_total of the sales within the date range. But targets amount might differer depending on the sale_date. So if a sale made within the selected ranges, i need to add 1x of the targets amount into sum of targets amount.

So as in the example data i provided, Store A has 3 sales and Store B has 1 sale within the date range. I don't have a problem with the sum of the total_sales but i have issue with the sum of targets amount. I need to sum targets amount within the date range (month) for each store as

• 2x 100 + 1x 200 = 500 for Store A
• 1x 150 = 150 for Store B

My Query is something like this now, which i know it's not the way to achieve. But i give as sample what i have in my hands now.

$query =Store::query();

$query->select(
   'stores.name',
   DB::raw('sum(targets.amount) as total_targets'),
   DB::raw('sum(sales.sub_total) as total_sales'),
)
->join('sales', function ($sale) {
   $sale->on('sales.store_id', 'stores.id');
})
->join('targets', function ($target) {
    $target->on('targets.store_id', 'stores.id');
})
->whereBetween('sales.sale_date','2020/01/01 00:00:00','2020/02/15 23:59:59')
->groupBy('stores.id', 'stores.name');

what output result i expect:

| name    | total_targets | total_sales |
|---------|---------------|-------------|
| Store A | 500           | 5760        |
| Store B | 150           | 120         |

Hope it is clear enough and i am looking forward for your help. Thanks.