Select only most recent record for each month

Question

I'm trying to write a query that will allow me to grab ONLY the most recent records for each month, and then sum them. Below is an example of my table. What I want to be able to do is select the Prior month. If I can do that, I can figure out how to grab 2 months ago, a year ago, the quarter etc.

Looking below, if we are in October, I want to grab and sum up only the records on 9/24/2014 8:57

I'm also going to want to write a separate query that does the same thing, but for August.

My goal is to do this by Declaring and Setting variables. Currently I'm using this in each of my where clauses. I'm just stuck with figuring out the max(date) part of what I need to do.

DECLARE @FirstDayofPrevMonth datetime
SET @FirstDayofPrevMonth = CONVERT(DATE, DATEADD(MONTH, -1, DATEADD(DAY, 1 - DAY(GETDATE()),    
GETDATE())))
DECLARE @LastDayofPrevMonth datetime
SET @LastDayofPrevMonth = CONVERT(DATE, DATEADD(DAY, 1 - DAY(GETDATE()), GETDATE()))


DECLARE @FirstDayofPrevMonthPrior datetime
SET @FirstDayofPrevMonthPrior = dateadd(MONTH, -2,@FirstDayofPrevMonth)
DECLARE @LastDayofPrevMonthPrior datetime
SET @LastDayofPrevMonthPrior = DATEADD(MONTH,-2,@LastDayofPrevMonth)

UPDATE: Here's what I have used as my final working solution:

SELECT SUM(NumofAccounts) AS Total
                FROM dbo.Summary
                WHERE ImportDate =  (select MAX(importdate) from AllAcctInfoSummary 
                    where year(importdate) = year(@LastDayofPrevMonth)
                    and month(importdate) = month(@LastDayofPrevMonth))
                    group by ImportDate

Answer 1

Try:

select sum(some_column)
from my_table
where importdate = 
(select max(importdate) 
from my_table
where year(importdate) = 2014 
and month(importdate) = 10)
group by importdate

You can replace the 2014 and 10 with variables after setting the year and month you want. The query above is logically what you want, you can just tinker with the variables you use. You could also use your FirstDayofPrevMonth variable and call YEAR and MONTH on it to get the correct values to compare to your table.