Pro Rata Split between accounts SQL 2008

Question

I've been asked to create a report that splits payments out between several accounts. The payment comes in against the Primary Account. I then need to split it on a pro rata basis based on the refered Balance.

Sample data :

CREATE TABLE [dbo].[tblProRataSplit](
    [CustomerNo] [int] NOT NULL,
    [AccountNo] [int] NOT NULL,
    [Account] [varchar](500) NOT NULL,
    [ReferredBalance] [FLOAT] NULL,
    [Payment] [FLOAT] NULL
) ON [PRIMARY]

Above is an example of how I have the data.

Here is some sample data to populate the table

INSERT 
INTO  tblProRataSplit(CustomerNo, AccountNo, Account, ReferredBalance, Payment)
VALUES     (75053894,3400266,'PRI',19449.56,150.00)
INSERT 
INTO  tblProRataSplit(CustomerNo, AccountNo, Account, ReferredBalance, Payment)
VALUES     (75053894,3443903,'MRGE',928.42,0.00)
INSERT 
INTO  tblProRataSplit(CustomerNo, AccountNo, Account, ReferredBalance, Payment)
VALUES     (75053894,3494492,'MRGE',14286.55,0.00)
INSERT 
INTO  tblProRataSplit(CustomerNo, AccountNo, Account, ReferredBalance, Payment)
VALUES     (75053894,3494714,'MRGE',912.81,0.00)
INSERT 
INTO  tblProRataSplit(CustomerNo, AccountNo, Account, ReferredBalance, Payment)
VALUES     (75053894,3495910,'MRGE',13613.23,0.00)

As you can see the 150.00 payment is registered against the PRI account. I need to split that £150 payment so that it is split between the 5 accounts pro rata against the balance (not evenly split).

I am at a bit off a loss here. If I havent been clear please let me know and I will provide more data.

Pro Rata Split between accounts SQL 2008

1 Answers