Query help; - SQL Server Forums

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/

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/

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/

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources