Select max date per month SQL SERVER

Question

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

 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

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.

  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

Have something like this so far that's grouping by date but I just want to select the max day per month.

Solution 1

I've produced a couple of samples based on some sample data I created based on the set above. I'm not sure whether you want the last value in each month or the max value, as these aren't necessarily the same, so I've written basic queries for both:

declare @table table
(
date date,
person varchar(10),
balance int
)

insert into @table
select '01-15-12', 'A', 79
union all
select '01-23-12', 'c', 150
union all
select '01-17-12', 'A', 65
union all
select '02-23-12', 'c', 150
union all
select '02-15-12', 'A', 70
union all
select '03-23-12', 'c', 15
union all
select '03-15-12', 'A', 705
union all
select '03-28-12', 'c', 150
union all
select '04-15-12', 'A', 700
union all
select '04-23-12', 'c', 150;

-- Average of max balance in month
with maxMonth as
(
  select year = year(date)
    , month = month(date)
    , person, monthMaxBalanace = max(balance)
  from @table
  where date between '01-17-2012' and getdate()
  group by year(date), month(date), person
)
select person, maxInMonthAverage = avg(monthMaxBalanace)
from maxMonth
group by person;

Or if you need to use the last balance in each month you can change the query:

-- Average of last balance in month
with lastInMonth as
(
  select year = year(date)
    , month = month(date)
    , person, balance
    , monthRank = row_number() over (partition by year(date), month(date), person order by date desc)
  from @table
  where date between '01-17-2012' and getdate()
),
lastRows as
(
  select * from lastInMonth where monthRank = 1
)
select person, lastInMonthAverage = avg(balance)
from lastRows
group by person;

Based on your example query (i.e. 17-Jan and greater) the results are the same, but if you include the value from the 15th they are slightly different due to the different logic in the two queries.

Solution 2

this retrieve you the row of last day on month for each month and each person..

select V.[date]
  ,V.person
  ,V.balance
from (  select [person]
          ,[date]
          ,max([date]) over(partition by person,datediff(mm,0,[date])) as [max_date]
          ,balance
    from @table
)V
where V.[date]=V.max_date

this retrieve you the average over all months in period

select V.person
,SUM(balance)/12 as avgbal_as_u_calc
,AVG(balance) as average_balance
from (  select [person]
          ,[date]
          ,max([date]) over(partition by person,datediff(mm,0,[date])) as [max_date]
          ,balance
    from @table
)V
where V.[date]=V.max_date
group by V.person

Comments

[bonCodigo]

@ERead take a look at the demo and comment please, let us know if you need any other changes, if the solution does or doesn't provide you what you need..

[Jt2ouan]

Not really sure how to implement this into my actually SQL, where is the Totals table? I have #bal as temp table of persons I join that to Totals table on person, which has the values or balance and dates I showed above

[Jt2ouan]

so average of max balance in month that selects the max balance for each month and sums them together than averages them.

[bonCodigo]

I created this solution based on the table that you have provided above with data. Do you also have a totals table? I got the impression that you only have persons table (date, person, bal) and you want to get the sum of max dates per month per person, then get the average out of it...

[Ian Preston]

The first query/resultset, for each person, takes the max balance in each month within the specified date range then averages those max values.

[Art]

I guess competing "experts" again... I'm here to help not to get scores. Cooperation is the key, not the opposite.

[Jt2ouan]

oh yeah this is good sorry I sorry your subquery but missed below it were you were summing and gettign the average so I was thinking well this is kinda right cause your gettign max value per month

[Ian Preston]

Cool - I've split the code above for more clarity. You should be able to paste the code directly into a query window and use that as a basis for your own code.