SQL Math problem - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Math problem

Author

Topic

xdk1x
Starting Member

9 Posts

Posted - 2004-06-03 : 09:48:03

Hi!

I need to calculate some tablerows with the following shema:

MONTHYEAR ROWA ROWB ROWC ROWD
Jan-2004- --A- --B- --A- --B-
Feb-2004- --C- --D- -A+C -B+D
...
Dec-2004- --X- --Y- A+.X B+.Y
Jan-2005- --A- --B- --A- --B-

What I want to show is that the vaules of row a should be added to row c from januar to dezember. the same with row b and row d. when the next year begins the values of c and d shoudl start again with 0:

MONTHYEAR ROWA ROWB ROWC ROWD
Jan-2004- --3- --5- --3- --5-
Feb-2004- --7- --8- -10- -13-
Mar-2004- --2- --3- -12- -16-
...
Dec-2004- --9- --3- -n+9 -n+3
Jan-2005- --2- --1- --2- --1-
Feb-2005- --4- --1- --4- --2-

Thanks for your help!!!

Daniel

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-03 : 10:59:46

Here ya go...

Declare @myTable Table (mBegin datetime, A int, B int)
Insert Into @myTable Select mBegin = '1/1/2004', A=3, B=5
Insert Into @myTable Select mBegin = '2/1/2004', A=7, B=8
Insert Into @myTable Select mBegin = '3/1/2004', A=2, B=3

Select
monthYear = left(datename(month,mBegin),3) + '-' + convert(nvarchar,year(mBegin)),
A,
B,
C = (Select sum(A) From @myTable as B Where year(mBegin)=year(src.mBegin) and mBegin <= src.mBegin),
D = (Select sum(B) From @myTable as B Where year(mBegin)=year(src.mBegin) and mBegin <= src.mBegin)
From @myTable as src

Corey Aldebol

xdk1x
Starting Member

9 Posts

Posted - 2004-06-04 : 03:53:09

Thanks Corey!!!

It works perfect!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources