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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query help;
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

egemen_ates
Yak Posting Veteran

Turkey
76 Posts

Posted - 03/01/2013 :  05:28:24  Show Profile  Reply with Quote
my query is ;
SELECT SUM(STOCK_IN - STOCK_OUT) AS AMOUNT ,PRODUCT_ID,PROCESS_DATE FROM STOCKS_ROW GROUP BY PRODUCT_ID,PROCESS_DATE HAVING PRODUCT_ID= 484

AMOUNT PRODUCT_ID PROCESS_DATE
-9 484 2013-01-04 00:00:00.000
-3 484 2013-01-10 00:00:00.000
-219 484 2013-02-06 00:00:00.000
20 484 2013-02-07 00:00:00.000
-5,4 484 2013-02-11 00:00:00.000
5 484 2013-02-21 00:00:00.000


How can i calculate "calculate_column" usin query ?

AMOUNT PRODUCT_ID PROCESS_DATE CALCULATE_COLUMN(amount+amount)
-9 484 2013-01-04 00:00:00.000 -9
-3 484 2013-01-10 00:00:00.000 -12
-219 484 2013-02-06 00:00:00.000 -231
20 484 2013-02-07 00:00:00.000 -211
-5,4 484 2013-02-11 00:00:00.000 -216,4
5 484 2013-02-21 00:00:00.000 -211,4


THANKS FOR ANSWERS

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/01/2013 :  05:38:23  Show Profile  Reply with Quote

SELECT SUM(STOCK_IN - STOCK_OUT) AS AMOUNT ,PRODUCT_ID,PROCESS_DATE,SummAcc
FROM (SELECT *,SUM(STOCK_IN - STOCK_OUT) OVER (PARTITION BY PRODUCT_ID ORDER BY PROCESS_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SummAcc
FROM STOCKS_ROW)t
GROUP BY PRODUCT_ID,PROCESS_DATE,SummAcc HAVING PRODUCT_ID= 484



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

egemen_ates
Yak Posting Veteran

Turkey
76 Posts

Posted - 03/01/2013 :  07:01:14  Show Profile  Reply with Quote
THANK YOU VERY VERY MUCH visakh16,I see this code firstly.this code run sql server 2008 ?

quote:
Originally posted by visakh16


SELECT SUM(STOCK_IN - STOCK_OUT) AS AMOUNT ,PRODUCT_ID,PROCESS_DATE,SummAcc
FROM (SELECT *,SUM(STOCK_IN - STOCK_OUT) OVER (PARTITION BY PRODUCT_ID ORDER BY PROCESS_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SummAcc
FROM STOCKS_ROW)t
GROUP BY PRODUCT_ID,PROCESS_DATE,SummAcc HAVING PRODUCT_ID= 484



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/01/2013 :  07:10:51  Show Profile  Reply with Quote
nope...it will run only in sql 2012

you posted this in 2012 forum thats why i gave 2012 based solution.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 03/01/2013 :  07:13:31  Show Profile  Reply with Quote
in 2008 this will work

;With CTE
AS
(
SELECT SUM(STOCK_IN - STOCK_OUT) AS AMOUNT ,PRODUCT_ID,PROCESS_DATE 
FROM STOCKS_ROW 
GROUP BY PRODUCT_ID,PROCESS_DATE 
HAVING PRODUCT_ID= 484
)

SELECT *
FROM CTE c1
CROSS APPLY (SELECT SUM(AMOUNT) AS CUMMAMOUNT
             FROM CTE
             WHERE PRODUCT_ID = c1.PRODUCT_ID	
             AND PROCESS_DATE < = c1.PROCESS_DATE
            )c2


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next 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.05 seconds. Powered By: Snitz Forums 2000