12,074,808 members (57,490 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 19: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 13-Feb-13 1:08am

+5, Well done.
kirthiga S 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.

v2
Amir Mahfoozi 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.

Top Experts
Last 24hrsThis month
 OriginalGriff 546 ProgramFOX 290 Dave Kreskowiak 240 CHill60 160 Richard MacCutchan 159
 Dave Kreskowiak 2,746 OriginalGriff 2,580 Richard MacCutchan 1,834 CPallini 1,507 CHill60 1,379