title: MYSQL - Calculating MTD in view, per Record/Row
body: <p>I am trying to figure out how create a view, that calculates Month to Date totals of the 'cattotal_food' field for each record.</p>
  
  <p>sample data:</p>
  
  <pre><code>report_date, cattotal_food
  2017-01-01, 100
  2017-01-02, 100
  2017-01-03, 100
  2017-01-04, 100
  </code></pre>
  
  <p>Here is what I have now:</p>
  
  <p>I created a function, called FIRST_DAY:</p>
  
  <pre><code>CREATE FUNCTION FIRST_DAY(input_date DATE) 
  RETURNS DATE DETERMINISTIC
  RETURN ADDDATE(LAST_DAY(SUBDATE(input_date, INTERVAL 1 MONTH)),1);
  </code></pre>
  
  <p>I then created a column in my view, to confirm it was selecting the correct date:</p>
  
  <pre><code>(select FIRST_DAY(report_date)) AS first_day_month,
  </code></pre>
  
  <p>This does infact, show the first day of the month the report was run.</p>
  
  <p>So, when I incorporate it into my view, I get the same value in every row (which is the the total for the entire month, not the total up to the date listed in report_date).</p>
  
  <pre><code>(select sum(cattotal_food) from table 
  WHERE report_date BETWEEN first_day_month AND report_date) as cattotal_food_mtd
  </code></pre>
  
  <p>With the code above, I my view looks like this:</p>
  
  <pre><code>report_date, cattotal_food, cattotal_food_MTD
  2017-01-01, 100, 400
  2017-01-02, 100, 400
  2017-01-03, 100, 400
  2017-01-04, 100, 400
  </code></pre>
  
  <p>I want it to look like this:</p>
  
  <pre><code>report_date, cattotal_food, cattotal_food_MTD
  2017-01-01, 100, 100
  2017-01-02, 100, 200
  2017-01-03, 100, 300
  2017-01-04, 100, 400
  </code></pre>
  
  <p>Any thoughts on how I can achieve this?
  Thanks for any and all responses!</p>
  
  <h2>UPDATE:</h2>
  
  <p>I tried the following query, which did <strong>not</strong> work:</p>
  
  <pre><code>IF (report_date = date_sub(report_date, interval day(report_date) -1 DAY), 
  cattotal_food,
      (select cattotal_food + 
          (select sum(cattotal_food) 
          from table 
          where report_date &gt;= date_sub(report_date, interval day(report_date) -1 DAY)
              AND report_date &lt;= yesterday)))
              AS cattotal_food_mtd
  from t;
  </code></pre>
  
  <p>However, if I calculate 'Yesterdays' date in another column and reference it in the query, then it <strong>does</strong> work.</p>
  
  <pre><code>sub_date(report_date, INTERVAL 1 DAY) yesterday,
  IF (report_date = date_sub(report_date, interval day(report_date) -1 DAY), 
      cattotal_food,
          (select cattotal_food + 
              (select sum(cattotal_food) 
              from table 
              where report_date &gt;= date_sub(report_date, interval day(report_date) -1 DAY)
                  AND report_date &lt;=yesterday)))
      AS cattotal_food_mtd
  from t;
  </code></pre>