Running Total for Fiscal Year Calculation

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

SQL Server Development (2000)

Running Total for Fiscal Year Calculation

Author

Topic

lnmhelp
Starting Member

4 Posts

Posted - 2007-10-11 : 14:04:16

Hello,

Here is a situation I have come across. I need a stored procedure that can calculate running totals for a fiscal year starting october. This procedure takes a fiscal year and costcenter as user input: like 2003-2004 which is a varchar field and 123456789 as costcenter which is also a varchar field.

My table is made up of
Fiscal Year,CostCenter, Month, Fiscal Month, Cost, Fiscal Year Total.
(Fiscal Month = 1 when Month = 10 for a given Fiscal Year)

Fiscal Year Total is the running total field.

How do I accomplish this?

Thanks in advance!

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 14:58:31

lnmhelp: Welcome to SQL Team!

Sorry Mate, but I'm not going to start from the position of having to create tables, invent sample data, and THEN begin to create a query - which may, therefore, be based on all the wrong assumptions, when I could be helping other folk instead.

Have a read of this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20to%20ask%20a%20question%20on%20SQL%20Team%20and%20get%20a%20quick%20answer

and if you post something similar then folk here are likely to help you.

Kristen

lnmhelp
Starting Member

4 Posts

Posted - 2007-10-11 : 15:32:13

Thank you Kirsten,
I will read up and then post. Appreciate your candor.

lnmhelp
Starting Member

4 Posts

Posted - 2007-10-12 : 09:53:48

Here is my test data. Any feedback in this matter is appreciated.

This is my table:

CREATE TABLE MyTable (

CostCenter varchar (7) NOT NULL ,
FiscalYear varchar (9) NOT NULL ,
Month tinyint NULL ,
Cost money NULL ,
FiscalMonth tinyint NULL ,
FiscalYearTotal money NULL
)

Here are some values:

INSERT INTO MyTable(CostCenter, FiscalYear, Month, Cost, FiscalMonth, FiscalYearTotal)
select '1111111','2004-2005',3,125.0000,6,.0000
union all
select '1111111','2004-2005',4,150.0000,7,.0000
union all
select '1111111','2005-2006',12,200.0000,3,.0000
union all
select '1111111','2004-2005',5,55.0000,8,.0000
union all
select '1111111','2004-2005',9,80.0000,12,.0000
union all
select '5555555','2005-2006',10,55.0000,1,55.0000
union all
select '5555555','2004-2005',10,100.0000,1,100.0000
union all
select '4444444','2005-2006',10,120.0000,1,120.0000
union all
select '5555555','2005-2006',6,140.0000,9,.0000
union all
select '5555555','2005-2006',5,65.0000,8,.0000
union all
select '5555555','2005-2006',4,75.0000,7,.0000
union all
select '5555555','2005-2006',3,85.0000,6,.0000
union all
select '5555555','2005-2006',2,45.0000,5,.0000
union all
select '5555555','2005-2006',1,65.0000,4,.0000
union all
select '5555555','2005-2006',7,95.0000,10,.0000
union all
select '5555555','2005-2006',8,145.0000,11,.0000
union all
select '5555555','2005-2006',9,155.0000,12,.0000
union all
select '5555555','2005-2006',11,35.0000,2,.0000
union all
select '5555555','2005-2006',12,750.0000,3,.0000
union all
select '5555555','2004-2005',12,109.0000,3,.0000

Expected Result:

Select CostCenter, FiscalYear, Month, Cost, FiscalMonth, FiscalYearTOtal from MyTable where CostCenter = '5555555' and FiscalYear = '2005-2006'

CostCenter FiscalYear Month Cost FiscalMonth FiscalyearTotal
========================================================================================
5555555 2005-2006 10 55.0000 1 55.0000
5555555 2005-2006 6 140.0000 9 1315.0000
5555555 2005-2006 5 65.0000 8 1175.0000
5555555 2005-2006 4 75.0000 7 1110.0000
5555555 2005-2006 3 85.0000 6 1035.0000
5555555 2005-2006 2 45.0000 5 950.0000
5555555 2005-2006 1 65.0000 4 905.0000
5555555 2005-2006 7 95.0000 10 1410.0000
5555555 2005-2006 8 145.0000 11 1555.0000
5555555 2005-2006 9 155.0000 12 1710.0000
5555555 2005-2006 11 35.0000 2 90.0000
5555555 2005-2006 12 750.0000 3 840.0000

Kristen
Test

22859 Posts

Posted - 2007-10-12 : 10:06:28

[code]
Select CostCenter, FiscalYear, Month, Cost, FiscalMonth,
[FiscalYearTOtal] =
(
SELECT SUM(Cost)
FROM MyTable AS T2
WHERE T2.CostCenter = T1.CostCenter
AND T2.FiscalYear = T1.FiscalYear
-- Select earlier fiscal months, and self, only
AND T2.FiscalMonth <= T1.FiscalMonth
)
from MyTable AS T1
where CostCenter = '5555555'
and FiscalYear = '2005-2006'
[/code]
A JOIN to a nested Sub-Select, containing an Aggregate, might be more efficient if you will be outputting LOTs of rows in the ResultSet.

Kristen

lnmhelp
Starting Member

4 Posts

Posted - 2007-10-12 : 11:59:36

Thanks Kristen, Works great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources