Calculation for accumulated figure

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)

Calculation for accumulated figure

Author

Topic

cujee75
Starting Member

2 Posts

Posted - 2008-02-27 : 00:00:27

I've created SQL statement to display data that include Project ID, WBS Element, DebitTotal, CreditTotal, Net (net figure is sum between DebitTotal and CreditTotal = CreditTotal - DebitTotal), FiscalPeriod and FiscalYear. To display data I need to key-in the timekey. If 200702 refer to data for February 2007.

The problem is, how to calculate accumulated figure for Net? I have to display YearToDate figure which is accumulated figure for every month for Net table.
e.g Net for February is 1000, March is 2000. YearToDate should be 3000 (1000+2000).
Another problem is because I'm using the timekey to display the data, table name is the same for every month.
How to accumulate the Net figure? Please help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-27 : 00:06:14

Can you provide me your sample data with table structure?

cujee75
Starting Member

2 Posts

Posted - 2008-02-27 : 01:22:49

SQL query is :

select Indicator, Project, Timekey,
sum(Debit) As TotalDebit, sum(Credit) As TotalCredit, sum(Debit)-sum(Credit) As Net
from @TotalTemp
group by Indicator,Project, TimeKey
having CAST(TimeKey As varchar) = @TKey and Substring (Indicator,1,4) = 'REV-'
order by Indicator, Project, TimeKey

Output is:
February
Indicator|Project|TimeKey|TotalDebit|TotalCredit |Net
REV-MOB |MOB 2 |200702 |0.00 |16,347,783.00|-16,347,783.00
REV-PCSB |PCSB 20|200702 |6,043,881.00|22,528,189.00|-16,484,308.00

March
Indicator|Project|TimeKey|TotalDebit|TotalCredit |Net
REV-MOB |2004 |200703 |16,347,783.00|16,887,004.01|-539,221.01
REV-PCSB |PCSB 2 |200703 |13,125,318.86|39,981,012.86|-26,855,694.00

To get YearToDate, I have to sum Net figure for every month. Example, YearToDate = [(-16,347,783.00)+(-539,221.01)] because it came from the same project REV-MOB. If I hve Net figure for April for REV-MOB, I'll sum it as well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-27 : 02:00:00

select Indicator, Project, Timekey,
sum(Debit) As TotalDebit, sum(Credit) As TotalCredit, sum(Debit)-sum(Credit) As Net into #Temp
from @TotalTemp
group by Indicator,Project, TimeKey
having CAST(TimeKey As varchar) = @TKey and Substring (Indicator,1,4) = 'REV-'
order by Indicator, Project, TimeKey

Update t1
SET t1.Net=t1.Net + t2.Net
FROM acct t1
INNER JOIN acct t2
on t2.Indicator=t1.Indicator
and t2.TimeKey<t1.TimeKey

select * from t1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources