Here is something that works:CREATE TABLE #CustomerAccount
(
AccountNoINT,
Customer1IDINT,
Customer2IDINT,
Customer3IDINT,
Customer4IDINT,
AccountHoldersTinyInt
)
CREATE TABLE #AccountBalance
(
AccountNoINT,
Balance MONEY,
InterestMONEY
)
INSERT INTO #CustomerAccount(AccountNo, Customer1ID, Customer2ID, Customer3ID, Customer4ID, AccountHolders)
VALUES(10001,1,0,0,0,1), (20002,2,3,0,0,2), (30003,4,5,6,0,3), (40004, 7,8,9,10,4), (50005,11,12,0,0,2), (60006,13,14,15,0,3), (70007,16,17,18,19,4)
INSERT INTO #AccountBalance(AccountNo, Balance, Interest)
VALUES(10001,500,0.54), (20002,459,0.34), (30003, 333, 0.33), (40004, 100, 0.4), (50005, 101, 0.33), (60006, 100, 1), (70007, 112, 0.55)
SELECT *,
round(CASE WHEN Customer1ID != 0 THEN (AB.Balance + AB.Interest) / CA.AccountHolders ELSE 0 END,2)
+ (case when Customer2ID = 0 then (AB.Balance + AB.Interest) - ((round((AB.Balance + AB.Interest) / CA.AccountHolders,2)) * CA.AccountHolders) else 0 end) AS AccountHolder1Balance,
round(CASE WHEN Customer2ID != 0 THEN (AB.Balance + AB.Interest) / CA.AccountHolders ELSE 0 END,2)
+ (case when Customer3ID = 0 and Customer2ID != 0 then (AB.Balance + AB.Interest) - ((round((AB.Balance + AB.Interest) / CA.AccountHolders,2)) * CA.AccountHolders) else 0 end) AS AccountHolder2Balance,
round(CASE WHEN Customer3ID != 0 THEN (AB.Balance + AB.Interest) / CA.AccountHolders ELSE 0 END,2)
+ (case when Customer4ID = 0 and Customer3ID != 0 then (AB.Balance + AB.Interest) - ((round((AB.Balance + AB.Interest) / CA.AccountHolders,2)) * CA.AccountHolders) else 0 end) AS AccountHolder3Balance,
round(CASE WHEN Customer4ID != 0 THEN (AB.Balance + AB.Interest) / CA.AccountHolders ELSE 0 END,2)
+ (case when Customer4ID != 0 then (AB.Balance + AB.Interest) - ((round((AB.Balance + AB.Interest) / CA.AccountHolders,2)) * CA.AccountHolders) else 0 end) AS AccountHolder4Balance,
(AB.Balance + AB.Interest) - ((round((AB.Balance + AB.Interest) / CA.AccountHolders,2)) * CA.AccountHolders) as diff
FROM #CustomerAccount CA INNER JOIN #AccountBalance AB ON CA.AccountNo = AB.AccountNo You can delete the diff column, but wanted to leave it to show you what it is doing to determine the round issue.