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
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 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
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 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.
source to share
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
source to share