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_pricesSELECT 'a','2014-08-01 00:00:00.000',1000.12 UNION ALLSELECT 'a','2014-08-02 00:00:00.000',1030 UNION ALLSELECT 'a','2014-08-03 00:00:00.000',1050 UNION ALLSELECT 'a','2014-08-04 00:00:00.000',1060 UNION ALLSELECT 'a','2014-08-05 00:00:00.000',1050 UNION ALLSELECT 'b','2014-08-01 00:00:00.000',100 UNION ALLSELECT 'b','2014-08-02 00:00:00.000',123 UNION ALLSELECT 'b','2014-08-03 00:00:00.000',105 UNION ALLSELECT 'b','2014-08-04 00:00:00.000',108 UNION ALLSELECT 'b','2014-08-05 00:00:00.000',113SELECT 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 diferenciafrom @stock_prices |
|
Chris_Kelley
Posting Yak Master
114 Posts |
Posted - 2014-08-09 : 12:54:21
|
c |
|
|
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:NULL2.987641483022031.941747572815530.95238095238095-0.94339622641509And I need an additional column where I can accumulate those values.It would look like:null2.987641483022032.98764148302203 + 1.941747572815532.98764148302203 + 1.94174757281553 + 1.941747572815532.98764148302203 + 1.94174757281553 + 1.94174757281553 + -0.94339622641509cheers,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
|
c |
|
|
Chris_Kelley
Posting Yak Master
114 Posts |
Posted - 2014-08-09 : 18:18:11
|
c |
|
|
Chris_Kelley
Posting Yak Master
114 Posts |
Posted - 2014-08-09 : 18:20:59
|
c |
|
|
jcarrallo
Starting Member
35 Posts |
Posted - 2014-08-13 : 13:35:07
|
Hi Chris and SwePeso,This is exactly what I was looking for.Many thanks for sharing your expertise.Best,Jay |
|
|
|
|
|