| Author |
 Topic  |
|
|
yingchai
Starting Member
33 Posts |
 Posted - 03/13/2012 : 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! |
Edited by - yingchai on 03/13/2012 23:46:01
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 03/13/2012 : 23:58:18
|
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
you should really considering changing the design. Don't store MTD & YTD data on the same table
KH
Time is always against us
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/14/2012 : 00:06:43
|
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/
|
|
|
|
yingchai
Starting Member
33 Posts |
Posted - 03/14/2012 : 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)
Singapore
16745 Posts |
Posted - 03/14/2012 : 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
Time is always against us
|
|
|
|
yingchai
Starting Member
33 Posts |
Posted - 03/21/2012 : 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
India
47023 Posts |
Posted - 03/21/2012 : 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/
|
Edited by - visakh16 on 03/21/2012 10:19:55 |
|
|
|
yingchai
Starting Member
33 Posts |
Posted - 03/21/2012 : 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! |
|
|
| |
Topic |
|
Sum up certain accounts accross period
