SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with Query - how to accumulate a column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jcarrallo
Starting Member

Spain
35 Posts

Posted - 08/09/2014 :  12:01:04  Show Profile  Reply with Quote
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 - 08/09/2014 :  12:54:21  Show Profile  Reply with Quote
c

Edited by - Chris_Kelley on 11/11/2014 19:32:58
Go to Top of Page

jcarrallo
Starting Member

Spain
35 Posts

Posted - 08/09/2014 :  15:50:06  Show Profile  Reply with Quote
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

Edited by - jcarrallo on 08/09/2014 16:18:55
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 08/09/2014 :  17:34:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SUM(Precio) OVER (PARTITION BY Codigo ORDER BY Fecha ROWS UNBOUNDED PRECEDING AND CURRENT ROW)



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 08/09/2014 :  17:59:53  Show Profile  Reply with Quote
c

Edited by - Chris_Kelley on 11/11/2014 19:33:07
Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 08/09/2014 :  18:18:11  Show Profile  Reply with Quote
c

Edited by - Chris_Kelley on 11/11/2014 19:33:15
Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 08/09/2014 :  18:20:59  Show Profile  Reply with Quote
c

Edited by - Chris_Kelley on 11/11/2014 19:33:25
Go to Top of Page

jcarrallo
Starting Member

Spain
35 Posts

Posted - 08/13/2014 :  13:35:07  Show Profile  Reply with Quote
Hi Chris and SwePeso,
This is exactly what I was looking for.
Many thanks for sharing your expertise.
Best,
Jay
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000