stephenejones1960 (4/19/2014)
Thanks for the code. I just have a general question: Does someone know how to change the code so that the five items can be multiplied, instead of added? Such a script could be used to calculate running total returns.
Multiplying values is not a straight forward function in sql server, hence it has to be a different work around. One alternative can be using a pivot function. Please find the query for it:
SELECT distinct p.id, max(p.[1]) * max(p.[2]) * max(p.[3]) * max(p.[4]) * max(p.[5])
FROM (SELECT A.ID AS ID,B.ID AS BID, B.Balance ,ROW_NUMBER() OVER(Partition by a.id order by b.id) R
FROM Accounts A
cross JOIN Accounts B
WHERE B.ID BETWEEN A.ID-4
AND A.ID AND A.ID>4
)T
pivot (max(balance) for r in ([1],[2],[3],[4],[5]))P
group by p.id
Let me know if it helps.
--Divya