Click here to Skip to main content
Rate this: bad
good
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 18: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 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: 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 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: 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)



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