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

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query help;

Author  Topic 

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2013-03-01 : 05:28:24
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

52326 Posts

Posted - 2013-03-01 : 05:38:23
[code]
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
[/code]


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

Go to Top of Page

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2013-03-01 : 07:01:14
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

52326 Posts

Posted - 2013-03-01 : 07:10:51
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

52326 Posts

Posted - 2013-03-01 : 07:13:31
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
   

- Advertisement -