Prorating data without using cursors

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 2000 Forums

Transact-SQL (2000)

Prorating data without using cursors

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-09 : 08:51:04

Anand writes "Hi All,

I have a Table with 2 Columns
AccountID and Limit
1 10,000
2 25,000
3 30,000
Which means upto 10,000 i can use Account 1 and after 10,000 upto 25,000 I can use account 2 and after 25,000 from account2, I can use Account3.

Now I can get already used amount (Say 8,000) and Currently I have to expense 3,000.

Since Account 1 limit is 10,000 and I have already expensed 8,000 from account 1, I can expense remaining 2,000 to account 1 and the balance should go to account 2 and nothing to account3.

Can I achive this logic whthout using Cursors??
Please help

Thanks in Advance"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-09 : 09:41:08

A quick'n'dirty solution


declare @t table (AccountID INT, Limit INT, Withdraw INT)

insert @t
select 1, 10000, 8000 union all
select 2, 25000, 0 union all
select 3, 30000, 0

select * from @t

declare @e int, @i int, @m int, @limit int, @withdraw int

select	@e = 3000,
	@i = min(accountid),
	@m = max(accountid)
from	@t

WHILE	@i <= @m and @e > 0
	begin
		select	@limit = Limit,
			@withdraw = Withdraw
		from	@t
		where	accountid = @i

		if @withdraw + @e <= @limit
			update	@t
			set	withdraw = withdraw + @e
			where	accountid = @i
		else
			if @withdraw + @e > @limit
				update	@t
				set	withdraw = limit
				where	accountid = @i

		select	@e = @e + @withdraw - @limit

		select	@i = min(accountid)
		from	@t
		where	accountid > @i
	end

select * from @t

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-06-09 : 10:22:46

here's a set based method.. just for grins


declare @t table (AccountID INT, Limit INT, Withdraw INT)

insert @t
select 0, 1000, 1000 union all
select 1, 10000, 8000 union all
select 2, 25000, 0 union all
select 3, 30000, 0

select * from @t

Declare @e int
Set @e = 28500

Select 
	*,
	Apply = case 
			when spendFirst is null then
				case when @e > Limit - withdraw then Limit - withdraw else @e end
			else
				case when @e >= isnull(spendFirst,0) then 
					case when @e > spendFirst then 
						case when @e - spendFirst < limit - withdraw then @e - spendFirst else limit - withdraw end
					else @e end
				else 0 end	
			end
From 
	(
	Select 
		A.*,
		spendFirst = (select sum(limit - withdraw) From @t Where accountId < a.accountId and limit > withdraw)
	From @t a
	) Z

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-13 : 06:24:55

For more grins, here's and quicker and even dirtier solution

--data
declare @t table (AccountID INT primary key clustered, Limit INT, Withdraw INT)
insert @t
          select 1, 10000, 8000
union all select 2, 25000, 0
union all select 3, 30000, 0

--inputs
declare @e int
set	@e = 3000

--calculation
--this is the dirty bit since the order of the rows in this update cannot be relied upon
declare @Remainder int
update @t set
    @e = isnull(@Remainder, @e),
    @Remainder = case when @e > (Limit - Withdraw) then @e - (Limit - Withdraw) else 0 end,
    Withdraw = case when @Remainder > 0 then Limit else Withdraw + @e end

select * from @t

/*results
AccountID   Limit       Withdraw    
----------- ----------- ----------- 
1           10000       10000
2           25000       1000
3           30000       0
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources