title: Select max date per month SQL SERVER
body: <p>I have a database table that looks like this called Totals and I'm trying to select the max date per month per per person so I can average the person's balance over the months</p>
  
  <pre><code> Date       Person     Balance
  01-15-12       A        79
  01-23-12       c        150
  01-17-12       A        65
  02-23-12       c        150
  02-15-12       A        70
  03-23-12       c        15
  03-15-12       A        705
  03-28-12       c        150
  04-15-12       A        700
  04-23-12       c        150
  </code></pre>
  
  <p>I'm joining this table to a temp table called #bal which contains just people like A B C ...etc
  So for each month I just want the max row per month per person so that I can sum the balances and find the average balance over the months per person.</p>
  
  <pre><code>  create table #bal 
   (  
   person bigint,
   avgbal decimal,
   mxdate datetime
  
   )
    insert into #bal(person,avgbal,mxdate)
    select 
    b.person,
    (sum(s.BAL)/12) as avgbal,
    max(date) as mxdate 
  
    from #bal b
    inner join TOTALS s on (b.person=s.person)
    where DATE between '01-17-2012' and getdate()
    group by b.person
  </code></pre>
  
  <p>Have something like this so far that's grouping by date but I just want to select the max day per month.</p>