title: Field to display Previous 30 Day Total
body: <p>I've got this table:</p>
  
  <pre><code>CREATE TABLE #Data1 
      (
      [Market] VARCHAR(100) NOT NULL, 
      [Operator] VARCHAR(100) NOT NULL,                                                                   
      [Date] DATETIME NOT NULL,   
      [Measure] VARCHAR(100) NOT NULL, 
      [Amount] NUMERIC(36,10)  NOT NULL, 
                  --new calculated fields 
      [DailyAvg_30days] NUMERIC(38,6) NULL    DEFAULT 0                                   
      )
  </code></pre>
  
  <p>I've populated all the fields apart from <code>DailyAvg_30days</code>.<br>
  This field needs to show the total for the preceding 30 days e.g.<br>
  1. if <code>Date</code> for a particular record is 2nd Dec then it will be the total for the period 3rd Nov - 2nd Dec inclusive.<br>
  2. if <code>Date</code> for a particular record is 1st Dec then it will be the total for the period 2nd Nov - 1st Dec inclusive.</p>
  
  <p>My attempt to try to find these totals before updating the table is as follows:</p>
  
  <pre><code>SELECT 
      a.[Market],
      a.[Operator],
      a.[Date],
      a.[Measure],
      a.[Amount],
      [DailyAvg_30days] = SUM(b.[Amount])
  FROM 
      #Data1 a
      INNER JOIN #Data1 b
          ON 
          a.[Market] = b.[Market] AND
          a.[Operator] = b.[Operator] AND
          a.[Measure] = b.[Measure]   AND
          a.[Date] &gt; b.[Date]-30 AND
          a.[Date] &lt;= b.[Date]
  GROUP BY
      a.[Market],
      a.[Operator],
      a.[Date],
      a.[Measure],
      a.[Amount]
  ORDER BY 1,2,4,3
  </code></pre>
  
  <p>Is this a valid approach or do I need to approach this from a different angle?</p>
  
  <hr>
  
  <p><strong>EDIT</strong></p>
  
  <p>There was a flaw in the logic. My colleague Jagoda got the answer first followed by Gordon Linoff who suggested a syntax which I find easier to read:</p>
  
  <pre><code>FROM 
      #DataCas a
      INNER JOIN #DataCas b
          ON 
          a.[Market] = b.[Market] AND
          a.[Operator] = b.[Operator] AND
          a.[Measure] = b.[Measure]   AND
          b.[Date]  BETWEEN a.[Date]-29 AND a.[Date]
  </code></pre>