asked
how to calculate columns value into the new column
i made a select query which shows following output as shown in picture ![alt text][1] now i want to add one more column in this query to show current bags and Bags in these 2 columns i want to show calculation like in first rows currentbags column (Receivedbags-DeleiveredBags) and in currentWeight column RecivedWeight+loss-gain-Deliverdweight) which is 1400 and 697.5 after that in secound row i want to add frist rows currentbags value+ second rows (Receivedbags-DeleiveredBags) and same in weight like daily stock register so output looked like below image ![alt text][2] there is one more column commondate on which i want to make calculation and want output [1]: /storage/temp/2569-demo1.jpg [2]: /storage/temp/2570-demo2.jpg
Your output dataset is wrong. You need to watch status based on date, so you need first group data by date and sum values, and after that calculate this new column ordered by date. In this picture you summed up 14-01-2015 and 08-01-2015 to get new value, but this is not good. Use my example below and create your query.
Use this example and create you queries: CREATE TABLE #RunTotalTestData ( id int not null identity(1,1) primary key, value1 int not null, value2 int not null ); INSERT INTO #RunTotalTestData (value1,value2) VALUES (10,0); INSERT INTO #RunTotalTestData (value1,value2) VALUES (30,0); INSERT INTO #RunTotalTestData (value1,value2) VALUES (15,0); INSERT INTO #RunTotalTestData (value1,value2) VALUES (10,0); INSERT INTO #RunTotalTestData (value1,value2) VALUES (15,10); INSERT INTO #RunTotalTestData (value1,value2) VALUES (50,20); INSERT INTO #RunTotalTestData (value1,value2) VALUES (100,30); INSERT INTO #RunTotalTestData (value1,value2) VALUES (150,50); INSERT INTO #RunTotalTestData (value1,value2) VALUES (0,10); INSERT INTO #RunTotalTestData (value1,value2) VALUES (0,0); INSERT INTO #RunTotalTestData (value1,value2) VALUES (0,0); INSERT INTO #RunTotalTestData (value1,value2) VALUES (30,10); INSERT INTO #RunTotalTestData (value1,value2) VALUES (15,10); INSERT INTO #RunTotalTestData (value1,value2) VALUES (10,0); SELECT * FROM #RunTotalTestData CREATE table #Current ( id int, RunningTotalValue1 int, RunningTotalValue2 int ) --Calculating running totals SELECT
a.id, a.value1, value2, (SELECT SUM(b.value1) FROM #RunTotalTestData b WHERE
b.id <=
a.id) AS RunningTotalsValue1, (SELECT SUM(b.value2) FROM #RunTotalTestData b WHERE
b.id <=
a.id) AS RunningTotalsValue2 FROM #RunTotalTestData a ORDER BY
a.id; --preparing data from column calculation INSERT INTO #Current SELECT
a.id,(SELECT SUM(b.value1) FROM #RunTotalTestData b WHERE
b.id <=
a.id) AS RunningTotalsValue1, (SELECT SUM(b.value2) FROM #RunTotalTestData b WHERE
b.id <=
a.id) AS RunningTotalsValue2 FROM #RunTotalTestData a ORDER BY
a.id; --Final data set: SELECT r.*,c.RunningTotalValue1,c.RunningTotalValue2,RunningTotalValue1 - RunningTotalValue2 AS CurrentValue FROM #Current c INNER JOIN #RunTotalTestData r ON
c.id =
r.ID DROP TABLE #RunTotalTestData DROP TABLE #Current
16 People are following this question.
