CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ThatRiddimGuy
ThatRiddimGuy

Reputation: 391

Sum of numbers from previous year month until this years month in SQL

Being a beginner, I'm having a hard time coding this particular scenario in SQL Server 2008

enter image description here

As you can see, The SUM column for July 2017 for example is equal to the following: August to Dec 2016 + Jan to July 2017 = 4625

Similarly, the SUM column for August 2017 is equal to the following: Sep to Dec 2016 + Jan to August 2017 = 4625

enter image description here

How can I automate this from month to month?

I appreciate any help I can get. Trying to code this in SQL Server 2008

Upvotes: 0

Views: 1782

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

This sounds like you want window functions. Assuming your data is already summarized by month:

select t.*,
       sum(numbers) over (order by yyyymm rows between 11 preceding and current row) as prev_12_sum
from t;

If the data is not already summarized, you can put this in a group by as well:

select year(date), month(date),
       sum(sum(numbers)) over (order by year(date), month(date) rows between 11 preceding and current row) as prev_12_sum
from t
group by year(date), month(date)
order by min(date);

Upvotes: 0

S3S
S3S

Reputation: 25152

Using this methodology to find the first day of the current month:

select dateadd(month, datediff(month, 0, getdate()), 0)

We can expan on it to get the first day, of the next month, a year ago... i.e. 11 months ago.

select dateadd(month, datediff(month, 0, dateadd(month,-11,getdate())), 0)

Then, we just need to use it in a where clause to limit your data...

declare @startDate = (select dateadd(month, datediff(month, 0, dateadd(month,-11,getdate())), 0))
declare @endDate = getdate()

select sum(someColumn)
from someTable
where dateColumn between @startDate and @endDate

Since you didn't provide your actual data set, just some pivoted data, I'm not sure of your column and table names

Upvotes: 1

Related Questions