June 19, 2009 at 1:36 pm
Anyone out there, I need help writing a query, probably based on some type of looping structure where I need to consume transactions from a "bank" up to a certain amount. The rules are:
1. the amount to consume cannot exceed the overall balance (across time periods)
2. the amount to consume within a time period cannot exceed the balance for that time period, in this case per quarter.
3. the transactions have to be consumed in the order they come in (FIFO)
4. there can be negative transactions (as shown below)
5. there are only 2 time periods below. Assume there could be "n" periods.
Here is script and at the bottom is the expected output. The comment column is only there to explain. Let me know if you have any questions.
declare @balancequarter table
(
quarterid int
,amount int
)
declare @transaction table
(
transactionid int
,quarterid int
,amount int
)
declare @amounttoload int
set @amounttoload = 15
insert into @transaction select 1, 1, 5
insert into @transaction select 2, 1, 3
insert into @transaction select 3, 1, 7
insert into @transaction select 4, 1, -5
insert into @transaction select 5, 2, 10
insert into @transaction select 6, 2, 16
insert into @transaction select 7, 2, -6
insert into @balancequarter select quarterid, sum(amount) from @transaction group by quarterid
-- debug code
select * from @balancequarter
select * from @transaction
--expected output
transactionidquarteridoriginaltransactionamountusedtransactionamountcomment
1155'fully used'
2133'fully used'
3172'partial transaction - quarter balance reached'
52105'partial transaction - amount fully loaded'
"Any fool can write code that a computer can understand. Good programmers write
code that humans can understand." -Martin Fowler et al, Refactoring: Improving the Design of Existing Code, 1999
June 19, 2009 at 5:07 pm
Duplicate post...
http://www.sqlservercentral.com/Forums/Topic738685-338-1.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply