May 14, 2009 at 6:17 pm
[font="Verdana"]Hi all,
I an rewriting some code I have in the mock up system to go into our data warehouse staging environment. The code allocates reversal transactions to outage transactions (it's a reconciliation thing). The trick is a reversal can only apply to one transaction, and a transaction can only have up to one reversal. What makes it hard is that the allocation needs to be done by logic: there is nothing on the reversal transaction that directly refers to the outage transaction.
So here are the rules:
1. An outage can have zero or one reversals
2. A reversal can be applied to zero or one transactions
3. The reversal should be applied to the earliest outage to which a reversal hasn't already been applied
4. A reversal and outage match if the reversal occurs after the outage and the amounts are inverted.
I've drawn up some example code that demonstrates what I am trying to do. You will notice that the example code uses a while loop, and allocates reversals one at a time to ensure they do not get allocated more than once.
If any of the SQL gurus out there can come up with a method where I can allocate the lot as a set, that would be great!
--
-- set up the transaction types
--
declare@type_outagetinyint,
@type_reversaltinyint;
set @type_outage = 1;
set @type_reversal = 2;
--
-- set up the transaction tellers
--
declare@teller_joetinyint,
@teller_mary tinyint,
@teller_fredtinyint;
set @teller_joe = 1;
set @teller_mary = 2;
set @teller_fred = 3;
--
-- create some example transactions
--
if object_id('tempdb..##Transaction') is not null
drop table ##Transaction;
create table ##Transaction(
TransactionIDtinyint identity not null primary key,
TransactionTypetinyint not null,
TransactionTellertinyint not null,
TransactionTimesmalldatetime not null,
TransactionAmountsmallmoney not null
);
--
-- create the example outage transactions
--
insert into ##Transaction(TransactionType, TransactionTeller, TransactionTime, TransactionAmount)
select@type_outage, @teller_joe, '20000101 01:00:00', 100 union all
select@type_outage, @teller_joe, '20000101 01:30:00', 200 union all
select@type_outage, @teller_mary, '20000101 02:00:00', 100 union all
select@type_outage, @teller_mary, '20000101 02:30:00', 300 union all
select@type_outage, @teller_mary, '20000101 03:30:00', 100 union all
select@type_outage, @teller_mary, '20000101 04:00:00', 200 union all
select@type_outage, @teller_joe, '20000101 03:00:00', 400;
--
-- create the example reversals
--
insert into ##Transaction(TransactionType, TransactionTeller, TransactionTime, TransactionAmount)
select@type_reversal, @teller_fred, '20000101 01:10:00', -100 union all
select@type_reversal, @teller_fred, '20000101 05:10:00', -200 union all
select@type_reversal, @teller_fred, '20000101 06:10:00', -100;
select*
from##Transaction;
--
-- create the table to hold the reversals
--
if object_id('tempdb..##Reversal') is not null
drop table ##Reversal;
create table ##Reversal(
Outage_TransactionIDtinyint not null primary key,
Reversal_TransactionIDtinyint not null unique
);
while (1=1) begin
with
UnallocatedReversal as (
select top (1)
t.*
from ##Transaction t
left join
##Reversal r
on t.TransactionID = r.Reversal_TransactionID
where t.TransactionType = @type_reversal and
r.Reversal_TransactionID is null
order by
TransactionTime
),
UnallocatedOutage as (
select t.*
from ##Transaction t
left join
##Reversal r
on t.TransactionID = r.Outage_TransactionID
where t.TransactionType = @type_outage and
r.Outage_TransactionID is null
)
insert into ##Reversal
select top (1)
uo.TransactionID,
ur.TransactionID
from UnallocatedReversal ur
inner join
UnallocatedOutage uo
on uo.TransactionAmount = -ur.TransactionAmount and
uo.TransactionTime < ur.TransactionTime
order by
uo.TransactionTime;
if @@rowcount = 0 break;
end; -- while
select*
from##Reversal;
[/code]
[/font]
May 14, 2009 at 7:12 pm
Not sure if this solution meets all of the requirements but it sjhould be close.
SELECTOutage.TransactionAmount
,Outage.TransactionID
,Reversal.TransactionID
FROM(selectTransactionAmount
,ROW_NUMBER ( ) OVER
( PARTITION BY TransactionAmount
ORDER BY TransactionTime )as OutageSequence
,TransactionID
,TransactionTeller
,TransactionTime
,TransactionType
from##Transaction
WHERETransactionType = 1
) as Outage
JOIN(selectTransactionAmount
,ROW_NUMBER ( ) OVER
( PARTITION BY TransactionAmount
ORDER BY TransactionTime )as ReversalSequence
,TransactionID
,TransactionTeller
,TransactionTime
,TransactionType
from##Transaction
WHERETransactionType = 2
) as Reversal
ON (Reversal.TransactionAmount * -1 ) = Outage.TransactionAmount
AND Reversal.ReversalSequence = Outage.OutageSequence
SQL = Scarcely Qualifies as a Language
May 14, 2009 at 7:24 pm
Carl Federl (5/14/2009)
Not sure if this solution meets all of the requirements but it sjhould be close.
[font="Verdana"]It seems to give identical results. Thanks -- it looks like that might be the right approach. I'll see if I can adapt it to fit.[/font]
May 14, 2009 at 7:45 pm
Bruce W Cassidy (5/14/2009)
Carl Federl (5/14/2009)
Not sure if this solution meets all of the requirements but it sjhould be close.[font="Verdana"]It seems to give identical results. Thanks -- it looks like that might be the right approach. I'll see if I can adapt it to fit.[/font]
[font="Verdana"]Okay, I've thought through applying that approach to the actual set of data (which is somewhat more complex as you can imagine), and it looks like it will do the job nicely.
By merging the two sets, it guarantees that an outage can only have up to one reversal, and a reversal can only be applied to one outage. By partitioning on the join criteria, it ensures that reversals are applied to matching outages. And by ordering by the time it ensures that the reversals are allocated to the earliest outage.
The complications are:
The join criteria includes a description. That's no problem at all, as that just gets added to the partition.
The values must match within one dollar. That's a bit tricky. However, I think if I round down to the nearest dollar, it should be fine.
[/font]
May 14, 2009 at 8:14 pm
[font="Verdana"]Still working through.
The issue is where a reversal exists to which there is no outage (surprisingly, they do happen.) In this case, both approaches fail. I can make them work by adding a where exists clause.
Here's the updated code, showing both cases to verify that they match:
--
-- set up the transaction types
--
declare
@type_outagetinyint,
@type_reversaltinyint;
select
@type_outage = 1,
@type_reversal = 2;
--
-- set up the transaction tellers
--
declare
@teller_joetinyint,
@teller_marytinyint,
@teller_fredtinyint;
select
@teller_joe = 1,
@teller_mary = 2,
@teller_fred = 3;
--
-- create some example transactions
--
if object_id('tempdb..##Transaction') is not null
drop table ##Transaction;
create table ##Transaction(
TransactionIDtinyint identity not null primary key,
TransactionTypetinyint not null,
TransactionTellertinyint not null,
TransactionTimesmalldatetime not null,
TransactionAmountsmallmoney not null
);
--
-- create the example outage transactions
--
insert into ##Transaction(TransactionType, TransactionTeller, TransactionTime, TransactionAmount)
select @type_outage, @teller_joe, '20000101 01:00:00', 100 union all
select @type_outage, @teller_joe, '20000101 01:30:00', 200 union all
select @type_outage, @teller_mary, '20000101 02:00:00', 100 union all
select @type_outage, @teller_mary, '20000101 02:30:00', 300 union all
select @type_outage, @teller_mary, '20000101 03:30:00', 100 union all
select @type_outage, @teller_mary, '20000101 04:00:00', 200 union all
select @type_outage, @teller_joe, '20000101 03:00:00', 400;
--
-- create the example reversals, now included an unmatched reversal
--
insert into ##Transaction(TransactionType, TransactionTeller, TransactionTime, TransactionAmount)
select @type_reversal, @teller_fred, '20000101 00:10:00', -100 union all
select @type_reversal, @teller_fred, '20000101 01:10:00', -100 union all
select @type_reversal, @teller_fred, '20000101 05:10:00', -200 union all
select @type_reversal, @teller_fred, '20000101 06:10:00', -100;
select*
from##Transaction;
--
-- create the table to hold the reversals
--
if object_id('tempdb..##Reversal') is not null
drop table ##Reversal;
create table ##Reversal(
Outage_TransactionIDtinyint not null primary key,
Reversal_TransactionIDtinyint not null unique
);
with
Outage as (
selectrow_number() over (
partition by TransactionAmount
order by TransactionTime
) as Sequence,
TransactionID,
TransactionAmount,
TransactionTime
from##Transaction o
whereTransactionType = @type_outage and
exists (
select 1
from ##Transaction r
where o.TransactionType = @type_outage and
o.TransactionTime < r.TransactionTime and
o.TransactionAmount = -r.TransactionAmount
)
),
Reversal as (
selectrow_number() over (
partition by TransactionAmount
order by TransactionTime
) as Sequence,
TransactionID,
TransactionAmount,
TransactionTime
from##Transaction r
whereTransactionType = @type_reversal and
exists (
select 1
from ##Transaction o
where o.TransactionType = @type_outage and
o.TransactionTime < r.TransactionTime and
o.TransactionAmount = -r.TransactionAmount
)
)
selectOutage.TransactionID as Outage_TransactionID,
Reversal.TransactionID as Reversal_TransactionID
fromOutage
inner join
Reversal
onOutage.TransactionAmount = -Reversal.TransactionAmount and
Outage.TransactionTime < Reversal.TransactionTime and
Outage.Sequence = Reversal.Sequence
order by
Outage.TransactionID ;
while (1=1) begin
with
UnallocatedReversal as (
select top (1)
t.*
from ##Transaction t
inner join
##Transaction x
on x.TransactionType = @type_outage and
x.TransactionTime t.TransactionTime and
x.TransactionAmount = -t.TransactionAmount
left join
##Reversal r
on t.TransactionID = r.Outage_TransactionID
where t.TransactionType = @type_outage and
r.Outage_TransactionID is null
)
insert into ##Reversal
select top (1)
uo.TransactionID,
ur.TransactionID
from UnallocatedReversal ur
inner join
UnallocatedOutage uo
on uo.TransactionAmount = -ur.TransactionAmount and
uo.TransactionTime < ur.TransactionTime
order by
uo.TransactionTime;
if @@rowcount = 0 break;
end; -- while
select*
from##Reversal
order by
Outage_TransactionID ;
[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply