SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Subtract current row from previous in same year
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

genlovesmusic09
Starting Member

USA
5 Posts

Posted - 06/21/2013 :  15:28:44  Show Profile  Reply with Quote
Hello!

I am trying to use a cursor to go through my table (called layer_loss2) and subtract the value from the current row from the previous, if they are in the same year.
This is an example of my data:
Year EventNumber Loss Inc Recovery Rec w/ Aggregate Limit
1 1 75M 20M 20M
1 2 60M 40M 20M
1 3 40M 40M 0M
2 1 25M 15M 15M
2 2 60M 35M 20M
2 3 40M 40M 5M

So basically if it was the first event of any year, the Recovery would stay the same (i.e. for year 1, event 1, 20M). If it was the second event, then it would be the current amount in Inc Recovery minus the previous (i.e. for year 1, event 2, 40M-20M)

DECLARE @dYear int, @EventNum int,
    @Cap_CML_L1 FLOAT, @Cap_CML_L2 FLOAT, @Cap_CML_L3 FLOAT,
    @prevL1 FLOAT, @prevL2 FLOAT, @prevL3 FLOAT

DECLARE layer_loss2 CURSOR
    FOR SELECT dYear, EventNum, Cap_CML_L1, Cap_CML_L2, Cap_CML_L3
		FROM Layer_Loss_Cap_Step2
		ORDER BY dYear, EventNum
OPEN layer_loss2
FETCH NEXT FROM layer_loss2

INTO @dYear, @EventNum, @Cap_CML_L1, @Cap_CML_L2, @Cap_CML_L3

WHILE @@FETCH_STATUS = 0
	BEGIN
	IF @EventNum=1
	SET @Cap_CML_L1=@Cap_CML_L1
	PRINT @Cap_CML_L1
	ELSE
	BEGIN
	SET @Cap_CML_L1=@Cap_CML_L1-@prevL1
	END
	SET @EventNum=@EventNum+1
--GRAB NEXT RECORD	
		FETCH NEXT FROM layer_loss2
		INTO @dYear, @EventNum, @Cap_CML_L1, @Cap_CML_L2, @Cap_CML_L3
	END

CLOSE layer_loss2
DEALLOCATE layer_loss2


So far this is the code I have built. I am newish to SQL, and this is my first time using cursors.

Thank you for your help!

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 06/21/2013 :  15:52:19  Show Profile  Reply with Quote
What is the output you are expecting for this sample data? Without access to the tables against which you are running the query, and with the clumn names in the query not matching with the sample data, it is hard to understand what the logic needed is.

In any case, almost always, it is better to use a set based approach instead of a cursor. If you can post sample input and sample output (and use fixed with code formatting like you did for the code, so columns are clearly demarked), someone or other will post a set-based solution.
Go to Top of Page

genlovesmusic09
Starting Member

USA
5 Posts

Posted - 06/21/2013 :  16:09:24  Show Profile  Reply with Quote
Sorry for the confusion. The column labeled "Inc Recovery" (which is called 'Cap_CML_L1' in my code) is the column I want to subtract the current values from the previous values, which would be the column labeled "Rec w/ Aggregate Limit".
So the column labeled "Rec w/ Aggregate Limit" is what I am expecting from the code.
The table, "Layer_Loss_Cap_Step2" looks like the data I've provided. 'EventNum' is the Event number (as in the number when the event happened, so 1 is the first event of the year, 2 is the second event of the year, etc). 'dYear' is the year, it is just numbers like 1, 2, 3 etc. As for 'prevL1', this was what I was trying to label as the previous row.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/21/2013 :  16:20:16  Show Profile  Reply with Quote
Which version of SQL Server are you using?

Also, you might want to check these links so we can help you better:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Edited by - Lamprey on 06/21/2013 16:22:20
Go to Top of Page

genlovesmusic09
Starting Member

USA
5 Posts

Posted - 06/21/2013 :  16:31:45  Show Profile  Reply with Quote
Microsoft SQL Server Management Studio (apart of Microsoft SQL Server 2008 R2)
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/25/2013 :  20:36:47  Show Profile  Reply with Quote
Is this what you are looking for:




DECLARE @Temp1 TABLE([Year] INT,  EventNumber INT,  
                     LossInMillion INT,  [Inc RecoveryInMillion] INT);
INSERT INTO @Temp1 VALUES 
(1, 1, 75, 20),
(1, 2, 60, 40),
(1, 3, 40, 40),
(2, 1, 25, 15),
(2, 2, 60, 35),
(2, 3, 40, 40);


SELECT T1.Year, T1.EventNumber, T1.LossInMillion , T1.[Inc RecoveryInMillion],  
	(T1.[Inc RecoveryInMillion] - COALESCE(T2.[Inc RecoveryInMillion], 0)) 
         as [Rec w/ Aggregate Limit In Million] 
	FROM @TEMP1 T1 LEFT JOIN @Temp1 T2 ON 
        T1.Year = T2.Year and T1.EventNumber = T2.EventNumber+1;

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000