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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query help;
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

egemen_ates
Yak Posting Veteran

Turkey
74 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
52249 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
74 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
52249 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
52249 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  
 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.05 seconds. Powered By: Snitz Forums 2000