11,813,557 members (45,334 online)
Rate this:
See more:
Hi,

Im having a table with 4 columns as below

Sno Opening Plusvalue MinusValue Closing
1 0 5 2 0
2 0 8 4 0
3 0 8 6 0
4 0 9 7 0

Formula for arriving my closing column is

Closing = Opening+Plusvalue-MinusValue

Formula for arriving my opening column is

Opening = Previous column closing

My required output is

Sno Opening Plusvalue MinusValue Closing
1 0 5 2 3
2 3 8 4 7
3 7 8 6 9
4 9 9 7 11

I need a query to acheive this output
Posted 12-Feb-13 18:18pm

Rate this:

Solution 1

Hi Krithiga,

Check my following Sample TSQL Script.

```-- Table Creation Statement
IF OBJECT_ID('TempDB..#TestSample') IS NOT NULL DROP TABLE #TestSample
CREATE TABLE #TestSample(Sno INT,Opening INT,Plusvalue INT,MinusValue INT,Closing INT)

-- Insert Sample Values
INSERT INTO #TestSample(Sno, Opening, Plusvalue, MinusValue, Closing)
VALUES(1, 0, 5, 2, 0),(2, 0, 8, 4, 0),(3, 0, 8, 6, 0),(4, 0, 9, 7, 0)

-- Given Data
SELECT Sno, Opening, Plusvalue, MinusValue, Closing FROM #TestSample

-- Required Output
SELECT T.Sno, (ISNULL((SELECT SUM((Opening+Plusvalue-MinusValue))
FROM #TestSample
WHERE Sno < T.Sno),0)) 'Opening',
T.Plusvalue, T.MinusValue,
(ISNULL((SELECT SUM((Opening+Plusvalue-MinusValue))
FROM #TestSample
WHERE Sno < T.Sno),0) + T.Plusvalue - T.MinusValue) 'Closing'
FROM #TestSample T
```

Regards,
GVPrabu
Amir Mahfoozi at 13-Feb-13 1:08am

+5, Well done.
kirthiga S at 13-Feb-13 1:57am

Hi Prabhu,

If I have some internal grouping how to proceed the same.
Rate this:

Solution 2

Hi Prabhu,

If I have some internal grouping how to proceed the same.

For Example

```IF OBJECT_ID('TempDB..#TestSample') IS NOT NULL DROP TABLE #TestSample
CREATE TABLE #TestSample(Sno INT,Group1 INT,Opening INT,Plusvalue INT,MinusValue INT,Closing INT)

INSERT INTO #TestSample(Sno, Group1, Opening, Plusvalue, MinusValue, Closing)
VALUES(1, 1, 0, 5, 2, 0),(1, 2, 0, 8, 4, 0),(1, 3, 0, 8, 6, 0),(2, 1,  0, 9, 7, 0),(2, 2, 0, 7, 2, 0)

select * from #TestSample```

My Required output is

Sno Group1 Opening Plusvalue MinusValue Closing
1 1 0 5 2 3
1 2 3 8 4 7
1 3 7 8 6 9
2 1 0 9 7 2
2 2 2 7 2 7

(ie) For Sno 1 seperate group and for Sno 2 seperate group.

Please tell me the solution.
v2
Amir Mahfoozi at 13-Feb-13 3:00am

ROW_NUMBER() function will help you achieve this new column. Do a research on it and try it till getting the result.
Rate this:

Solution 3

Hi Kirthiga,

Try This

```-- Required Output
SELECT T.Sno, Group1, (ISNULL((SELECT SUM((Opening+Plusvalue-MinusValue))
FROM #TestSample
WHERE Group1 < T.Group1 AND Sno=T.SNo),0)) 'Opening',
T.Plusvalue, T.MinusValue,
(ISNULL((SELECT SUM((Opening+Plusvalue-MinusValue))
FROM #TestSample
WHERE Group1 < T.Group1 AND Sno=T.SNo),0) + T.Plusvalue - T.MinusValue) 'Closing'
FROM #TestSample T```

Have a nice day.....

Regards,
Venkatesh.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

 0 Abhinav S 275 1 Palash Mondal_ 190 2 KrunalRohit 150 3 PANKAJMAURYA 124 4 Manas_Kumar 115
 0 OriginalGriff 3,877 1 KrunalRohit 2,526 2 Maciej Los 2,245 3 CPallini 2,240 4 ppolymorphe 1,750

Advertise | Privacy | Mobile
Web02 | 2.8.151002.1 | Last Updated 13 Feb 2013