Click here to Skip to main content
12,074,808 members (57,490 online)
Rate this:
 
Please Sign up or sign in to vote.
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: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

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.
  Permalink  
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

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..... Smile | :)


Regards,
Venkatesh.
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


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

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