Sum Amount Across Fiscal Year - 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 2005 Forums

Transact-SQL (2005)

Sum Amount Across Fiscal Year

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-08-29 : 12:19:18

Folks,
Good people of SqlTeam, Thanks for the other responses, they really helps alot.. and am learning fast..

I have this problem,

Fiscal year starts 200805 and Ends 200904. I want to sum Amount across the fiscal year, that in between, i can generates YTDs.

Am able to do this until 200812, but when it's 200901.. I got lost.. Please help, how to do this.

sum(CASE WHEN CAST(LEFT(s.dw_period_key,4) AS INT) = @year AND CAST(RIGHT(s.dw_period_key, 2) AS INT) BETWEEN 5 and @prd THEN isnull(s.actual, 0) ELSE 0 END) AS YTDAmtAct

I can only do for present year, getting to the other year inthe fiscal is giving me troubles..

Thanks sirs

I sign for fame not for shame but all the same, I sign my name.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-29 : 12:42:49

Select year(dateAdd(Month,5,a.mydate)) as MyYear, Sum(a.MyAmounts)
From
MYtable a
group by year(dateAdd(Month,5,a.mydate))
order by MyYear

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-29 : 13:00:22

Assuming your dates are DATETIME datatypes:
create a table with a range of dates and JOIN by the range.


declare @fy table (periodStart datetime, periodend datetime, fiscalYear int)
insert @fy 
select '2006-05-01', '2007-05-01', 2006 union all
select '2007-05-01', '2008-05-01', 2007 union all
select '2008-05-01', '2009-05-01', 2008

select fy.fiscalYear
       ,sum([AmtCol])
from @fy fy
inner join [someOtherTable] d
       on d.[dtCol] >= periodStart
       and d.[dtCol] < periodEnd
group by fy.fiscalYear

Ugh, looking at your example I'm guessing you are storing dates as CHAR(6), right?

EDIT:
Well, even so I guess this would work:


declare @fy table (periodStart char(6), periodend char(6), fiscalYear int)
insert @fy 
select '200605', '200705', 2006 union all
select '200705', '200805', 2007 union all
select '200805', '200905', 2008

Be One with the Optimizer
TG

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-29 : 13:16:06

Assuming it is stored in char
can't you do like this:

Declare @StartPeriod char(6)
Declare @EndPeriod char(6)

select sum(CASE WHEN dw_period_key between @StartPeriod and @EndPeriod then isnull(actual, 0) ELSE 0 END) AS YTDAmtAct
from your table

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-09-01 : 04:19:11

Thanks all,
sodeep I used that, and it's perfect.. thanks all once again

I sign for fame not for shame but all the same, I sign my name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources