November 4, 2011 at 6:25 am
Hi,
I'm struggeling to create a view that will return YTD values.
Basicly I have a huge Postings table (that holds every transaction).
I have a view that will group these postings into a monhtly sum for each specific account.
Returning:
Year - Month - Account - Amount - ++
Now I would like to add a YTD column at the end, or if its better I could replace the Amount column with YTD column.
The YTD column should return the sum-total for that row from January that year until the month of the row.
So lets say we have this row:
2010 - 2 - 22 - 5678,99
2010 - 1 - 22 - 123,00
The YTD column for the january row should have the same value as the amount column.
The YTD column for february should have 123 + 5678,99 in the YTD column.
Is this something that could be easily implemented?
November 4, 2011 at 7:05 am
There are several ways to generate running totals like that.
Often, it's best to do so in the presentation layer of the application, rather than in the data layer. What are you using for the front end on this?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2011 at 7:25 am
Due to buisness needs we need to calculate the YTD totals in the view itself. So even though presentation-layer would be the best solution it would not work in this case..
November 4, 2011 at 7:43 am
How would this work in your view?
select p.Year, p.Month, p.Account,p.Amount,
case when Month=(select top 1 Month from Postings order by Month)
then Amount
else (select SUM(Amount) from Postings where Month <= p.Month)
end as YTD
from Postings p
_________________________________
seth delconte
http://sqlkeys.com
November 4, 2011 at 7:55 am
Yes i guess that works in theory. if you also add a where clause on the totals so we only sum the correct accounts)
But problem is that when postings has millions of rows, this query will take a lifetime to run... I did run a similar query against my data some hours ago and it is still running...
November 4, 2011 at 8:07 am
Then add a YTD column to the base table and create insert/update/delete triggers to adjust the YTD data when values are added, updated, and deleted.
_________________________________
seth delconte
http://sqlkeys.com
November 4, 2011 at 8:18 am
You'll want to take a look at this article: http://www.sqlservercentral.com/articles/T-SQL/68467/
Make sure to read through the discussions after you read the article. It's about solving running totals in SQL Server, and it's got a lot of data in it on that subject.
You'll also want to take a look at: http://www.pawlowski.cz/2010/09/sql-server-and-fastest-running-totals-using-clr/
It's about solving running totals in SQL with a CLR object. That's the solution I prefer, if it can't be done in the presentation layer, but some DBAs refuse to allow CLR, so Jeff's data from the first article may be more useful if that's the case.
Edit: Updated URL
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 7, 2011 at 1:29 am
Thanks for your input,
However, i just had an idea.
Why dont we just do a right outher join on distinct months and year? That would give the running totals as pr. month and year right?
Lets say we have two simple tables:
Postings: id - year - month - amount (every transaction. id is departmentId and year month would be date, but for this example lets say they are ints)
Months: year - month - name (just every month for the wanted months)
Then:
SELECT p.id, p.year, cal.month, sum(amount)
FROM Postings p
RIGHT OUTER JOIN Months cal
on cal.year = p.year
and cal.month>=p.month
group by p.id, p.year, cal.month
That seem to work. And a lot more efficient than a cross apply?
November 8, 2011 at 6:24 am
Compare the execution plans for the Cross Apply and the join to a list of months.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2011 at 9:30 pm
seth delconte (11/4/2011)
Then add a YTD column to the base table and create insert/update/delete triggers to adjust the YTD data when values are added, updated, and deleted.
My preference would be to use an indexed view. This has the advantage of automatically maintaining the totals in the same plan that makes the change (triggers execute as a separate statement and you have to be careful to get all the 'delta' logic right, and worry about locking and concurrency issues). For sure, the indexed view cannot use outer joins, but it would be trivial to layer a non-indexed view on top of the indexed view to perform the required outer join to the calendar table.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply