May 20, 2008 at 7:18 am
Does anyone know of a way to accumulate values as per the following example:
1. I have data for each month of the year
2. Jan = 10, Feb = 20, Mar = 30, Apr = 40 etc
3. I want my query to return each month but as an accumulation of the previous months
4. Using the data in step 2 the query would return Jan = 10, Feb = 30, Mar = 60, Apr = 100 etc
May 20, 2008 at 7:31 am
There's an excellent article by Jeff Moden on running totals. You'll find it in the articles section here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 7:37 am
I gave it a bash, see if this can work for you:
create table monthacc(monthnum int, amount int)
go
insert monthacc(monthnum, amount)
select 1, 10
union all
select 2, 30
union all
select 3, 15
union all
select 4, 20
union all
select 5, 11
--get acumulated values
;with accumulated as
(
select monthnum mnum, amount from monthacc where monthnum = 1
union all
select a.monthnum as mnum, a.amount + b.amount as amount
from monthacc a inner join accumulated b on a.monthnum = b.mnum + 1
)
select mnum, amount from accumulated
go
drop table monthacc
go
Regards
Piotr
...and your only reply is slàinte mhath
May 20, 2008 at 8:10 am
Piotr Rodak (5/20/2008)
I gave it a bash, see if this can work for you:
That will work fine on small chunks of data, but recursion is inherently RBAR and therefor slow.
The article Gail speaks of is...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2008 at 8:28 am
Yes Jeff I agree, but there are only 12 moths in an average year. 😉
Piotr
...and your only reply is slàinte mhath
May 20, 2008 at 8:38 am
Piotr Rodak (5/20/2008)
Yes Jeff I agree, but there are only 12 moths in an average year. 😉Piotr
How many years is it being run over and does the query ever need to include multiple years? Does the query reset to 10 for each January, or continue counting from the prior December?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2008 at 8:56 am
Piotr Rodak (5/20/2008)
I gave it a bash, see if this can work for you:
create table monthacc(monthnum int, amount int)
go
insert monthacc(monthnum, amount)
select 1, 10
union all
select 2, 30
union all
select 3, 15
union all
select 4, 20
union all
select 5, 11
--get acumulated values
;with accumulated as
(
select monthnum mnum, amount from monthacc where monthnum = 1
union all
select a.monthnum as mnum, a.amount + b.amount as amount
from monthacc a inner join accumulated b on a.monthnum = b.mnum + 1
)
select mnum, amount from accumulated
go
drop table monthacc
go
Regards
Piotr
Thanks all for your responses.
Piotr, I understand the WITH statement but I don't understand how the query definition within it has produced the resultset. The output is exactly what I'm looking for but can you explain how SQL Server is working out -just so that I learn something rather than just copying it 🙂
May 20, 2008 at 9:16 am
Hi David,
This is CTE - new construct in SQL 2005 that allows for easy browsing of recursive structures. In this case you can't say that data are recursive, but you may consider calculating accumulative amounts as a recursive operation. There is good explanation of CTEs in BOL.
If you use recursive CTE, each query is executed for each row on higher level, so it may not be the most efficient way of solving your problem.
If you have lots of records to process, you may need another solution, as per Jeffs and GSquareds suggestions.
Regards
Piotr
...and your only reply is slàinte mhath
May 21, 2008 at 2:22 am
Piotr Rodak (5/20/2008)
Hi David,This is CTE - new construct in SQL 2005 that allows for easy browsing of recursive structures. In this case you can't say that data are recursive, but you may consider calculating accumulative amounts as a recursive operation. There is good explanation of CTEs in BOL.
If you use recursive CTE, each query is executed for each row on higher level, so it may not be the most efficient way of solving your problem.
If you have lots of records to process, you may need another solution, as per Jeffs and GSquareds suggestions.
Regards
Piotr
Hi Piotr,
Yes, Jeff's point is valid but thankfully I don't have that many records to process so this method should do the trick.
However, I've had a play but cannot get the CTE to work. I have a table that stores data per month in individual columns, i.e. each month has its own column. This is how the software supplier designed the table so I will not be able to change this. Having extracted the data I use UNPIVOT to list the data by month_id and value. Here is the initial SELECT statement that works:
select unit, datepart(m, '01-' + month_name + '-2008') as month_id, month_value
from myStoredProc
unpivot (month_value for month_name in (jul,aug,sep,oct,nov,dec,jan,feb,mar,apr,may,jun)) as month
where datepart(m, '01-' + month_name + '-2008') = 7
Our financial year starts in July hence the 7. So, how do I proceed from here because the following does not work:
with accumulated(unit, month_id, month_value) as
(
select unit, datepart(m, '01-' + month_name + '-2008') as month_id, month_value
from myStoredProc
unpivot (month_value for month_name in (jul,aug,sep,oct,nov,dec,jan,feb,mar,apr,may,jun)) as month
where datepart(m, '01-' + month_name + '-2008') = 7
union all
select a.unit, a.month_value + b.month_value
from myStoredProc a
unpivot (a.month_value for month_name in (jul,aug,sep,oct,nov,dec,jan,feb,mar,apr,may,jun)) as month
inner join accumulated b on a.datepart(m, '01-' + month_name + '-2008') = b.month_id + 1
)
May 21, 2008 at 5:14 am
I think that there is one issue here - you can't join straight on previous month number because Jan will not join to Dec. Try to normalize month numbers so you have numbers from 1 to 12, or join on date built from month numbers. Is hardcoding '-2008' ok? Shouldn't year switch to 2009 in Jan?
Piotr
...and your only reply is slàinte mhath
May 21, 2008 at 5:22 am
Hi,
The 2008 bit has only been put in to form a date so that the datepart will return the numerical value of the month only. Not sure I understand what you mean by not being able to join the months.
May 21, 2008 at 6:25 am
What results / error messages do you get when you run the query?
What I meant, is that you join for example month 8 with month 7 in the lower part of the union. What happens if month number exeeds 12?
Piotr
...and your only reply is slàinte mhath
May 21, 2008 at 6:40 am
The error I get is:
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ')'.
I see what you mean about the month exceeding 12 problem.
May 21, 2008 at 6:59 am
Well, you seem to have syntax error. Shouldn't month names be in quotes?
Could you send DDL, and a few rows of sample inserts?
It would be easier. By exceeding 12 I mean that for December, b.month_id + 1 will return 13, which will never join because datepart will not return 13.
Piotr
...and your only reply is slàinte mhath
May 21, 2008 at 7:50 am
Here is a sample script that should produce the table and populate it:
create table mySales (Unit_ID varchar(20),
Jul numeric(28,9), Aug numeric(28,9), Sep numeric(28,9),
Oct numeric(28,9), Nov numeric(28,9), Dec numeric(28,9),
Jan numeric(28,9), Feb numeric(28,9), Mar numeric(28,9),
Apr numeric(28,9), May numeric(28,9), Jun numeric(28,9))
insert into mySales (Unit_ID, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun)
select 'Italy', 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120
insert into mySales (Unit_ID, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun)
select 'France', 15, 25, 35, 45, 55, 65, 75, 85, 95, 105, 115, 125
insert into mySales (Unit_ID, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun)
select 'Spain', 17, 27, 37, 47, 57, 67, 77, 87, 97, 107, 117, 127
with accumulated as
(
select Unit_ID, month_value, datepart(m, '01-' + month_name + '-2008') as month_name
from mySales
unpivot (month_value for month_name in (jul,aug,sep,oct,nov,dec,jan,feb,mar,apr,may,jun)) as month
where datepart(m, '01-' + month_name + '-2008') = 1
union all
select a.Unit_ID, a.month_value + b.month_value
from mySales a
unpivot (month_value for month_name in (jul,aug,sep,oct,nov,dec,jan,feb,mar,apr,may,jun)) as month
inner join accumulated b on a.datepart(m, '01-' + month_name + '-2008') = b.month_name + 1
)
--drop table mySales
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply