Subtract from above row without using cursor

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

SQL Server 2008 Forums

Transact-SQL (2008)

Subtract from above row without using cursor

Author

Topic

SQLJunior84
Starting Member

3 Posts

Posted - 2011-12-09 : 09:48:32

Good Afternoon

I have written a query which produces a tempoary table like this

USE MPDB
Create Table Premium
(RowNumber INT,
PolRef Varchar(Max),
SettlementDueDate Datetime,
Premium float,
Signed float)

INSERT INTO Premium
Values (1,'MJHH03372A09','2009-12-09',12454.78,22563.78),
(2,'MJHH03372A09','2010-03-09',12454.78,NULL),
(3,'MJHH03372A09','2010-06-09',12454.78,NULL),
(4,'MJHH03372A09','2010-09-09',12454.78,NULL)

Now how I got that data shown in example was creating a premium table with row number partitions by polref and ordered by settlementduedate then a second table which showed the sum total signed by polref and joined by polid and where rownumber = 1

Hence y all signings in row 2,3,4 are null. What I need to do is create a column at the end which shows remaining debt (signing - premium), then remaining signing - second row premium etc until signing is 0 and rest is debt. I'm very new and never really done things like this, I need to do it for 100,000 policies all with far more complicated signings and premiums so if I can get some help on this one it gives me a base to work from.

The results Id expect is
NEW COLUMN 1 Row 1 0
Row 2 -2345.78
Row 3 -12454.78
Row 4 -12454.78

lappin
Posting Yak Master

182 Posts

Posted - 2011-12-09 : 10:36:35

You can set RowNumber as an identity column to automate the row number (or not if you prefer to insert it yourself). You can get the debt using a calculated column, if you use PERSISTED it is written to disk so only calculated on insert but this is also optional.

Create Table Premium
(RowNumber INT identity,
PolRef Varchar(Max),
SettlementDueDate Datetime,
Premium float,
Signed float
,debt as (Signed - Premium) PERSISTED
)

INSERT INTO Premium
Values ('MJHH03372A09','2009-12-09',12454.78,22563.78),
('MJHH03372A09','2010-03-09',12454.78,NULL),
('MJHH03372A09','2010-06-09',12454.78,NULL),
('MJHH03372A09','2010-09-09',12454.78,NULL)

select * from Premium

Edit:
sorry just noticed title - do you need to subtract from row above?

SQLJunior84
Starting Member

3 Posts

Posted - 2011-12-09 : 10:50:12

Yeah sorry I should have been more specific,

I ran a query sort of modifying this guys website

http://stackoverflow.com/questions/1153879/how-do-i-calculate-a-running-total-in-sql-without-using-a-cursor

and it gives me the correct results if i hardcode the running total paramater

DECLARE @RunningTotal decimal (18,4)

SET @RunningTotal = 22563.7822 -- Total Signing Value

UPDATE #Test
SET @RunningTotal = RunningTotal = @RunningTotal - GBPWrittenPremiumIncome
FROM #Test

Select *, CASE WHEN RunningTotal < 0 THEN DateDiff(dd,GetDate(),SettlementDueDate) ELSE 0 END as DaysOverDue from #Test

So now my debt column now reads as

11808.935 (THis is original 22563.7822 (signing) - 12454.7887 (prem)
-2345.7952 (remaining signing from above which is 10108.9935 - row prem same as above in this instance but can change
-14800.5839 (remaining signing which is by now negative 2345- row prem again)
-27255.3726 (as above)

This looks how I'd want it I think, but not sure if right method, if it will even work for 100,00 + policies or how I can hardcode the signing. :/

lappin
Posting Yak Master

182 Posts

Posted - 2011-12-09 : 10:50:34

Is this what you are looking for?
Create Table Premium
(RowNumber INT identity,
PolRef Varchar(Max),
SettlementDueDate Datetime,
Prem float,
Signed float

)

INSERT INTO Premium
Values ('MJHH03372A09','2009-12-09',12454.78,22563.78),
('MJHH03372A09','2010-03-09',12454.78,NULL),
('MJHH03372A09','2010-06-09',12454.78,NULL),
('MJHH03372A09','2010-09-09',12454.78,NULL)

select p.*, p1.Signed - p.Prem as debt from Premium p
join Premium p1
on p.RowNumber =p1.RowNumber

SQLJunior84
Starting Member

3 Posts

Posted - 2011-12-09 : 10:55:24

That gives me the top level result so I see 22563.7822- 12454.7887 give me a positive balance of 10108.9935, but now I need that 10108.9935 to be essentially populated in the second row for the same sum then second row debt populated in third row and so fourth.

For each individual policy number we'll have multiple premiums over different settlement due dates, and ive partitioned them by row number, we'll then have a sum total signings at the top which needs to be looped through to give final result, in this instance its a negative of about 27k I believe.

lappin
Posting Yak Master

182 Posts

Posted - 2011-12-12 : 04:36:34

Sorry should have joined on next row (...join Premium p1
on p.RowNumber =p1.RowNumber+1...)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-12 : 05:15:38

Also refer this
http://beyondrelational.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources