Subtract current row from previous in same year

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subtract current row from previous in same year

Author

Topic

genlovesmusic09
Starting Member

5 Posts

Posted - 2013-06-21 : 15:28:44

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-21 : 15:52:19

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.

genlovesmusic09
Starting Member

5 Posts

Posted - 2013-06-21 : 16:09:24

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-21 : 16:20:16

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

genlovesmusic09
Starting Member

5 Posts

Posted - 2013-06-21 : 16:31:45

Microsoft SQL Server Management Studio (apart of Microsoft SQL Server 2008 R2)

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-25 : 20:36:47

Is this what you are looking for:

[CODE]

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;

[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources