cumulative number - 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)

cumulative number

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-03-31 : 18:43:14

Below code is supposed to return the cumulative revenue for year 2005 and 2006 based on the date parameters you input.

For example, if I enter month = 1 and year 2006
it will return the sum of revenue from 1 2005 to 12 2005.
it works fine until I input 2 2006 ( it should grap the sum of revenue from month <= 1 and year <= 2006 ),but It doesn't accumulate the revenue anymore, it only return the sum of 1 2006 data. I am not sure why?
How can I fix this issue?

declare @month int
declare @year int
declare @unit_abbr varchar(12)

set @month = 2
set @year = 2006
set @unit_abbr = 'A13'

declare @prev_month int
declare @prev_year int
SET @prev_month = MONTH(DATEADD(month, @month -2, DATEADD(year, @year - 1900, 0)))
SET @prev_year = YEAR(DATEADD(month, @month -2, DATEADD(year, @year - 1900, 0)))

select @prev_month
select @prev_year

select sum(revenue) from revenue_aggregate_assertions where (month <= @prev_month and year <= @prev_year and unit_abbr = @unit_abbr)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-31 : 18:55:58

this is because when year = 2006, month = 2, the prev_year = 2006 and prev_month = 1
so your where condition
month <= 1 only will result in sum of Jan records only.

You can store your data in datetime and not separate in 2 integer field. It will make your query much easier.


select sum(revenue) 
from   revenue_aggregate_assertions 
where  unit_abbr = @unit_abbr
and    (year * 100) + month <=  (@prev_year * 100) + @prev_month

KH
Choice is an illusion, created between those with power, and those without.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources