13,588,673 members
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

## 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
Comments
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.

## 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
Comments
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.

## 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)

Top Experts
Last 24hrsThis month
 OriginalGriff 110 Kornfeld Eliyahu Peter 105 Richard MacCutchan 85 Eric Lynch 70 Mike V Baker 60
 OriginalGriff 3,410 Jochen Arndt 2,202 ppolymorphe 1,714 Thaddeus Jones 1,388 Maciej Los 1,386

Advertise | Privacy | Cookies | Terms of Service
Web04-2016 | 2.8.180615.1 | Last Updated 13 Feb 2013
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100