title: Sum value for each end of month
body: <p>I have two tables.</p>
  <p>Table A with CustomerID, InternalID, and AsOfDate.</p>
  <p>Table B with InternalID, Spend, StoreNum and AsOfDate.</p>
  <p>I am looking to create an output such that I have the sum of Spend per CustomerID for the last day of the month for last 12 months. Last day of the month is not 30 or 31, it goes by business days so I would like a query that looks for MAX date within each month to determine last day.</p>
  <p>Example tables with data and query.</p>
  <pre><code>Declare @MAXDATE DATETIME
  Select @MAXDATE = MAX (as_of_date) from table2 
  
  select
    s.customerID,
    sum(case when s.asofdate = CAST('11/29/21' AS DATETIME) then hn.spend end) as val_prevmonth,
    sum(case when s.asofdate = @MAXDATE then hn.spend end) as val_curr
  from table1 s
  join table2 hn on hn.asofdate = s.asofdate 
                   and hn.internalID = s.internalID
  where 
  and  (s.asofdate = CAST('11/30/21' AS DATETIME) or s.asofdate = @MAXDATE)
  group by s.customerID
  order by s.customerID;
  </code></pre>
  <p>Table A</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>CustomerID</th>
  <th>InternalID</th>
  <th>AsOfDate</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>610</td>
  <td>016</td>
  <td>11/30/2021</td>
  </tr>
  <tr>
  <td>610</td>
  <td>016</td>
  <td>11/29/2021</td>
  </tr>
  <tr>
  <td>610</td>
  <td>016</td>
  <td>11/28/2021</td>
  </tr>
  <tr>
  <td>610</td>
  <td>016</td>
  <td>10/29/2021</td>
  </tr>
  <tr>
  <td>610</td>
  <td>016</td>
  <td>10/15/2021</td>
  </tr>
  <tr>
  <td>610</td>
  <td>016</td>
  <td>9/15/2021</td>
  </tr>
  <tr>
  <td>610</td>
  <td>016</td>
  <td>9/14/2021</td>
  </tr>
  <tr>
  <td>610</td>
  <td>016</td>
  <td>8/10/2021</td>
  </tr>
  <tr>
  <td>725</td>
  <td>527</td>
  <td>11/30/2021</td>
  </tr>
  <tr>
  <td>725</td>
  <td>527</td>
  <td>11/29/2021</td>
  </tr>
  <tr>
  <td>725</td>
  <td>527</td>
  <td>11/28/2021</td>
  </tr>
  <tr>
  <td>725</td>
  <td>527</td>
  <td>10/29/2021</td>
  </tr>
  <tr>
  <td>725</td>
  <td>527</td>
  <td>10/15/2021</td>
  </tr>
  <tr>
  <td>725</td>
  <td>527</td>
  <td>9/15/2021</td>
  </tr>
  <tr>
  <td>725</td>
  <td>527</td>
  <td>9/14/2021</td>
  </tr>
  <tr>
  <td>725</td>
  <td>527</td>
  <td>8/10/2021</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Table B</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Spend</th>
  <th>InternalID</th>
  <th>StoreNum</th>
  <th>AsOfDate</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>5000</td>
  <td>016</td>
  <td>123</td>
  <td>11/30/2021</td>
  </tr>
  <tr>
  <td>4000</td>
  <td>016</td>
  <td>321</td>
  <td>11/30/2021</td>
  </tr>
  <tr>
  <td>3000</td>
  <td>016</td>
  <td>456</td>
  <td>11/28/2021</td>
  </tr>
  <tr>
  <td>4500</td>
  <td>016</td>
  <td>654</td>
  <td>10/29/2021</td>
  </tr>
  <tr>
  <td>5000</td>
  <td>016</td>
  <td>789</td>
  <td>10/15/2021</td>
  </tr>
  <tr>
  <td>4000</td>
  <td>016</td>
  <td>756</td>
  <td>9/15/2021</td>
  </tr>
  <tr>
  <td>9000</td>
  <td>016</td>
  <td>987</td>
  <td>9/15/2021</td>
  </tr>
  <tr>
  <td>1000</td>
  <td>016</td>
  <td>545</td>
  <td>8/10/2021</td>
  </tr>
  <tr>
  <td>6000</td>
  <td>527</td>
  <td>124</td>
  <td>11/30/2021</td>
  </tr>
  <tr>
  <td>4500</td>
  <td>527</td>
  <td>354</td>
  <td>11/30/2021</td>
  </tr>
  <tr>
  <td>9000</td>
  <td>527</td>
  <td>745</td>
  <td>11/28/2021</td>
  </tr>
  <tr>
  <td>8500</td>
  <td>527</td>
  <td>846</td>
  <td>10/29/2021</td>
  </tr>
  <tr>
  <td>7500</td>
  <td>527</td>
  <td>646</td>
  <td>10/29/2021</td>
  </tr>
  <tr>
  <td>6000</td>
  <td>527</td>
  <td>311</td>
  <td>9/14/2021</td>
  </tr>
  <tr>
  <td>3000</td>
  <td>527</td>
  <td>211</td>
  <td>9/14/2021</td>
  </tr>
  <tr>
  <td>2500</td>
  <td>527</td>
  <td>121</td>
  <td>8/10/2021</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Table C. Note <strong>CustomerID 725 is missing from September</strong> because there were no transactions conducted by them on the last business day of September, the 15th. Also column names do not need to have the date in them, I put it there for sake of clarity.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>CustomerID</th>
  <th>11/30/2021 TotalSpend</th>
  <th>10/29/2021 TotalSpend</th>
  <th>9/15/2021 TotalSpend</th>
  <th>8/10/2021 TotalSpend</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>610</td>
  <td>9000</td>
  <td>4500</td>
  <td>1300</td>
  <td>1000</td>
  </tr>
  <tr>
  <td>725</td>
  <td>10500</td>
  <td>16000</td>
  <td></td>
  <td>2500</td>
  </tr>
  </tbody>
  </table>
  </div>