Previous Months Data 1

[CrystalVis]

I need to collate all data by company and accounts for the current year. you can see from the case statement
that i want to aggregate the amount from the beginning of the current year up to last month of the current year.
however, when i get to Jan of a new year i need Dec of the previous year. so if the report is run on 1/1/2006, i would like to aggregate
the data from 1/1/2005 - 12/31/2005. the report normally run sometimes after the 15th of the following month for the previous month in order to give the accounting dept enough time to post all the month transactions. can you please help.
thanks,


select
company_,

case when month(getdate())-1 = 1 then sum(jan_+start_of_year_)

when month(getdate())-1 = 2 then sum(jan_+feb_+start_of_year_)

when month(getdate())-1 = 3 then sum(jan_+feb_+mar_+start_of_year_)

when month(getdate())-1 = 4 then sum(jan_+feb_+mar_+apr_+start_of_year_)

when month(getdate())-1 = 5 then sum(jan_+feb_+mar_+apr_+may_+start_of_year_)

when month(getdate())-1 = 6 then sum(jan_+feb_+mar_+apr_+may_+jun_+start_of_year_)

when month(getdate())-1 = 7 then sum(jan_+feb_+mar_+apr_+may_+jun_+jul_+start_of_year_)

when month(getdate())-1 = 8 then sum(jan_+feb_+mar_+apr_+may_+jun_+jul_+aug_+start_of_year_)

when month(getdate())-1 = 9 then sum(jan_+feb_+mar_+apr_+may_+jun_+jul_+aug_+sep_+start_of_year_)
when month(getdate())-1 = 10 then sum(jan_+feb_+mar_+apr_+may_+jun_+jul_+aug_+sep_+oct_+start_of_year_)
when month(getdate())-1 = 11 then sum(jan_+ feb_+ mar_+apr_+may_+jun_+jul_+aug_+sep_+oct_+nov_+start_of_year_)
when month(getdate())-1 = 12 then sum(jan_+feb_+mar_+apr_+may_+jun_+jul_+aug_+sep_+oct_+nov_+dec_+start_of_year_)
end as RevPost

from genl_balance
where post_year_ = year(getdate())
and company_ in ('001','012','015','021','025')
and account_ in ('0500','0501')
group by company_

 

[vongrunt]

Idea:

select
	company_,
	case month(dateadd(mm, -1, getdate()))
	when  1 then sum(jan_+start_of_year_)
   when  2 then sum(jan_+feb_+start_of_year_)
   when  3 then sum(jan_+feb_+mar_+start_of_year_)
   when  4 then sum(jan_+feb_+mar_+apr_+start_of_year_)
   when  5 then sum(jan_+feb_+mar_+apr_+may_+start_of_year_)
   when  6 then sum(jan_+feb_+mar_+apr_+may_+jun_+start_of_year_)
   when  7 then sum(jan_+feb_+mar_+apr_+may_+jun_+jul_+start_of_year_)
   when  8 then sum(jan_+feb_+mar_+apr_+may_+jun_+jul_+aug_+start_of_year_)
   when  9 then sum(jan_+feb_+mar_+apr_+may_+jun_+jul_+aug_+sep_+start_of_year_)
   when 10 then sum(jan_+feb_+mar_+apr_+may_+jun_+jul_+aug_+sep_+oct_+start_of_year_)
   when 11 then sum(jan_+ feb_+ mar_+apr_+may_+jun_+jul_+aug_+sep_+oct_+nov_+start_of_year_)
   when 12 then sum(jan_+feb_+mar_+apr_+may_+jun_+jul_+aug_+sep_+oct_+nov_+dec_+start_of_year_)
   end    as RevPost
from genl_balance
where post_year_ = year(dateadd(mm, -1, getdate()))
	and company_ in ('001','012','015','021','025')
	and account_ in ('0500','0501')
group by company_

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)