set-based way to apply a rounding adjustment to the largest share
-
Is there a set-based way to apply a rounding adjustment to the largest share?
All I can find or conjure up are cursor-based solutions which I want to avoid but cannot think of any other way to do it.
Scenario:
A payment of £45675.23 is to be distributed to the following parties: A gets half while B, C & D get one sixth each.
I have the following in my Party table:
>>>> code >>>>
insert Party(PartyId, Share) values(1, .50)
insert Party(PartyId, Share) values(2, .166666)
insert Party(PartyId, Share) values(3, .166666)
insert Party(PartyId, Share) values(4, .166666)
>>>> code >>>>
And a payment to apply
>>>> code >>>>
insert Payment(PayId, Amount) values(1, 45675.23)
>>>> code >>>>
Which is split amongst a PartyPayment table like so:
>>>> code >>>>
insert PartyPayment(
PartyId,
PayId,
Amount)
select
Party.PartyId,
Payment.PayId,
(Payment.Amount * Party.Share)
from
Payment,
Party
where
Payment.PayId = @PayNo
>>>> code >>>>
This leaves 0.09 outstanding which I want to assign to the party with the largest share so I could use the following set method:
>>>> code >>>>
update PartyPayment set
Amount = (Amount + (select
Payment.Amount - sum(PartyPayment.Amount)
from
Payment,
PartyPayment
where
Payment.PayId = PartyPayment.PayId and
Payment.PayId = @PayNo
group by
PartyPayment.PayId,
Payment.Amount
))
from
PartyPayment
where
PayId = @PayNo and
PartyId = (select top 1 PartyId from PartyPayment where PartyPayment.PayId = @PayNo order by PartyPayment.Amount)
>>>> code >>>>
But this looks awful in a showplan no matter what indexes I create
Worse is that I need to do this for hundreds of payments. I have ended up using a cursor that loops for each payment doing this:
>>>> code >>>>
select @MaxPartyAmount = max(Amount) from PartyPayment where PayId = @PayNo
select @MaxPartyId = PartyId from PartyPayment where PayId = @PayNo and Amount = @MaxPartyAmount
>>>> code >>>>
I then apply the split using the @MaxPartyId but this processing of a payment at a time seems very inefficient.
I wonder if I am missing some logic somewhere? I have got a lot better at using set methods instead of loops but I still get the odd mental block.
-
Here is a set-based method based to assign the rounding amount to the party with the largest share and if two parties have equal shares, then the one with the lower partyid is given the rounding amount. I have made the schema a little more complex by having multiple "Contracts" and therefore added "contractId" as part of the primary key to all of the tables. Change "Contract" to the appropriate business term.
create schema authorization dbo
Create table ContractParty
( ContractIdintegernot null
, PartyId integer not null
, Share float not null
, constraint Party_PK primary key (ContractId , PartyId)
, constraint Party_C_Share check (Share >= 0.0)
)
Create table ContractPayment
( ContractIdintegernot null
, PayIdinteger not null
, Amount decimal(11,2) not null
, constraint Payment_PK primary key (ContractId , PayId)
, constraint Payment_C_Amount check (Amount > 0.0 )
)
Create table ContractPartyPayment
( ContractIdintegernot null
, PartyId integer not null
, PayIdinteger not null
, Amount decimal (11,2) not null
, constraint PartyPayment_PK primary key (ContractId , PartyId , PayId )
, constraint PartyPayment_C_Amount check (Amount >= 0.0)
, constraint Party_F_PartyPayment foreign key (ContractId , PartyId) references ContractParty
, constraint Payment_F_PartyPayment foreign key (ContractId , PayId) references ContractPayment
)
go
Create unique index ContractParty_X_Share on ContractParty (ContractId, Share, PartyId)
go
insert ContractParty(ContractId , PartyId, Share) values( 1 , 1, .50)
insert ContractParty(ContractId , PartyId, Share) values( 1 , 2, .166666)
insert ContractParty(ContractId , PartyId, Share) values( 1 , 3, .166666)
insert ContractParty(ContractId , PartyId, Share) values( 1 , 4, .166666)
insert ContractParty(ContractId , PartyId, Share) values( 2 , 1, .333333)
insert ContractParty(ContractId , PartyId, Share) values( 2 , 2, .333333)
insert ContractParty(ContractId , PartyId, Share) values( 2 , 3, .166666)
insert ContractParty(ContractId , PartyId, Share) values( 2 , 4, .166666)
insert ContractPayment(ContractId , PayId, Amount) values(1, 1, 45675.23)
insert ContractPayment(ContractId , PayId, Amount) values(1 , 2, 245675.23)
insert ContractPayment(ContractId , PayId, Amount) values(1 ,3, 25675.23)
insert ContractPayment(ContractId , PayId, Amount) values(2 , 1 , 675.23)
insert ContractPayment(ContractId , PayId, Amount) values(2 , 2 , 5675.23)
insert ContractPayment(ContractId , PayId, Amount) values(2 , 3 , 275.23)
go
insert ContractPartyPayment
( ContractId , PartyId, PayId, Amount)
select ContractParty.ContractId
,ContractParty.PartyId
, ContractPayment.PayId
, ROUND( (ContractPayment.Amount * ContractParty.Share) , 2 )
from ContractPayment
JOIN ContractParty
on ContractParty.ContractId= ContractPayment.ContractId
go
begin transaction
update ContractPartyPayment
set Amount = Amount + ContractPayment_Adjustment.AdjustmentAmt
from -- Determine the rounding adjustment amount by Contract and Payment
(select ContractPayment.ContractId
,ContractPayment.PayId
, ContractPayment.Amount - sum(ContractPartyPayment.Amount) as AdjustmentAmt
from ContractPayment
joinContractPartyPayment
on ContractPartyPayment.ContractId = ContractPayment.ContractId
and ContractPartyPayment.PayId = ContractPayment.PayId
group by ContractPayment.ContractId
,ContractPayment.PayId
, ContractPayment.Amount
--Rounding must be outside of the range of 0.01 (positive or negative)
HAVING ContractPayment.Amount - sum(ContractPartyPayment.Amount) >= +0.01
OR ContractPayment.Amount - sum(ContractPartyPayment.Amount) <= -0.01
) As ContractPayment_Adjustment
--Contract Party with largest share - on equal share, lowest partyId is tiebreaker
,(select ContractParty.ContractId
,MIN(PartyId) AS PartyId
fromContractParty
join(select ContractParty.ContractId
,MAX(Share) as Share
from ContractParty
group by ContractParty.ContractId
) as ContractParty_LargestShareholder
on ContractParty_LargestShareholder.ContractId = ContractParty.ContractId
and ContractParty_LargestShareholder.Share = ContractParty.Share
GROUP BY ContractParty.ContractId
) AS ContractParty_Adjustment
WHERE ContractPartyPayment.ContractId= ContractPayment_Adjustment.ContractId
ANDContractPartyPayment.PayId = ContractPayment_Adjustment.PayId
ANDContractPartyPayment.ContractId= ContractParty_Adjustment.ContractId
AND ContractPartyPayment.PartyId = ContractParty_Adjustment.PartyId
-- Confirm in balance
select ContractPayment.ContractId
, ContractPayment.PayId
,ContractPayment.Amount
,sum(ContractPartyPayment.Amount)
from ContractPayment
joinContractPartyPayment
on ContractPartyPayment.ContractId = ContractPayment.ContractId
and ContractPartyPayment.PayId = ContractPayment.PayId
group by ContractPayment.ContractId
, ContractPayment.PayId
,ContractPayment.Amount
having ContractPayment.Amount sum(ContractPartyPayment.Amount)
rollback
SQL = Scarcely Qualifies as a Language
-
You missed the case when 2 or more shareholders are the largest.
Check your method for a case with 6 shareholders evenly shared the amount of 100 dollars.
And
ContractPayment.Amount - sum(ContractPartyPayment.Amount) >= +0.01
OR ContractPayment.Amount - sum(ContractPartyPayment.Amount) <= -0.01
could be replaced with
ABS(ContractPayment.Amount - sum(ContractPartyPayment.Amount)) >= +0.01
More easy to read and less math for server to do.
_____________
Code for TallyGenerator -
Regarding, "You missed the case when 2 or more shareholders are the largest", should not this section of the SQL handle that case?
select ContractParty.ContractId
, MIN(PartyId) AS PartyId
from ContractParty
join (select ContractParty.ContractId
, MAX(Share) as Share
from ContractParty
group by ContractParty.ContractId
) as ContractParty_LargestShareholder
on ContractParty_LargestShareholder.ContractId = ContractParty.ContractId
and ContractParty_LargestShareholder.Share = ContractParty.Share
GROUP BY ContractParty.ContractId
Regarding substituting "ABS(ContractPayment.Amount - sum (ContractPartyPayment.Amount)) >= +0.01".
Yes, the that works out the same, but I tend to have the habit of not using functions since they may generate comparisions that cannot use an index.
SQL = Scarcely Qualifies as a Language
-
ContractPayment.Amount - sum (ContractPartyPayment.Amount) >= +0.01
will not use index definetely, operator "-" is a function as well.
So, you have nothing to loose.
_____________
Code for TallyGenerator -
I think it may be easier to do this in steps.
Identify who will receive the rounded value
Insert payment records for all other parties based on multiplying by share percent
Insert final payment with rounding value by subtracting other payments from total payment
DECLARE
@RoundParty INT
SET @RoundParty = (SELECT TOP 1 PartyID FROM Party ORDER BY Share DESC, PartyID)
INSERT INTO PartyPayment (PartyID, PayID, Amount)
SELECT Party.PartyID, Payment.PayID, (Payment.Amount * Party.Share)
FROM Payment, Party
WHERE Payment.PayID = @PayNo
AND Party.PartyID <> @RoundParty
INSERT INTO PartyPayment (PartyID, PayID, Amount)
SELECT @RoundParty, Payment.PayID, (Payment.Amount - ISNULL(p.Amount, 0))
FROM Payment
LEFT JOIN (
SELECT PayID, SUM(Amount) AS Amount
FROM PartyPayment WHERE PayID = @PayNo
GROUP BY PayID
) p ON Payment.PayID = p.PayID
WHERE Payment.PayID = @PayNo
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply