Sum up certain accounts accross period

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 2008 Forums

Transact-SQL (2008)

Sum up certain accounts accross period

Author

Topic

yingchai
Starting Member

33 Posts

Posted - 2012-03-13 : 23:44:44

Hi SQL gurus,

I have a fact table shown as below. It consists a mixture of Profit & Loss (PL) and Balance Sheet (BS) accounts. The PL account code is in 600000x series and the BS account code is in 500000x series.

Currently, the BS accounts are stored as YTD basis and the PL accounts are stored as periodic basis.


Account   | Organization    | Period   | Year | Amount 
------------------------------------------------- 
6000001    | PM1             | 06       | 2011 | 100
6000002    | PM1             | 06       | 2011 | 250
6000001    | PM1             | 09       | 2011 | 400
6000002    | PM1             | 09       | 2011 | 500
6000001    | PM2             | 06       | 2011 | 150
6000002    | PM2             | 06       | 2011 | 230
6000001    | PM2             | 09       | 2011 | 330
6000002    | PM2             | 09       | 2011 | 490
5000001    | PM1             | 06       | 2011 | 100
5000002    | PM1             | 06       | 2011 | 250
5000001    | PM1             | 09       | 2011 | 400
5000002    | PM1             | 09       | 2011 | 500

My target now is to convert the above table to a view that stores all accounts in YTD basis. This means that only the PL account code which is 600000x series need to be calculated to YTD values. The desired output is below:


Account   | Organization    | Period   | Year | Amount 
------------------------------------------------- 
6000001    | PM1             | 06       | 2011 | 100
6000002    | PM1             | 06       | 2011 | 250
6000001    | PM1             | 09       | 2011 | 500
6000002    | PM1             | 09       | 2011 | 750
6000001    | PM2             | 06       | 2011 | 150
6000002    | PM2             | 06       | 2011 | 230
6000001    | PM2             | 09       | 2011 | 480
6000002    | PM2             | 09       | 2011 | 720
5000001    | PM1             | 06       | 2011 | 100
5000002    | PM1             | 06       | 2011 | 250
5000001    | PM1             | 09       | 2011 | 400
5000002    | PM1             | 09       | 2011 | 500

Please advise how can I achieve that? Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-13 : 23:58:18

[code]
select a.Account, a.Organization, a.Period, a.Year,
case when a.Account like '600000%' then b.Amount else a.Amount end
from acc a
outer apply
(
select Amount = sum(Amount)
from acc x
where x.Account = a.Account
and x.Organization = a.Organization
and (x.Year * 100) + x.Period <= (a.Year * 100) + a.Period
) b
[/code]

you should really considering changing the design. Don't store MTD & YTD data on the same table

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-14 : 00:06:43

[code]
SELECT t.Account,t.Organization,t.Period,t.Year,
CASE WHEN t.Account -5000000 < 1000000 THEN Amount ELSE t1.RunAmt END AS Amount
FROM table t
CROSS APPLY (SELECT SUM(Amount) AS RunAmt
FROM table
WHERE Account=t.Account
AND Year=t.Year
AND Organization = t.Organization
AND Period <= t.Period
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yingchai
Starting Member

33 Posts

Posted - 2012-03-14 : 01:35:28

Hi visakh16,

What does this condition means?
---
CASE WHEN t.Account -5000000 < 1000000
---

Actually, from my fact table, it has a column called 'acc_type' which stores the values (asset, equity, liability, income, expense).
The 6000000x series accounts is the 'income' and 'expense' acc_type value.
I would think that it would be more of a better way if the calculation of YTD values is based on acc_type attribute?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-14 : 01:51:31

quote:
I would think that it would be more of a better way if the calculation of YTD values is based on acc_type attribute?

of-course. Just change the checking accordingly

KH
[spoiler]Time is always against us[/spoiler]

yingchai
Starting Member

33 Posts

Posted - 2012-03-21 : 06:37:21

quote:

Originally posted by visakh16


SELECT t.Account,t.Organization,t.Period,t.Year,
CASE WHEN t.Account -5000000 < 1000000  THEN Amount ELSE t1.RunAmt END AS Amount
FROM table t
CROSS APPLY (SELECT SUM(Amount) AS RunAmt
             FROM table
             WHERE Account=t.Account
             AND Year=t.Year
             AND Organization = t.Organization    
             AND Period <= t.Period   
            )t1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi visakh16,

I had tried apply your code but it does not work. The amount return is zero. Currently, my fact table only contains period 06, 09 and 12 data.

Below is my code. Appreciate if you can highlight me what's went wrong. Thanks.


SELECT t.submission, t.Account, t.Organization, t.Period, t.Year, t.data_type, t.data_view, t.subgroup, t.currency,
CASE WHEN t.Account not like '6%'  THEN amount ELSE t1.RunAmt END AS Amount
FROM financial_datas t
CROSS APPLY (SELECT SUM(Amount) AS RunAmt
             FROM financial_datas
             WHERE Account = t.Account
             AND Year = t.Year
             AND Organization = t.Organization 
             AND submission = t.submission
             AND data_type = t.data_type
             AND data_view = t.data_view
             AND subgroup = t.subgroup
             AND currency = t.currency   
             AND Period <= t.Period   
            )t1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 10:19:28

you've lot of other conditions also added which i dont even have an idea of and you've never specified them so far also
so post the data for all those fields and tell what output you want out of them and i can help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yingchai
Starting Member

33 Posts

Posted - 2012-03-21 : 22:34:36

Hi visakh16,

I am able to get the desired results after removing this two conditions:
AND subgroup = t.subgroup
AND currency = t.currency

Your query really works. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources