March 13, 2012 at 9:11 pm
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!
March 13, 2012 at 11:27 pm
I'm sure someone is going to ask you to post your DDL, so I'll do it for you.
DECLARE @acct TABLE
(Account INT, Organization CHAR(3), Period CHAR(2), [Year] CHAR(4), Amount MONEY)
INSERT INTO @acct
SELECT 6000001 As Account, 'PM1' As Organization, '06' As Period, '2011' As [Year], 100 As Amount
UNION ALL SELECT 6000002, 'PM1','06','2011',250
UNION ALL SELECT 6000001, 'PM1','09','2011',400
UNION ALL SELECT 6000002, 'PM1','09','2011',500
UNION ALL SELECT 6000001, 'PM2','06','2011',150
UNION ALL SELECT 6000002, 'PM2','06','2011',230
UNION ALL SELECT 6000001, 'PM2','09','2011',330
UNION ALL SELECT 6000002, 'PM2','09','2011',490
UNION ALL SELECT 5000001, 'PM1','06','2011',100
UNION ALL SELECT 5000002, 'PM1','06','2011',250
UNION ALL SELECT 5000001, 'PM1','09','2011',400
UNION ALL SELECT 5000002, 'PM1','09','2011',500
My question is, for the P&L accounts shouldn't they be showing a YTD amount for the periods that are missing for example 01-05, 07, 08?
So your expected results may be different that what you have posted.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 14, 2012 at 9:35 am
Joe,
I believe the OP needs a view that shows both PL and BS amounts in YTD, but the calculation is only needed for the PL amounts. Also, the OP posted in a 2008 forum so posting a solution using functionality available to the OP is no help whatsoever.
I think something like this works, but likely won't scale that well. I'm sure someone else can come up with something better:
DECLARE @acct TABLE
(
Account INT,
Organization CHAR(3),
Period CHAR(2),
[Year] CHAR(4),
Amount MONEY
)
INSERT INTO @acct
SELECT
6000001 AS Account,
'PM1' AS Organization,
'06' AS Period,
'2011' AS [Year],
100 AS Amount
UNION ALL
SELECT
6000002,
'PM1',
'06',
'2011',
250
UNION ALL
SELECT
6000001,
'PM1',
'09',
'2011',
400
UNION ALL
SELECT
6000002,
'PM1',
'09',
'2011',
500
UNION ALL
SELECT
6000001,
'PM2',
'06',
'2011',
150
UNION ALL
SELECT
6000002,
'PM2',
'06',
'2011',
230
UNION ALL
SELECT
6000001,
'PM2',
'09',
'2011',
330
UNION ALL
SELECT
6000002,
'PM2',
'09',
'2011',
490
UNION ALL
SELECT
5000001,
'PM1',
'06',
'2011',
100
UNION ALL
SELECT
5000002,
'PM1',
'06',
'2011',
250
UNION ALL
SELECT
5000001,
'PM1',
'09',
'2011',
400
UNION ALL
SELECT
5000002,
'PM1',
'09',
'2011',
500;
WITH accounts
AS (
SELECT
A.Account,
A.Organization,
A.Period,
A.Year,
A.Amount,
CASE WHEN A.Account LIKE '6%'
THEN ROW_NUMBER() OVER (PARTITION BY A.Account,
A.Organization, A.Year ORDER BY A.Account, A.Organization, A.year, A.Period)
ELSE 1
END AS row_no
FROM
@acct AS A
),
accountsYTD
AS (
SELECT
accounts.Account,
accounts.Organization,
accounts.Period,
accounts.Year,
accounts.Amount AS YTD,
accounts.row_no
FROM
accounts
WHERE
accounts.row_no = 1
UNION ALL
SELECT
A.Account,
A.Organization,
A.Period,
A.Year,
A.YTD + B.amount,
B.row_no + 1
FROM
accountsYTD AS A
JOIN accounts AS B
ON A.Account = B.Account AND
A.Organization = B.Organization AND
A.Year = B.Year AND
A.row_no < B.row_no
)
SELECT
Account,
organization,
period,
YEAR,
YTD
FROM
accountsYTD AS A
ORDER BY
account,
organization,
YEAR,
period;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 15, 2012 at 12:28 am
Here's another solution:
;WITH PLYTD AS (
SELECT Account, Organization, Period, [Year]
,(SELECT SUM(Amount) FROM @acct a2
WHERE a1.Account = a2.Account and a1.Organization = a2.Organization and a1.[Year] = a2.[Year] and
a2.Period <= a1.Period
) As Amount
FROM @acct a1
)
SELECT Account, Organization, Period, [Year], Amount
FROM PLYTD
WHERE Account >= 6000000
UNION ALL
SELECT Account, Organization, Period, [Year], Amount
FROM @acct
WHERE Account < 6000000
This solution should generate exactly the results set requested.
I'm still troubled by the lack of a response to my earlier question. I've worked on GL systems before. The fact that the P&L accounts are missing values for missing periods (for example 07 and 08) when they should be carrying forward the YTD amount just seems wrong to me.
Also, for the Balance Sheet accounts, normally these are not "YTD" they are Life to Date. They also should carry forward the previous month's value in months where there are no impacting transactions to change them. For example, if an Asset account has a value of 1000 in Feb, if no assets are purchased or sold in Mar, the ending balance in Mar is still 1000.
Generating the missing months would be slightly more complicated than the query I posted above but possible.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 15, 2012 at 8:44 am
dwain.c (3/15/2012)
Here's another solution:
;WITH PLYTD AS (SELECT Account, Organization, Period, [Year]
,(SELECT SUM(Amount) FROM @acct a2
WHERE a1.Account = a2.Account and a1.Organization = a2.Organization and a1.[Year] = a2.[Year] and
a2.Period <= a1.Period
) As Amount
FROM @acct a1
)
SELECT Account, Organization, Period, [Year], Amount
FROM PLYTD
WHERE Account >= 6000000
UNION ALL
SELECT Account, Organization, Period, [Year], Amount
FROM @acct
WHERE Account < 6000000
This solution should generate exactly the results set requested.
I'm still troubled by the lack of a response to my earlier question. I've worked on GL systems before. The fact that the P&L accounts are missing values for missing periods (for example 07 and 08) when they should be carrying forward the YTD amount just seems wrong to me.
Also, for the Balance Sheet accounts, normally these are not "YTD" they are Life to Date. They also should carry forward the previous month's value in months where there are no impacting transactions to change them. For example, if an Asset account has a value of 1000 in Feb, if no assets are purchased or sold in Mar, the ending balance in Mar is still 1000.
Generating the missing months would be slightly more complicated than the query I posted above but possible.
Sorry for the late response as I'm been busy lately.
Currently, we are still testing the system by loading quarterly data (which is why you only see period 06 and 09). When system goes live, you won't see the missing periods anymore as it will be updated monthly basis.
March 15, 2012 at 6:20 pm
Ah so then every account code will have a transaction in every period?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 15, 2012 at 8:08 pm
dwain.c (3/15/2012)
Ah so then every account code will have a transaction in every period?
Yes. Every account code will have transactions in every period. The differences is that BS account will be loaded as YTD every month while the PL account will load in as periodic every month.
Example, if Asset for Mar is 1000, and no data is loaded on Apr, then it will treat it as no data on Apr. So, the user is required to load BS and PL data every month regardless of any changes.
March 16, 2012 at 7:45 am
dwain.c (3/15/2012)
Here's another solution:
;WITH PLYTD AS (SELECT Account, Organization, Period, [Year]
,(SELECT SUM(Amount) FROM @acct a2
WHERE a1.Account = a2.Account and a1.Organization = a2.Organization and a1.[Year] = a2.[Year] and
a2.Period <= a1.Period
) As Amount
FROM @acct a1
)
SELECT Account, Organization, Period, [Year], Amount
FROM PLYTD
WHERE Account >= 6000000
UNION ALL
SELECT Account, Organization, Period, [Year], Amount
FROM @acct
WHERE Account < 6000000
This solution should generate exactly the results set requested.
I'm still troubled by the lack of a response to my earlier question. I've worked on GL systems before. The fact that the P&L accounts are missing values for missing periods (for example 07 and 08) when they should be carrying forward the YTD amount just seems wrong to me.
Also, for the Balance Sheet accounts, normally these are not "YTD" they are Life to Date. They also should carry forward the previous month's value in months where there are no impacting transactions to change them. For example, if an Asset account has a value of 1000 in Feb, if no assets are purchased or sold in Mar, the ending balance in Mar is still 1000.
Generating the missing months would be slightly more complicated than the query I posted above but possible.
Hi Dwain,
You might want to check out the "Hidden RBAR" you have in that code in the following article.
http://www.sqlservercentral.com/articles/T-SQL/61539/
--Jeff Moden
Change is inevitable... Change for the better is not.
March 18, 2012 at 6:23 pm
Jeff,
That's a very interesting article and it has described what I could not articulate in my dislike for correlated subqueries. I intrinsically knew that there would be a performance hit across the SUM but I never knew precisely how to describe it.
In the article though, you never really mentioned an alternative that is better performing. Any suggestions for a general technique to avoid this?
I can come up with two alternative methods:
;WITH PLYTD AS (
SELECT a1.Account, a1.Organization, a1.Period, a1.[Year], SUM(a2.Amount) As Amount
FROM @acct a1
INNER JOIN @acct a2
ON a1.Account = a2.Account and a1.Organization = a2.Organization and
a1.[Year] = a2.[Year] and a2.Period <= a1.Period
WHERE a1.Account >= 6000000
GROUP BY a1.Account, a1.Organization, a1.Period, a1.[Year]
)
SELECT Account, Organization, Period, [Year], Amount
FROM PLYTD
UNION ALL
SELECT Account, Organization, Period, [Year], Amount
FROM @acct
WHERE Account < 6000000
;WITH PLYTD AS (
SELECT Account, Organization, Period, [Year]
,SUM(Amount) OVER (PARTITION BY Account, Organization, Period, [Year]) as Amount
FROM @acct
WHERE Account >= 6000000
)
SELECT Account, Organization, Period, [Year], Amount
FROM PLYTD
UNION ALL
SELECT Account, Organization, Period, [Year], Amount
FROM @acct
WHERE Account < 6000000
When I look at the execution plans of the 3 solutions I've posted they are (in order): 17%/36%/30% so the first looks the best. But I seem to recall something (possibly) you said once about the subquery only seeing the first row. Is that the case here?
What you may be saying is that, one of my last two results (or your general solution) may scale up better.
I am honestly not trying to second guess you. Just trying to learn so I can live to my mantra 🙂
Dwain
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 19, 2012 at 9:05 am
dwain.c (3/18/2012)
Jeff,That's a very interesting article and it has described what I could not articulate in my dislike for correlated subqueries. I intrinsically knew that there would be a performance hit across the SUM but I never knew precisely how to describe it.
In the article though, you never really mentioned an alternative that is better performing. Any suggestions for a general technique to avoid this?
Just to be sure, it's not correlated subqueries that are the problem. In fact, CROSS APPLY and OUTER APPLY aren't much more than correlated subqueries. Correlated subqeries based on equalities can be fast even if they have a SUM in them (although you still need to be careful there).
Correlated subqueries, including the two forms of APPLY suffer the Triangular Join problem only when there are in-equality operators present and particularly when such operators are accompanied by aggreagates.
There are two works arounds for the problem. A well written cursor or While loop is one work around (of course, I don't use that method but it is a supported method). The other relies on the "Pseudo-Cursor" contained in an update clause that uses 3 part updates and is affectionately referred to as the "Quirky Update". It's called "Quirky" because there are some pretty strict rules to follow to use it. None of the rules are insane or difficult to remember but you really shouldn't skip any of them. It's also not an MS supported method but I've been using it for I don't know how many years now.
Here's the link. Make sure you read the "rewrite" notice at the top for additional information on an improvement that Paul White and Tom Thompson came up with. And, yeah... eventually I'll finish the rewrite.
http://www.sqlservercentral.com/articles/T-SQL/68467/
--Jeff Moden
Change is inevitable... Change for the better is not.
March 19, 2012 at 8:10 pm
Jeff,
That's a great article even if it is a bit "techno."
I've used the quirky update for other purposes although not against a prod table (just temp or table variables) and never knew about the effect of clustered indexes on it, and I consider that a potential gotcha that I'll need to carefully watch for.
I'm wondering if the new SQL 2012 functions (LEAD and LAG?) offer a more promising approach to what seems to me to be a pretty basic problem and whether they'd also be affected by indexing.
Alas, for those not so technically inclined I think the triangular join is going to remain the chosen approach without regard to performance issues (not me, I didn't mean me! :-D).
Certainly this was educational for me. Thanks.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply