Help with Query - how to accumulate a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Query - how to accumulate a column

Author

Topic

jcarrallo
Starting Member

35 Posts

Posted - 2014-08-09 : 12:01:04

Hello,
I would like to accumulate the 'diferencia' column by 'codigo'.

Can I do this?
Many thanks,
jay
__________________________

DECLARE @stock_prices TABLE
(codigo char(7)
,fecha date
, precio decimal(10,3)
)

INSERT INTO @Stock_prices
SELECT 'a','2014-08-01 00:00:00.000',1000.12 UNION ALL
SELECT 'a','2014-08-02 00:00:00.000',1030 UNION ALL
SELECT 'a','2014-08-03 00:00:00.000',1050 UNION ALL
SELECT 'a','2014-08-04 00:00:00.000',1060 UNION ALL
SELECT 'a','2014-08-05 00:00:00.000',1050 UNION ALL
SELECT 'b','2014-08-01 00:00:00.000',100 UNION ALL
SELECT 'b','2014-08-02 00:00:00.000',123 UNION ALL
SELECT 'b','2014-08-03 00:00:00.000',105 UNION ALL
SELECT 'b','2014-08-04 00:00:00.000',108 UNION ALL
SELECT 'b','2014-08-05 00:00:00.000',113

SELECT codigo, fecha, precio,LAG(precio,1,0) OVER (PARTITION BY codigo ORDER BY fecha) AS Precio_anterior, (precio - LAG(precio,1,0) OVER (PARTITION BY codigo ORDER BY fecha))*100/nullif(LAG(precio,1,0) OVER (PARTITION BY codigo ORDER BY fecha),0) as diferencia
from @stock_prices

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-08-09 : 12:54:21

jcarrallo
Starting Member

35 Posts

Posted - 2014-08-09 : 15:50:06

Hi Chris,
Thanks for helping.
Could you ellaborate?

Let me give you an example. If you run the query you will get a column called diferencia with the following values:

NULL
2.98764148302203
1.94174757281553
0.95238095238095
-0.94339622641509

And I need an additional column where I can accumulate those values.
It would look like:
null
2.98764148302203
2.98764148302203 + 1.94174757281553
2.98764148302203 + 1.94174757281553 + 1.94174757281553
2.98764148302203 + 1.94174757281553 + 1.94174757281553 + -0.94339622641509

cheers,
jay

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-09 : 17:34:59

[code]SUM(Precio) OVER (PARTITION BY Codigo ORDER BY Fecha ROWS UNBOUNDED PRECEDING AND CURRENT ROW)[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-08-09 : 17:59:53

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-08-09 : 18:18:11

Chris_Kelley
Posting Yak Master