June 17, 2009 at 3:08 am
Hello all,
I have the following problem:
In my report i have a table that list results grouped by week.
I would like to copy, the total value to the next line in the preftotal column.
example:
weeknbr prefTotal value1 value2 value3 total
01 15 20 30 65
02 65 10 -10 5 70
03 70 20 15 10 115
04 115 ....
As the table is dynamic (based on the availlable weeks) i don't really know how to
handle this one.
Thanks in advance for your reply's
Vincent
June 17, 2009 at 3:26 am
Hi Vincent
I think the prefTotal value would have to be calculated in the t-sql that returns your dataset. I am not a fan of inline select statements but this is an example of how you could use t-sql to generate the values:
SELECT
a.weeknbr
, (SELECT SUM(value1, value2, value3) FROM tablename where weeknbr = (a.weeknbr - 1) )[prefTotal]
, a.value1
, a.value2
, a.value3
, sum(a.value1, a.value2, a.value3)[total]
FROM tablename a
GROUP BY a.weeknbr, a.value1, a.value2, a.value3
Another option is to use a stored proc to create the dataset. I would prefer this method if the dataset is really big as inline select statements can be detrimental to processing time.
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
June 17, 2009 at 3:34 am
Hello William,
A simple solution... But it works great.
Thanks a lot
V.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply