title: Update 3rd table based on date range from another table
body: <p>I have two tables in MySQL. The first table 'table1' records daily data for all the days in a month for specific items. Like this -</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>Item</th>
  <th>Date</th>
  <th>num_of_items</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Bag</td>
  <td>2021-12-01</td>
  <td>2</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Bag</td>
  <td>2021-12-02</td>
  <td>3</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Bag</td>
  <td>2021-12-03</td>
  <td>4</td>
  </tr>
  <tr>
  <td>4</td>
  <td>Bag</td>
  <td>2021-12-04</td>
  <td>2</td>
  </tr>
  <tr>
  <td>5</td>
  <td>Bag</td>
  <td>2021-12-05</td>
  <td>7</td>
  </tr>
  <tr>
  <td>6</td>
  <td>Pencil</td>
  <td>2021-12-01</td>
  <td>2</td>
  </tr>
  <tr>
  <td>7</td>
  <td>Pencil</td>
  <td>2021-12-02</td>
  <td>4</td>
  </tr>
  <tr>
  <td>8</td>
  <td>Pencil</td>
  <td>2021-12-03</td>
  <td>1</td>
  </tr>
  <tr>
  <td>9</td>
  <td>Pencil</td>
  <td>2021-12-04</td>
  <td>5</td>
  </tr>
  <tr>
  <td>10</td>
  <td>Pencil</td>
  <td>2021-12-05</td>
  <td>2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>This goes up to Day 31 if needed for any number of items in the month.</p>
  <p>The second table 'table2' contains the rate for the items as it changes during the month and is not constant. It looks like this -</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>Month</th>
  <th>Year</th>
  <th>Item</th>
  <th>Entry Date</th>
  <th>Final Date</th>
  <th>Rate</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>December</td>
  <td>2021</td>
  <td>Bag</td>
  <td>2021-12-01</td>
  <td>2021-12-02</td>
  <td>10</td>
  </tr>
  <tr>
  <td>2</td>
  <td>December</td>
  <td>2021</td>
  <td>Bag</td>
  <td>2021-12-03</td>
  <td>2021-12-05</td>
  <td>12</td>
  </tr>
  <tr>
  <td>3</td>
  <td>December</td>
  <td>2021</td>
  <td>Pencil</td>
  <td>2021-12-01</td>
  <td>2021-12-03</td>
  <td>5</td>
  </tr>
  <tr>
  <td>4</td>
  <td>December</td>
  <td>2021</td>
  <td>Pencil</td>
  <td>2021-12-04</td>
  <td>2021-12-05</td>
  <td>3</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>To calculate the total value of Bag's in the month for instance it would be a rate of 10 for the first two days of quantity Day 1 + Day 2; 10x6 = 60 plus the rate of 12 for the remaining 3 days - 12x(2+5+1) = 96 for a total of 156 for bags.</p>
  <p>I would like to update a 3rd table 'table3' with the total value of each item for that month the moment it is updated with a trigger. The 3rd table would look like this -</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>Item</th>
  <th>Month</th>
  <th>Year</th>
  <th>total</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Bag</td>
  <td>12</td>
  <td>2021</td>
  <td>156</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Pencil</td>
  <td>12</td>
  <td>2021</td>
  <td>56</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I know how to manually get the value I want and update table3 with it but have no idea how to automatically do this with a trigger which updates table3 whenever data is inserted or updated into table2.</p>
  <p>How do I go about doing something like this?</p>