May 7, 2009 at 9:12 am
Hi there,
I need to loop thru a number of records in which if there is a duplicate value in one column, I need to display an acculmative value. So as an example...
DECLARE @tbl TABLE
(
id INT IDENTITY(1,1),
cusip VARCHAR(10),
trdate DATETIME,
trvalue FLOAT
)
INSERT @tbl
SELECT '665278404','06/02/2008',100 UNION ALL
SELECT '665278404','06/15/2008',100 UNION ALL
SELECT '665278404','06/25/2008',400 UNION ALL
SELECT '665278404','06/29/2008',700 UNION ALL
SELECT '123456789','06/30/2008',500
I need to get back:
'665278404','06/02/2008',100
'665278404','06/15/2008',200
'665278404','06/25/2008',600
'665278404','06/29/2008',1300
'123456789','06/30/2008',500
damned if I can think of a way to do it without keeping @variables floating around in some sort of loop.
thanks!
May 7, 2009 at 10:16 am
Jeff Moden wrote an article which covered this.
(Whether you want to rely on non-relational features is up to you.)
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
If there are not too many trdates for each cusip, I would be inclined to try something like:
SELECT T1.cusip, T1.trdate
,(
SELECT SUM(T2.trvalue)
FROM @tbl T2
WHERE T2.cusip = T1.cusip
AND T2.trdate <= T1.trdate
) AS trvalue
FROM @tbl T1
(In SQL2005 the ROW_NUMBER function can be used to speed this up.)
May 7, 2009 at 11:14 am
thanks, I'll check out the link.
May 7, 2009 at 12:24 pm
Christopher
Note that Jeff Moden is rewriting the article and the original is not available.... HOWEVER if you scroll down to the bottom of what is there you will find a link to the older SPs that Jeff had included in the original article... they are well commented and self explanatory...and applicable to SQL 2000
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply