Conditional calculation based on multiple columns requiring a window function

here's the table I'm working with:

CUST_REF	ACCOUNT_NUMBER	VALUATION	RATE
10001	ABC123	2000	0.0025
10001	XYZ456	3000	45
10001	DEF334	2000	45

I'm trying to build a fees calculator in SQL Server which applies the rate on the account valuation, with a few special conditions thrown in. I've managed to cover off all permutations but am thrown by an edge-case where I need to apply a flat rate of £3.75 per month in a few cases.

For the above example the table I'm looking to generate, with the expected output, is as follows:

CUST_REF	ACCOUNT_NUMBER	VALUATION	RATE	FEE
10001	ABC123	2000	0.0025	0.42
10001	XYZ456	3000	45	2.25
10001	DEF334	2000	45	1.50

Essentially:

Where there's a rate other than '45', calculate the fee by multiplying the valuation figure by the rate / 12.
When there's 45 seen against any of the accounts, divide a fixed figure of £3.75 between any account with the rate of 45, in the proportion of the valuations - in this case 3000 / 5000 = 60% or 2.25 to the first account and 1.50 to the next.

I've tried...

SELECT
     A.CUST_REF,
     A.ACCOUNT_NUMBER,
     A.VALUATION,
     A.RATE,

     CASE 
        WHEN A.RATE= 45 THEN 3.75 * (A.VALUATION) / SUM(A.VALUATION) OVER (PARTITION BY A.CUST_REF)
        WHEN A.RATE != 45 THEN (A.VALUATION) * (A.RATE/12)
     
     FROM FEES_TABLE A

... but the above code does not yield the expected output as the proportion calculated is inaccurate.

Any thoughts please?

Solution

I think you just missed out partitioning by rate in your sum function.

declare @FEES_TABLE table (CUST_REF int, ACCOUNT_NUMBER varchar(6), VALUATION money, RATE money);

insert into @FEES_TABLE (CUST_REF, ACCOUNT_NUMBER, VALUATION, RATE)
values
(10001, 'ABC123',   2000,   0.0025),
(10001, 'XYZ456',   3000,   45),
(10001, 'DEF334',   2000,   45);

select A.CUST_REF, A.ACCOUNT_NUMBER, A.VALUATION, A.RATE
  , convert(decimal(9,2),
    case when RATE != 45 then A.VALUATION * A.RATE / 12
    else VALUATION / sum(A.VALUATION) over (partition by A.CUST_REF, A.RATE/*<-- this fixes it*/) * 3.75 end
  ) FEE
from @FEES_TABLE A

Returns:

CUST_REF	ACCOUNT_NUMBER	VALUATION	RATE	FEE
10001	ABC123	2000	0.0025	0.42
10001	XYZ456	3000	45	2.25
10001	DEF334	2000	45	1.50

Note: Adding your sample data as DDL+DML (as shown here) makes it much easier for people to assist you.