January 29, 2015 at 1:10 pm
I need help figuring out in SQL Server (TSQL) how to divide a number by either 2,3,4, or 6 and spreading it across that number of rows depending on what we are dividing the number by where it comes back to the original number...That is confusing I know so let me break it down...
Bill for 143.23 that will be paid out through 2 months...When you divide that by 2, you come back with 71.62, but if you multiply that number by 2, you come back with 143.24, not the amount for the bill...the end result has to be 71.62 for month1 and 71.61 for month2...Basically when there is a remainder, that has to be applied to the first month...
143.23:
Month1 = 71.62
Month2 = 71.61
Another example...Same amount but have to divide by 6
143.23
Month1 = 23.88
Month2 = 23.87
Month3 = 23.87
Month4 = 23.87
Month5 = 23.87
Month6 = 23.87
Thanks
January 29, 2015 at 1:23 pm
Also remember the amounts can change as well what it is divided by...
January 29, 2015 at 1:36 pm
Does this give you any ideas?
select Ceiling((143.230*100) / 6)/100, floor((143.230*100) / 6)/100
January 29, 2015 at 1:41 pm
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
January 29, 2015 at 1:45 pm
Thank you both for the responses, let me try them out and see what I come up with!
January 29, 2015 at 2:24 pm
djj (1/29/2015)
Does this give you any ideas?
select Ceiling((143.230*100) / 6)/100, floor((143.230*100) / 6)/100
I was happy as I thought this was going to work as it worked for most of my examples, however, it doesnt account for a 2 penny remainder:
Using the solution above, the result was:
Month1 = 47.68
Month2 = 47.67
Month3 = 47.67
Totaling 143.02, off by a penny!
Here is what is needed...
Example: Amount is 143.03, and have to spread it out over 3 months:
Expected Result:
Month1 = 47.69
Month2 = 47.67
Month3 = 47.67
Thanks again for the input though!!!!
January 29, 2015 at 2:25 pm
Louis's code puts the entire remainder in the first payment, which is technically what you asked for. Another approach would be to add 0.01 per month to make up the difference.
DECLARE @amount DECIMAL(8,2) = 143.23;
DECLARE @periods INT = 6;
SELECT Period = number,
Payment = pmt + CASE WHEN number <= residual THEN 0.01 ELSE 0 END
FROM master.dbo.spt_values
CROSS JOIN (
-- Calculate how many payments need an extra 0.01
SELECT pmt, residual = CAST((@amount - pmt * @periods) * 100 AS INT)
FROM (
-- Calculate the base payment, rounded down
SELECT pmt = CAST(FLOOR(@amount * 100 / @periods) / 100 AS DECIMAL(8,2))
) p
) r
WHERE type = 'P' AND number BETWEEN 1 AND @periods;
January 29, 2015 at 2:29 pm
First thought is that is going to work...Still have to run through some examples but this is looking like the way to go! Thanks Luis
January 29, 2015 at 2:34 pm
Scott Coleman (1/29/2015)
Louis's code puts the entire remainder in the first payment, which is technically what you asked for. Another approach would be to add 0.01 per month to make up the difference.
DECLARE @amount DECIMAL(8,2) = 143.23;DECLARE @periods INT = 6;
SELECT Period = number,
Payment = pmt + CASE WHEN number <= residual THEN 0.01 ELSE 0 END
FROM master.dbo.spt_values
CROSS JOIN (
-- Calculate how many payments need an extra 0.01
SELECT pmt, residual = CAST((@amount - pmt * @periods) * 100 AS INT)
FROM (
-- Calculate the base payment, rounded down
SELECT pmt = CAST(FLOOR(@amount * 100 / @periods) / 100 AS DECIMAL(8,2))
) p
) r
WHERE type = 'P' AND number BETWEEN 1 AND @periods;
Yes you are correct, that is what I was asking and that is what the result was...I had not worked with it yet, I was going over djj's solution first, then moved on to Luis'...But I believe this is going to do the trick...Now just have to integrate it into my existing code!! Thanks so much guys...
January 29, 2015 at 3:47 pm
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
January 29, 2015 at 5:35 pm
You might want to read this article:
Financial Rounding of Allocations [/url]
The problem solved there differs slightly in that you're trying to allocate an amount across rows with different values, but I'm pretty sure the same technique can be used to achieve this "penny rounding."
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 29, 2015 at 5:52 pm
Or maybe it would be best to just skip all of that insufferable reading and do it like this.
WITH SampleData (ID, Amount, Months) AS
(
SELECT 1, 143.23, 2 UNION ALL SELECT 2, 143.25, 6
),
Tally (n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
)
SELECT ID, Amount, Months, n, DividedAmt, TotalAmt
,CorrectedAmt=DividedAmt +
CASE WHEN ABS((TotalAmt-Amount)*100) <= n-1
THEN 0
ELSE .01*SIGN((TotalAmt-Amount))
END
FROM
(
SELECT ID, Amount, Months, n, DividedAmt
,TotalAmt=SUM(DividedAmt) OVER (PARTITION BY ID)
FROM
(
SELECT ID, Amount, Months, n
,DividedAmt=CAST(Amount/Months AS DECIMAL(10,2))
FROM SampleData a
CROSS APPLY
(
SELECT TOP (Months) n
FROM Tally
ORDER BY n
) b
) a
) a;
Note that most of the work (in fact both of the CTEs and the CROSS APPLY of the inner query, is just setting up the data/exploding the rows.
Edit: Looking back I see that this solution is quite similar to Luisi's. Should have looked before I leaped.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 30, 2015 at 8:16 am
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
January 30, 2015 at 11:03 am
asm1212 (1/30/2015)
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
Sure, I'll try to explain.
The SELECT 1 UNION ALL are just generating rows. If you run the code inside the first CTE, you'll get 12 rows with a single column having always 1. The value is not important, we just need the rows.
WITH E(n) is declaring a CTE named E which will have only one column named n.
FROM E a, E b is doing a CROSS JOIN with the CTE to go from 12 rows to 144 (12*12 or 12^2). This is the old join syntax defined by standard SQL-86, while CROSS JOIN is SQL-92 version. E is the name of the CTE and a and b are alias.
Is this clear enough? I'd suggest you to comment every part that you might have trouble remembering so you can immediately remember what it's doing every time you look at the code.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply