Luis Cazares (1/29/2015)
Maybe something like this. If you change/remove the third parameter of round() you'll get different results. This will always return the first payment equal or greater than all the other payments.
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
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)
GO
DROP TABLE #SampleData
Luis, or someone else, I am a fellow DBA but I have not seen this before! If it is not too much to ask, can you just kind of explain what this code is doing! I am familiar with CTE's, ROW_NUMBER() OVER, but I guess I am confused as to what the SELECT 1 UNION ALL over and over is really doing...And the WITH E(n), and in the ROW_NUMBER SELECT statement, the FROM E a, E b?
That has me all confused! I know the output is exactly what I need, I just would like to know how the code is working to achieve it...
Thanks