Recursive sum data using group by month and ayear

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Recursive sum data using group by month and ayear

Author

Topic

espinho
Starting Member

1 Post

Posted - 2008-09-24 : 10:50:25

well with your help i was able to do the query i want, but i face another challange now that is driving me crazy:

here is de query that works:

DECLARE @datafim as datetime;

DEclare @datainicio as datetime;

SET @datafim = convert(datetime, '31/08/'+convert(varchar,year(getdate())),103);

SET @datainicio = convert(datetime, '01/08/'+convert(varchar,year((dateadd(YYYY,-1,getdate())))),103);

select datepart(year,date) as Ano,datepart(month,date) as Mes,sum(field1) as extdefp, sum(field2) as delvp, sum(field3) as extconc, sum(field4) as somadefp

from

(select c.conc_date as date, c.conc_extdefp as field1,0 as field2, 0 as field3, 0 as field4

from

COncern c

union

select d.data_date, 0 as field1,d.data_delvp as field2, d.data_extconctarget, 0

from

data d

union

select dt.dta_data as date, 0, 0, 0, 0

FROM

DataTempo dt

union

select c.conc_date as date, 0,0 as field2, 0 as field3, 0 as field4

from

COncern c

) abcd

WHERE

date between @datainicio and @datafim

group by datepart(year,date), datepart(month,date)

Order by datepart(year,date)

with gives me somethingh like this:

2007 8 56 1564632 19 0
2007 9 58 1112199 25 0
2007 10 56 990489852 29 0
2007 11 56 615000 9 0
2007 12 56 616100 9 0
2008 1 56 615000 9 0
2008 2 56 616100 9 0
2008 3 56 1230000 18 0
2008 4 56 616100 9 0
2008 5 57 615000 9 0
2008 6 56 616100 9 0
2008 7 57 615000 9 0
2008 8 56 616100 9 0

Now, my probem is getting the last field somadefp, this field should be the sum of all the valors in field1 , but this sum should be from one year before each month (sum of 12 months before), for example for the months for the first colummn:

year month field1 field2 field3 field4

2007 8 56 1 564632 19 0

field4 should be the sum of all field1 of the 12 months bettween 01/08/2006 to 31/07/2007

for the second colummn

year month field1 field2 field3 field4

2007 9 58 1112199 25 0

field4 should be the sum of all field1 of the 12 months bettween 01/09/2006 to 31/07/2008

I m getting lots of headheck trying to figure this out, the importance of having this in one query his to be able to put his information in a pareto chart in reporting services,

thnaks in advace for all the help..

Greetings

Rui

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 11:43:52

Give some table data along with structure and illustrate what you want. its quite difficult to get what you want from query (cant understand which column is what valors,somadefp,..)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources