Select only the most recent entry for each month

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

Let's see below, if we are in October, I want to take and summarize only the entries for 24/24/2014 8:57

I also want to write a separate query that does the same thing but in August.

My goal is to do this by specifying and setting variables. I currently use this in every one of my articles. I just got stuck on finding out the maximum (date) portion of what I need to do.

DECLARE @FirstDayofPrevMonth datetime
DECLARE @LastDayofPrevMonth datetime

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


enter image description here

UPDATE: Here's what I 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



source to share

2 answers


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 2014 and 10 with variables after setting the year and month you want. The above query is logically what you want, you can just rewrite the variables you are using. You can also use the FirstDayofPrevMonth variable and call YEAR and MONTH to get the correct values ​​to compare against your table.



this will give you the sum for each largest available day of each month.

select ImportDate, sum(NumOfAccounts)
from mytable t1
where not exists (
    select 1
    from mytable t2 where t2.ImportDate > t1.ImportDate
    and month(t2.ImportDate) = month(t1.ImportDate)
    and year(t2.ImportDate) = year(t1.ImportDate)
group by ImportDate
order by ImportDate


if you want the previous month to add the following to your place.

and month(dateadd(month,-1,getdate())) = month(ImportDate)
and year(dateadd(month,-1,getdate())) = year(ImportDate)


The same query using analytic functions, which should be slightly faster

select ImportDate, sum(NumOfAccounts)
from (
    select *,
    rank() over (partition by month(ImportDate), year(ImportDate) order by ImportDate desc) rk
    from mytable
) t1 where rk = 1
group by ImportDate
order by ImportDate




All Articles