Just to give both options together to compare them. The first version including all the remainder in the first month and the second version putting an extra cent per month.
ALTER FUNCTION Payments
(
@Amount decimal(12,4),
@Payments int
)
RETURNS TABLE AS
RETURN
WITH E(n) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,cteTally(n) AS(
SELECT TOP(@Payments) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E a, E b
)
SELECT n AS PaymentNumber,
CASE WHEN n = 1
THEN @Amount - (ROUND(@Amount/@Payments, 2,1) * (@Payments - 1))
ELSE ROUND(@Amount/@Payments, 2,1) END AS PaymentAmount
FROM cteTally
GO
ALTER FUNCTION Payments2
(
@Amount decimal(12,4),
@Payments int
)
RETURNS TABLE AS
RETURN
WITH E(n) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,cteTally(n) AS(
SELECT TOP(@Payments) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM E a, E b
)
SELECT n AS PaymentNumber,
ROUND(@Amount/@Payments, 2,1)
+ CASE WHEN t.n <= r.residual
THEN 0.01
ELSE 0 END AS PaymentAmount
FROM cteTally t
CROSS JOIN (SELECT (@Amount - (ROUND(@Amount/@Payments, 2,1) * (@Payments))) * 100) r(residual)
GO
CREATE TABLE #SampleData(
TotalPayment decimal(12, 4),
Payments int
)
INSERT INTO #SampleData
VALUES(143.23,2), (143.23,5), (143.23,6)
SELECT *
FROM #SampleData
CROSS APPLY Payments(TotalPayment, Payments) p1
CROSS APPLY Payments2(TotalPayment, Payments) p2
WHERE p1.PaymentNumber = p2.PaymentNumber
GO
DROP TABLE #SampleData