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
31 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

106 Posts

Posted - 08/09/2014 :  12:54:21  Show Profile  Reply with Quote
I am new to coding, so first thank you for what I about to research (rownum, lag, partitian(I am accustomed to GROUP BY since I havent used rownum any)

I found this --
--------------
A group by normally reduces the number of rows returned by rolling them up and calculating averages or sums for each row. partition by does not affect the number of rows returned, but it changes how a window function's result is calculated.
-----------

maybe a GROUP BY and SUM as the aggregate?

Thanks,
Chris
Jr Programmer
Go to Top of Page

jcarrallo
Starting Member

Spain
31 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
30265 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

106 Posts

Posted - 08/09/2014 :  17:59:53  Show Profile  Reply with Quote
Hey Jay,

I checked the math and it seems to work, here is what I did --

-------------------------------------
--temp table creation
-------------------------------------
CREATE TABLE #JCARRALLO
(ID INT,DIFERENCIA FLOAT)
INSERT INTO #JCARRALLO (ID,DIFERENCIA)
VALUES (1,NULL),(2,2.98764148302203),(3,1.94174757281553),(4,0.95238095238095),(5,-0.94339622641509)

--------------------------------------
--STILL WORKING ON IT I'M AFRAID, BUT SO FAR
--------------------------------------
SELECT ID, DIFERENCIA = SUM(DIFERENCIA) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)+1
FROM #JCARRALLO

--------------------
--Results
--------------------
ID DIFERENCIA
1 NULL
2 3.98764148302203
3 5.92938905583756
4 6.88177000821851
5 5.93837378180342

--------------------------------------
--AND
--------------------------------------
SELECT ID, DIFERENCIA = SUM(DIFERENCIA) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM #JCARRALLO

-----------------------------
--RESULTS
-----------------------------

ID DIFERENCIA
1 NULL
2 2.98764148302203
3 4.92938905583756
4 5.88177000821851
5 4.93837378180342



JUST TRYING TO GET THE BOTTOM 1,2,3 RESULTS AND THE TOP 4,5 TOGETHER, SORRY COMPLETE NEWBIE




Thanks,
Chris
Jr Programmer
Go to Top of Page

Chris_Kelley
Posting Yak Master

106 Posts

Posted - 08/09/2014 :  18:18:11  Show Profile  Reply with Quote
Hey Jay,

Reviewing your post, while setting up a cursor I saw an error, I believe

should it be --
1 = null
2 = 2.98764148302203
3 = 2.98764148302203 + 1.94174757281553
or [4.92938905583756]
4 = 2.98764148302203 + 1.94174757281553 + 0.95238095238095
or [5.88177000821851]
5 = 2.98764148302203 + 1.94174757281553 + 0.95238095238095 + -0.94339622641509
or [4.93837378180342]
????????????

If so --
--------------------
SELECT ID, DIFERENCIA = SUM(DIFERENCIA) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM #JCARRALLO
--===========================
--Results
--===========================
ID DIFERENCIA
1 NULL
2 2.98764148302203
3 4.92938905583756
4 5.88177000821851
5 4.93837378180342



Thanks,
Chris
Jr Programmer
Go to Top of Page

Chris_Kelley
Posting Yak Master

106 Posts

Posted - 08/09/2014 :  18:20:59  Show Profile  Reply with Quote
AWW nuts, WTG SwePeso.....I am too slow, good job

Thanks,
Chris
Jr Programmer
Go to Top of Page

jcarrallo
Starting Member

Spain
31 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