Drumroll please! The winner of the July SQL Code Challenge is...Greg B.! Greg's solution might not be the most elegant way to handle the remainder but it is effective. His code is below.
CREATE PROCEDURE prCreatePaymentSchedule(@TotalSaleAmount Decimal(12,2), @TaxRate Decimal(12,2), @NumPaymentsToSpan int)
AS
BEGIN
SET NOCOUNT ON
Declare @Start int --Used for while loop
Declare @SumValue Decimal(12,2)
Create Table #TempPayment (ID int Identity(1,1), PreTaxPayment decimal(12,2), TaxAmount decimal(12,2), TotalPayment Decimal(12,2))
Set @Start = 1
While @Start <= @NumPaymentsToSpan
BEGIN
--In cases when the amount is small and num payments is WAAAYYY out there this will
--prevent negatives from happening, but the last payment may be bigger than the others
IF (Select SUM(totalPayment) + (@TotalSaleAmount / @NumPaymentsToSpan) From #TempPayment) > @TotalSaleAmount
BEGIN
Insert into #TempPayment (TotalPayment)
Select @TotalSaleAmount - Sum(totalPayment) FROM #TempPayment
Set @Start = @NumPaymentsToSpan --Set @Start to the num of payments so it doesn't look again
END
ELSE If @Start = @NumPaymentsToSpan --If we are on the last payment then take do the math on what is in the table to get the last payment.
BEGIN
Insert into #TempPayment (TotalPayment)
Select @TotalSaleAmount - Sum(totalPayment) FROM #TempPayment
END
ELSE
BEGIN
--Case to do the "even" thing
--If whole number leave it alone
--If even leave it alone
--If odd then add .01 to make it even.
Insert into #TempPayment (TotalPayment)
Select Case
When ceiling((@TotalSaleAmount / @NumPaymentsToSpan)) = (@TotalSaleAmount /@NumPaymentsToSpan) Then (@TotalSaleAmount/@NumPaymentsToSpan) --Whole Number, leave alone
When (Cast((@TotalSaleAmount / @NumPaymentsToSpan) % 2 as int)) = 0 Then (@TotalSaleAmount/@NumPaymentsToSpan) --Even leave calc alone
When (Cast((@TotalSaleAmount / @NumPaymentsToSpan) % 2 as int)) = 1 Then (@TotalSaleAmount/@NumPaymentsToSpan) + .01 --Odd add .01
END
END
Set @Start = @Start + 1 --Increase Start
END
--Update #TempPayment for TaxAmount Based on Total Payment and Passed in TaxRate
--Update #TempPayment for PreTaxpayment Based on TaxAmount and total Payment.
Update #TempPayment
Set TaxAmount = (TotalPayment - (TotalPayment / (1+@TaxRate))),
PreTaxPayment = TotalPayment - (TotalPayment - (TotalPayment / (1+@TaxRate)))
--Return the recordset
Select ID, PreTaxPayment, TaxAmount, TotalPayment From #TempPayment Order by ID ASC
SET NOCOUNT OFF
END
I do have to mention that Chad H. had the most elegant solution by using the ROW_NUMBER and OVER clause. His code is below.
-- Assumes the following table exists
CREATE TABLE dbo.tmp_Payments
( ID INT
,PreTaxPayment DECIMAL(32,2)
,TaxAmount DECIMAL(32,5)
,TotalPayment DECIMAL(32,2)
)
GO
if OBJECTPROPERTY(object_id('dbo.usp_Payments_INS'), 'IsProcedure') = 1
Drop Procedure usp_Payments_INS
GO
/*
dbo.usp_GenPayments
Calcualtes payment and tax information for a total value.
Explanation of Approach
As a DBA, I generally create a physical "integer Dim table" that contains numbers 1 - 73048
(AKA 01/01/1900 - 12/31/2099) that I use for just an occasion, but since this not mentioned
in the challenge I will generate it in the stored procedure (NOT a best practice)
CREATE TABLE Int_Dim
( Val INT )
Val
---
1
2
3
4
.
.
.
Modifications
07/01/2009 Chad H. - Created
*/
CREATE PROCEDURE dbo.usp_Payments_INS
( @Total DECIMAL(32,2) -- Total amount charged
,@Tax Decimal(3,3) -- Tax amount (assumes decimal version)
,@Payments INT -- Number of Payments
)
AS
BEGIN
SET NOCOUNT ON
-- Start of Integer Dimision table
-- Create a table of "string digits" to assemble integers with
DECLARE @Chars Table ( val Char(1) )
INSERT @Chars Values ('1')
INSERT @Chars Values ('2')
INSERT @Chars Values ('3')
INSERT @Chars Values ('4')
INSERT @Chars Values ('5')
INSERT @Chars Values ('6')
INSERT @Chars Values ('7')
INSERT @Chars Values ('8')
INSERT @Chars Values ('9')
INSERT @Chars Values ('0')
-- Generate Integer Dimision table
DECLARE @IntDim Table ( val INT )
INSERT @IntDim
SELECT c1.Val + c2.Val + c3.Val + c4.Val + c5.Val
FROM @Chars c1
CROSS JOIN @Chars c2
CROSS JOIN @Chars c3
CROSS JOIN @Chars c4
CROSS JOIN @Chars c5
WHERE CONVERT(INT, c1.Val + c2.Val + c3.Val + c4.Val + c5.Val) between 1 AND @Payments -- Limit table to number of payments
-- End of Integer Dim table (Real sp code starts here)
DECLARE @TotalCnts INT
,@GrandTotalCnts INT
,@TotalTaxCnts INT
-- Change to integer since mudulo expects and returns integer
SET @TotalCnts = @Total * 100
SET @GrandTotalCnts = ((@Total * @Tax) + @Total) * 100
SET @TotalTaxCnts = (@Total * @Tax) * 100
-- Insert records in single insert statement
INSERT dbo.tmp_Payments (ID, PreTaxPayment, TaxAmount, TotalPayment)
SELECT
ROW_NUMBER() OVER(order by @Payments) as ID
,CASE i.val
WHEN @Payments THEN ((@TotalCnts / @Payments) + (@TotalCnts % @Payments)) / 100.00
ELSE (@TotalCnts / @Payments) / 100.00
END as Payment
,CASE i.val WHEN @Payments THEN ((@TotalTaxCnts / @Payments) + (@TotalTaxCnts % @Payments)) / 100.00
ELSE (@TotalTaxCnts / @Payments) / 100.00
END as Tax
,CASE i.Val
WHEN @Payments THEN ((@GrandTotalCnts / @Payments) + (@GrandTotalCnts % @Payments)) / 100.00
ELSE (@GrandTotalCnts / @Payments) / 100.00
END as TotalPayment
FROM @IntDim i
END
GO
-- USAGE:
BEGIN TRAN
DECLARE
@Total DECIMAL(32,2)
,@Tax DECIMAL(3,3)
,@Payments INT
SET @Total = 106
SET @Tax = .06
SET @Payments = 3
EXEC usp_Payments_INS
@Total = @Total
,@Tax = @Tax
,@Payments = @Payments
SELECT
SUM(T1.PreTaxPayment)
,SUM(T1.TaxAmount)
,@Total * @Tax
,SUM(T1.TotalPayment)
,(@Total * @Tax) + @Total
FROM tmp_Payments T1
ROLLBACK TRAN
GO
-- USAGE:
BEGIN TRAN
DECLARE
@Total DECIMAL(32,2)
,@Tax DECIMAL(3,3)
,@Payments INT
SET @Total = 1
SET @Tax = .12
SET @Payments = 20
EXEC usp_Payments_INS
@Total = @Total
,@Tax = @Tax
,@Payments = @Payments
SELECT
SUM(T1.PreTaxPayment)
,SUM(T1.TaxAmount)
,@Total * @Tax
,SUM(T1.TotalPayment)
,(@Total * @Tax) + @Total
FROM tmp_Payments T1
ROLLBACK TRAN
GO
-- USAGE -- Ver large number:
BEGIN TRAN
DECLARE
@Total DECIMAL(32,2)
,@Tax DECIMAL(3,3)
,@Payments INT
SET @Total = 1897279.65
SET @Tax = .25
SET @Payments = 234
EXEC usp_Payments_INS
@Total = @Total
,@Tax = @Tax
,@Payments = @Payments
SELECT
SUM(T1.PreTaxPayment)
,SUM(T1.TaxAmount)
,@Total * @Tax
,SUM(T1.TotalPayment)
,(@Total * @Tax) + @Total
FROM tmp_Payments T1
ROLLBACK TRAN
GO
Chad, like most of our participants, missed the last requirement which states, "any remainder should be evenly split among all payment amounts, with the final payment amount reduced accordingly." Sorry guys, meeting requirements is priority number 1. Regardless of who won, it is great to see all of you taking an interest in our challenges and we look forward to receiving your submittals for our August Code Challenge. Which, by the way, should be posted in the next day or two.
If you have any comments or need clarification on something, let me know. Greg B. your prize is on its way!!
Share or Bookmark this post…