October 29, 2008 at 6:30 am
I'm using SQL Server 2000...
ok, so my outer query creates the [next pay date] using the tally table, so 1 record now has however many records until maturity date... (like below)... as you can guess, at each new paydate, i need to track the [balance after payment] so when i get to the next month, i can do the calculations over again...
I am trying to do this in 1 query (set based is it?)... currently I have a While loop and it took 45 minutes to calc all the [principal payments] and then i grab the that for each record.... so the Aggregate came back with 147,773 records
[next pay date] [inerest payment] [other thing] [inerest payment] [principal payment] [balance after payment]
2008-09-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2008-10-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2008-11-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2008-12-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2009-01-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2009-02-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2009-03-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2009-04-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2009-05-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2009-06-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2009-07-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2009-08-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2009-09-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2009-10-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2009-11-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2009-12-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2010-01-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2010-02-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2010-03-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2010-04-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2010-05-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2010-06-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2010-07-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2010-08-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2010-09-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2010-10-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2010-11-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2010-12-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2011-01-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2011-02-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2011-03-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2011-04-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2011-05-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2011-06-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2011-07-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2011-08-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2011-09-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2011-10-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2011-11-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2011-12-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2012-01-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2012-02-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2012-03-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2012-04-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2012-05-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2012-06-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2012-07-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2012-08-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2012-09-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2012-10-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2012-11-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2012-12-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2013-01-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2013-02-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2013-03-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2013-04-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2013-05-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
2013-06-25 00:00:00.0003303.85236217137NULL3303.85236217137859.90763782863482459.542362171
October 29, 2008 at 8:00 am
The answer is 42.
If that doesn't help you, post some DDL / Sample Data / Expected Results and how you want to manipulate your data to get there.
Also, having everything in one query does not make it set based. Using the word "loop" in the same room as your query does pretty much ensure that it is not though.
October 29, 2008 at 10:22 am
I had to get something together that was legable for you all...
In the query off to the side i put what is constants...
for each result I need to then do the amortization so that I can SUM([p_principal_pmt])
notice in my query that [tot pmt] and [Prin pmt] will flip flop by the case statement [iEval]...
So one of those fields will always be constant
So the ending Balance now has to become the Beggining Balance for the next month... and that should be the only thing i need to continue the amortization for each record... that begging balance is the Col [CUR_PAR_BAL] alias [v_bal_before_pmt] now somehow needs to be the [Beg Bal] from the Results...
THANKS...
John
RESULTS:
v_next_pmt_date483319.454163.763303.85236217137859.90763782863480015.597637829
v_next_pmt_date300003466.49401875133.164018753333.3326666.67
INNER QUERY DATA:
1483319.454163.763v_total_pmt4163.768.0850000.08085000008.454858333333333303.85236217137p_interest_pmt/p_principal_pmt3303.85236217137859.90763782863480015.597637829
2300003333.332p_principal_pmt3333.335.2500000.05250000008.45485833333333133.16401875v_total_pmt3466.494018753333.3326666.67
QUERY:
SELECT
--*
'v_next_pmt_date' AS [Date],[A].[v_bal_before_pmt]AS [Beg Bal],
(CASE [A].[iEVAL]
WHEN 1 THEN [A].[PARAM_FIELD_VALUE_SET]
WHEN 2 THEN [A].[PARAM_FIELD_VALUE_SET]
WHEN 3 THEN [A].[CUR_PAYMENT]
END)AS [Tot Pmt],
(CASE [A].[iEVAL]
WHEN 1 THEN [A].[p_interest_pmt]
WHEN 2 THEN [A].[p_interest_pmt]
WHEN 3 THEN [A].[PARAM_FIELD_VALUE_SET]
END)AS [Int Pmt],
[A].[p_principal_pmt]AS [Prin Pmt],
[A].[p_bal_after_pmt/ENDING_BAL]AS [End Bal]
FROM
(
SELECT
[ID_NUMBER]
,CAST ([CUR_PAR_BAL] AS FLOAT)AS [v_bal_before_pmt]-- THIS CHANGES DURING THE LOOP
,CAST ([CUR_PAYMENT] AS FLOAT)AS [CUR_PAYMENT]
,XJASN4N.fn_RUNOFF_CONSTANTS([AMRT_TYPE_CD])AS [iEVAL]-- CONSTANT
,(CASE XJASN4N.fn_RUNOFF_CONSTANTS([AMRT_TYPE_CD])
WHEN 1 THEN 'p_principal_pmt'
WHEN 2 THEN 'p_principal_pmt' --[CUR_PAR_BAL]
WHEN 3 THEN 'v_total_pmt'
END)AS [PARAM_FIELD_SET]-- CONSTANT
,(CASE XJASN4N.fn_RUNOFF_CONSTANTS([AMRT_TYPE_CD])
WHEN 1 THEN 0
WHEN 2 THEN ABS([CUR_PAYMENT]) --[CUR_PAR_BAL]
WHEN 3 THEN ABS([CUR_PAYMENT]) --[CUR_PAR_BAL]
END)AS [PARAM_VALUE]-- CONSTANT
,[CUR_GROSS_RATE]
,[CUR_GROSS_RATE]/ 100AS [p_cur_gross_rate]-- CONSTANT
,XJASN4N.fn_Calc_Cur_Pmt_Rate([PMT_FREQ_MULT]
,[ACCRUAL_BASIS_CD]
,CAST([PMT_FREQ] AS INT))AS [p_pmt_int_rate]-- CONSTANT
,XJASN4N.p_interest_pmt(CAST([CUR_PAR_BAL] AS FLOAT),
CAST([CUR_GROSS_RATE]/ 100 AS FLOAT),
XJASN4N.fn_Calc_Cur_Pmt_Rate([PMT_FREQ_MULT]
,[ACCRUAL_BASIS_CD]
,CAST([PMT_FREQ] AS INT)))AS [p_interest_pmt]-- THIS CHANGES DURING THE LOOP
,(CASE XJASN4N.fn_RUNOFF_CONSTANTS([AMRT_TYPE_CD])
WHEN 1 THEN 'v_total_pmt'
WHEN 2 THEN 'v_total_pmt' --[CUR_PAR_BAL]
WHEN 3 THEN 'p_interest_pmt/p_principal_pmt'
END)AS [PARAM_FIELD_TO_SET]-- CONSTANT
,(XJASN4N.fn_PARAM_VALUE([CUR_PAYMENT], [AMRT_TYPE_CD],
XJASN4N.p_interest_pmt(CAST([CUR_PAR_BAL] AS FLOAT),
CAST([CUR_GROSS_RATE] / 100 AS FLOAT),
XJASN4N.fn_Calc_Cur_Pmt_Rate([PMT_FREQ_MULT]
,[ACCRUAL_BASIS_CD]
,CAST([PMT_FREQ] AS INT)))
-- CUR_PAYMENT = @p_principal_pmt AND @v_total_pmt
, CUR_PAYMENT, CUR_PAYMENT))AS [PARAM_FIELD_VALUE_SET] -- 2 PURPOSE NEXT_ROUND_VALUES AND IF CASE 3 SET @p_principal_pmt
-- USE ABOVE ONLY IF IEVAL = 3 ELSE NULL
,(CASE XJASN4N.fn_RUNOFF_CONSTANTS(AMRT_TYPE_CD)
WHEN 3 THEN [CUR_PAYMENT] - XJASN4N.p_interest_pmt(CAST([CUR_PAR_BAL] AS FLOAT),
CAST([CUR_GROSS_RATE]/ 100 AS FLOAT),
XJASN4N.fn_Calc_Cur_Pmt_Rate([PMT_FREQ_MULT]
,[ACCRUAL_BASIS_CD]
,CAST([PMT_FREQ] AS INT)))
ELSE [CUR_PAYMENT]
END)AS [p_principal_pmt]
--, @v_bal_before_pmt - @p_principal_pmt = CUR_PAYMENT FOR CASE 1 OR 2, IF 3 USE ABOVE [p_principal_pmt]AS [p_bal_after_pmt/ENDING_BAL]
--, CUR_PAR_BAL - @p_principal_pmtAS [p_bal_after_pmt/ENDING_BAL]
,(CASE XJASN4N.fn_RUNOFF_CONSTANTS(AMRT_TYPE_CD)
WHEN 1 THEN [CUR_PAR_BAL] - [CUR_PAYMENT]
WHEN 2 THEN [CUR_PAR_BAL] - [CUR_PAYMENT]
WHEN 3 THEN [CUR_PAR_BAL] - XJASN4N.p_interest_pmt(CAST([CUR_PAR_BAL] AS FLOAT),
CAST([CUR_GROSS_RATE]/ 100 AS FLOAT),
XJASN4N.fn_Calc_Cur_Pmt_Rate([PMT_FREQ_MULT]
,[ACCRUAL_BASIS_CD]
,CAST([PMT_FREQ] AS INT)))
END)AS [p_bal_after_pmt/ENDING_BAL]
FROM
[tbl] A
WHERE [CUR_PAYMENT] <> 0 AND [CUR_PAR_BAL] <> 0
--AND ID_NUMBER = 100000890510052367513
--AND PMT_FREQ_MULT = 'M'
) [A]
October 29, 2008 at 12:36 pm
I've updated my code, still not sure how to repeat the process...
2 RESULTS:
12008-09-25 00:00:00.000483319.454163.763303.85236217137859.90763782863482459.5423621714163.768.454858333333330.08085
22008-12-09 00:00:00.000300003466.49401875133.164018753333.3326666.673333.338.454858333333330.0525
QUERY:
SELECT
[ID_NUMBER]
,[NEXT_PAYMENT_DATE]
,CAST ([CUR_PAR_BAL] AS FLOAT)AS [Begin Balance] --v_bal_before_pmt]-- THIS CHANGES DURING THE LOOP
,[XJASN4N].[fn_TOTAL_PAYMENT](XJASN4N.fn_RUNOFF_CONSTANTS([AMRT_TYPE_CD])
, CAST([CUR_PAR_BAL] AS FLOAT)
, CAST([CUR_GROSS_RATE] / 100 AS FLOAT)
, CAST(XJASN4N.fn_Calc_Cur_Pmt_Rate([PMT_FREQ_MULT]
,[ACCRUAL_BASIS_CD]
,CAST([PMT_FREQ] AS INT)) AS FLOAT)
, [CUR_PAYMENT])AS [TOTAL_PAYMENT]
,XJASN4N.p_interest_pmt(CAST([CUR_PAR_BAL] AS FLOAT),
CAST([CUR_GROSS_RATE]/ 100 AS FLOAT),
XJASN4N.fn_Calc_Cur_Pmt_Rate([PMT_FREQ_MULT]
,[ACCRUAL_BASIS_CD]
,CAST([PMT_FREQ] AS INT)))AS [INT_PAYMENT]-- THIS CHANGES DURING THE LOOP
,[XJASN4N].[fn_PRINCIPAL_PAYMENT](XJASN4N.fn_RUNOFF_CONSTANTS([AMRT_TYPE_CD])
, CAST([CUR_PAR_BAL] AS FLOAT)
, CAST([CUR_GROSS_RATE] / 100 AS FLOAT)
, CAST(XJASN4N.fn_Calc_Cur_Pmt_Rate([PMT_FREQ_MULT]
,[ACCRUAL_BASIS_CD]
,CAST([PMT_FREQ] AS INT)) AS FLOAT)
, [CUR_PAYMENT])AS [PRINCIPAL_PAYMENT]
,[XJASN4N].[fn_ENDING_BALANCE]([CUR_PAR_BAL]
-- PRINCIPAL PAYMENT
,[XJASN4N].[fn_PRINCIPAL_PAYMENT](XJASN4N.fn_RUNOFF_CONSTANTS([AMRT_TYPE_CD])
, CAST([CUR_PAR_BAL] AS FLOAT)
, CAST([CUR_GROSS_RATE] / 100 AS FLOAT)
, CAST(XJASN4N.fn_Calc_Cur_Pmt_Rate([PMT_FREQ_MULT]
,[ACCRUAL_BASIS_CD]
,CAST([PMT_FREQ] AS INT)) AS FLOAT)
, [CUR_PAYMENT]))AS [ENDING BALANCE]
,CAST ([CUR_PAYMENT] AS FLOAT)AS [P_CUR_PAYMENT]
,XJASN4N.fn_Calc_Cur_Pmt_Rate([PMT_FREQ_MULT]
,[ACCRUAL_BASIS_CD]
,CAST([PMT_FREQ] AS INT))AS [P_PAYMENT_INT_RATE]
,CAST([CUR_GROSS_RATE] / 100 AS FLOAT)AS [P_GROSS_RATE]
FROM
[tbl] A
WHERE [CUR_PAYMENT] <> 0 AND [CUR_PAR_BAL] <> 0
October 29, 2008 at 12:52 pm
Sounds like a Running Totals type query. Please read this article written by Jeff Moden, it should help you.
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
October 29, 2008 at 1:26 pm
Well I thought I understood it until I ran the very last part...
I'm not seeing the connection just yet???
October 29, 2008 at 8:01 pm
Solution eventually reached at the following URL...
http://www.sqlservercentral.com/Forums/Topic593999-8-1.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
October 29, 2008 at 8:18 pm
I knew you would post...lol
I'll check it out...
But i did figure it out, see my other post
Running Total isn't working in the UPDATE CLAUSE... doesn't that look familiar (Running Total; oh yeah!!!)
October 29, 2008 at 9:06 pm
John, aren't you glad I pointed you to Jeff's article?
Jeff, I guess I should add that one to my sig block as well.
October 29, 2008 at 10:15 pm
jsteinbeck (10/29/2008)
I knew you would post...lolI'll check it out...
But i did figure it out, see my other post
Running Total isn't working in the UPDATE CLAUSE... doesn't that look familiar (Running Total; oh yeah!!!)
Heh... the link I posted is to your other post. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 29, 2008 at 10:16 pm
Lynn Pettis (10/29/2008)
Jeff, I guess I should add that one to my sig block as well.
Seems like there have been a lot of reasons to use it, lately.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2008 at 5:34 am
Very glad you pointed it out... Ironicly I had found it the other night, not knowing how significant it was or would be for me...
THANKS...
October 30, 2008 at 6:35 pm
No problem... thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2008 at 6:45 pm
Hey,
thought you might be interested in my stats...
My first attempt at this Amortization was doing a loop to create 3 Mill + Records took 45 Mins
Then I did an insert calculating the Columns as they went in... 17 Mins just for that, not including the Update to do the Amortization...
So to kill the 17 Min, I read an Article in here talking about Temp In-Place; using the table you already have...
So here is the complete code, and the Stats... I would hope that i could speed up the Amortization Update... Also, I had to Create a 2nd Stored Procedure for Amortization Update, because the WITH INDEX caused an error when going to EXE; stated Index didn't exist, which it shouldn't...
So it took about 13 Minutes total to do the Amortization table... cool, cool...
3 Seconds Elapsed Time: STAGE1:
24 Seconds Elapsed Time: STAGE2:
51 Seconds Elapsed Time: STAGE3:
41 Seconds Elapsed Time: STAGE4:
668 Seconds Elapsed Time: STAGE5:
USE [CPMTest]
GO
/****** Object: StoredProcedure [XJASN4N].[p_RUNOFF4] Script Date: 10/30/2008 18:14:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [XJASN4N].[p_RUNOFF4]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- KILL IT IF IT EXISTS....
IF OBJECT_ID('[XJASN4N].[tblCLS_OFSA]','U') IS NOT NULL
DROP TABLE [XJASN4N].[tblCLS_OFSA]
-- BUILD OUR STAGING TABLE
CREATE TABLE [CPMTEST].[XJASN4N].[tblCLS_OFSA]
(
--ROWIDINT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
[ID_NUMBER]DECIMAL(25, 0) NOT NULL
,BANK_IDDECIMAL(14, 0)
,BANK_CODEVARCHAR(5)
,PMT_FREQINT
,PMT_FREQ_MULTCHAR(1)
,NEXT_PAYMENT_DATEDATETIME
,ACCRUAL_BASIS_CDINT
,AMRT_TYPE_CDDECIMAL(5, 0)
,CUR_GROSS_RATEFLOAT
,CUR_PAR_BALFLOAT
,CUR_PAYMENTFLOAT
,MATURITY_DATEDATETIME
,LAST_PAYMENT_DATEDATETIME
,[@iEVAL]INT
,OBLIGORVARCHAR(100)
,OBLIGATIONVARCHAR(100)
,[Begin_Balance]FLOAT
,[TOTAL_PAYMENT]FLOAT
,[INT_PAYMENT]FLOAT
,[PRINCIPAL_PAYMENT]FLOAT
,[ENDING_BALANCE]FLOAT
,[P_CUR_PAYMENT]FLOAT
,[P_PAYMENT_INT_RATE]FLOAT
,[P_GROSS_RATE]FLOAT
,[P_MATURITY_DATE]DATETIME
,[NEXT_PAY_DATE2]DATETIME
,[RunBal]FLOAT NULL
,[GrpBal]FLOAT NULL
,[RunCnt]INT NULL
,[GrpCnt]INT NULL
)
--DECLARATIONS
DECLARE @ROUTINEVARCHAR(100)
DECLARE @ERR_NUMINT
SET @ERR_NUM= 0
DECLARE @iRETURNINT
SET @iRETURN= 0
DECLARE @UDT_ERR_MSGVARCHAR(200)
DECLARE @PARAMNAMEVARCHAR(200)
DECLARE @START_TIMEDATETIME
DECLARE @END_TIMEVARCHAR(100)
-- WE'RE GOING TO INSERT ONLY THE DATA WE NEED FROM THE DISTANT TABLE...
--truncate table [CPMTEST].[XJASN4N].[tblCLS_OFSA]
STAGE1:
SET @START_TIME = GETDATE() --Start the timer
BEGIN TRAN
INSERT [CPMTEST].[XJASN4N].[tblCLS_OFSA]
(
ID_NUMBER, BANK_ID, BANK_CODE, PMT_FREQ, PMT_FREQ_MULT, NEXT_PAYMENT_DATE
, ACCRUAL_BASIS_CD, AMRT_TYPE_CD, CUR_GROSS_RATE, CUR_PAR_BAL, CUR_PAYMENT
, MATURITY_DATE, LAST_PAYMENT_DATE
)
SELECT
ID_NUMBER, BANK_ID, BANK_CODE, PMT_FREQ, PMT_FREQ_MULT, NEXT_PAYMENT_DATE
, ACCRUAL_BASIS_CD, AMRT_TYPE_CD, CUR_GROSS_RATE, CUR_PAR_BAL, CUR_PAYMENT
, MATURITY_DATE, LAST_PAYMENT_DATE
FROM
[tbl]
WHERE
[AMRT_TYPE_CD] <> 999
AND
[MATURITY_DATE] > [NEXT_PAYMENT_DATE]
--AND
--[CUR_PAYMENT] <> 0 ???
--AND
--[CUR_PAR_BAL] <> 0
-- ERR ROUTINE
SET @ERR_NUM = @@ERROR
SET @ROUTINE = 'STAGE1: ERROR...'
IF (@ERR_NUM <> 0) GOTO PROGRAM_ERROR
COMMIT TRAN
SELECT @END_TIME = CAST((DATEDIFF(SS,@START_TIME,GETDATE()))AS VARCHAR(100)) + ' Seconds Elapsed Time: ' + @ROUTINE
PRINT @END_TIME
STAGE2:
SET @START_TIME = GETDATE() --Start the timer
-- Temp In-Place RATHER THAN ANOTHER TEMP TABLE...
--NOW WE ARE GOING TO CALCULATE OUR VARIABLES WE NEED TO FIGURE PRINCIPAL PAYMENT FOR EACH ID_NUM 65000 ROWS
BEGIN TRAN
UPDATE[CPMTEST].[XJASN4N].[tblCLS_OFSA]
SET
[OBLIGOR] =(CASE
WHEN (ISNUMERIC(BANK_CODE)=1 AND BANK_CODE = 1)
THEN SUBSTRING (CONVERT (char, ID_NUMBER), 2,10)
ELSE SUBSTRING (CONVERT (char, ID_NUMBER), 3,10)
END),
[OBLIGATION] = (CASE
WHEN (ISNUMERIC(BANK_CODE)=1 AND BANK_CODE = 1)
THEN SUBSTRING (CONVERT (char, ID_NUMBER), 12,10)
ELSE SUBSTRING (CONVERT (char, ID_NUMBER), 13,10)
END),
[BEGIN_BALANCE] = CAST ([CUR_PAR_BAL] AS FLOAT), -- may not need since in table already
[TOTAL_PAYMENT] = [XJASN4N].[fn_TOTAL_PAYMENT](XJASN4N.fn_RUNOFF_CONSTANTS([AMRT_TYPE_CD])
, CAST([CUR_PAR_BAL] AS FLOAT)
, CAST([CUR_GROSS_RATE] / 100 AS FLOAT)
, CAST(XJASN4N.fn_Calc_Cur_Pmt_Rate([PMT_FREQ_MULT]
,[ACCRUAL_BASIS_CD]
,CAST([PMT_FREQ] AS INT)) AS FLOAT)
, CAST([CUR_PAYMENT] AS FLOAT)),
[INT_PAYMENT] = XJASN4N.p_interest_pmt(CAST([CUR_PAR_BAL] AS FLOAT),
CAST([CUR_GROSS_RATE]/ 100 AS FLOAT),
XJASN4N.fn_Calc_Cur_Pmt_Rate([PMT_FREQ_MULT]
,[ACCRUAL_BASIS_CD]
,CAST([PMT_FREQ] AS INT))),
-- THIS CHANGES DURING THE LOOP
[PRINCIPAL_PAYMENT] = [XJASN4N].[fn_PRINCIPAL_PAYMENT](XJASN4N.fn_RUNOFF_CONSTANTS([AMRT_TYPE_CD])
, CAST([CUR_PAR_BAL] AS FLOAT)
, CAST([CUR_GROSS_RATE] / 100 AS FLOAT)
, CAST(XJASN4N.fn_Calc_Cur_Pmt_Rate([PMT_FREQ_MULT]
,[ACCRUAL_BASIS_CD]
,CAST([PMT_FREQ] AS INT)) AS FLOAT)
, CAST([CUR_PAYMENT] AS FLOAT)),
[ENDING_BALANCE] = [XJASN4N].[fn_ENDING_BALANCE]([CUR_PAR_BAL]
-- PRINCIPAL PAYMENT
,[XJASN4N].[fn_PRINCIPAL_PAYMENT](XJASN4N.fn_RUNOFF_CONSTANTS([AMRT_TYPE_CD])
, CAST([CUR_PAR_BAL] AS FLOAT)
, CAST([CUR_GROSS_RATE] / 100 AS FLOAT)
, CAST(XJASN4N.fn_Calc_Cur_Pmt_Rate([PMT_FREQ_MULT]
,[ACCRUAL_BASIS_CD]
,CAST([PMT_FREQ] AS INT)) AS FLOAT)
,CAST([CUR_PAYMENT] AS FLOAT))),
[P_CUR_PAYMENT] = CAST([CUR_PAYMENT] AS FLOAT), -- may not need since in table already
[P_PAYMENT_INT_RATE] = XJASN4N.fn_Calc_Cur_Pmt_Rate([PMT_FREQ_MULT]
,[ACCRUAL_BASIS_CD]
,CAST([PMT_FREQ] AS INT)),
[P_GROSS_RATE] = CAST([CUR_GROSS_RATE]/ 100 AS FLOAT),
[@iEVAL] = XJASN4N.fn_RUNOFF_CONSTANTS([AMRT_TYPE_CD]),
[P_MATURITY_DATE] = [MATURITY_DATE] -- may not need since in table already
FROM
[CPMTEST].[XJASN4N].[tblCLS_OFSA] A
-- ERR ROUTINE
SET @ERR_NUM = @@ERROR
SET @ROUTINE = 'STAGE2: ERROR...'
IF (@ERR_NUM <> 0) GOTO PROGRAM_ERROR
COMMIT TRAN
SELECT @END_TIME = CAST((DATEDIFF(SS,@START_TIME,GETDATE()))AS VARCHAR(100)) + ' Seconds Elapsed Time: ' + @ROUTINE
PRINT @END_TIME
STAGE3:
SET @START_TIME = GETDATE() --Start the timer
-- Temp In-Place RATHER THAN ANOTHER TEMP TABLE...
-- NOW WE ARE GOING TO ADD ROWS FOR EACH RECORD... DATEDIFF(DAY, [A].[NEXT_PAYMENT_DATE], [A].[MATURITY_DATE]) + 1
-- ADDING THESE ROWS HERE BEFORE WE BUILD INDEX... SLOW IF DO BEFORE...
-- 3.3 Million Rows Created...
BEGIN TRAN
INSERT [CPMTEST].[XJASN4N].[tblCLS_OFSA]
(
[A].ID_NUMBER , [A].BANK_ID, [A].BANK_CODE, [A].PMT_FREQ, [A].PMT_FREQ_MULT, [A].NEXT_PAYMENT_DATE
, [A].ACCRUAL_BASIS_CD, [A].AMRT_TYPE_CD, [A].CUR_GROSS_RATE, [A].CUR_PAR_BAL
, [A].CUR_PAYMENT, [A].MATURITY_DATE, [A].LAST_PAYMENT_DATE, [A].[@iEVAL], [A].OBLIGOR
, [A].OBLIGATION, [A].[Begin_Balance], [A].[TOTAL_PAYMENT], [A].[INT_PAYMENT]
, [A].[PRINCIPAL_PAYMENT], [A].[ENDING_BALANCE], [A].[P_CUR_PAYMENT], [A].[P_PAYMENT_INT_RATE]
, [A].[P_GROSS_RATE], [A].[P_MATURITY_DATE]
)
SELECT
ID_NUMBER ,BANK_ID ,BANK_CODE ,PMT_FREQ,PMT_FREQ_MULT,NEXT_PAYMENT_DATE,ACCRUAL_BASIS_CD,AMRT_TYPE_CD
,CUR_GROSS_RATE,CUR_PAR_BAL ,CUR_PAYMENT,MATURITY_DATE,LAST_PAYMENT_DATE
,[@iEVAL],OBLIGOR,OBLIGATION
,[Begin_Balance],[TOTAL_PAYMENT],[INT_PAYMENT],[PRINCIPAL_PAYMENT],[ENDING_BALANCE],[P_CUR_PAYMENT],[P_PAYMENT_INT_RATE]
,[P_GROSS_RATE],[P_MATURITY_DATE]
FROM
DBO.TALLY T
CROSS JOIN
(
SELECT
ID_NUMBER ,BANK_ID ,BANK_CODE ,PMT_FREQ,PMT_FREQ_MULT,NEXT_PAYMENT_DATE,ACCRUAL_BASIS_CD,AMRT_TYPE_CD
,CUR_GROSS_RATE,CUR_PAR_BAL ,CUR_PAYMENT,MATURITY_DATE,LAST_PAYMENT_DATE
,[@iEVAL],OBLIGOR,OBLIGATION
,[Begin_Balance],[TOTAL_PAYMENT],[INT_PAYMENT],[PRINCIPAL_PAYMENT],[ENDING_BALANCE],[P_CUR_PAYMENT],[P_PAYMENT_INT_RATE]
,[P_GROSS_RATE],[P_MATURITY_DATE]
FROM
[CPMTEST].[XJASN4N].[tblCLS_OFSA] A
) [A]
WHERE T.N < =
(CASE [A].[PMT_FREQ_MULT]
WHEN 'M' THEN DATEDIFF(MONTH, [A].[NEXT_PAYMENT_DATE], [A].[MATURITY_DATE]) + 1
WHEN 'D' THEN DATEDIFF(DAY, [A].[NEXT_PAYMENT_DATE], [A].[MATURITY_DATE]) + 1
END )
-- ERR ROUTINE
SET @ERR_NUM = @@ERROR
SET @ROUTINE = 'STAGE3: ERROR...'
IF (@ERR_NUM <> 0) GOTO PROGRAM_ERROR
COMMIT TRAN
SELECT @END_TIME = CAST((DATEDIFF(SS,@START_TIME,GETDATE()))AS VARCHAR(100)) + ' Seconds Elapsed Time: ' + @ROUTINE
PRINT @END_TIME
STAGE4:
SET @START_TIME = GETDATE() --Start the timer
-- THIS CLUSTER ALOWS US NOT TO MESS UP WHEN DOING RUNNING COUNT AND CALCS...
BEGIN TRAN
CREATE CLUSTERED INDEX IX_tblCLS_OFSA_ID_BI_BC --clustered to resolve "Merry-go-Round"
ON [CPMTEST].[XJASN4N].[tblCLS_OFSA] ([ID_NUMBER], [BANK_ID], [BANK_CODE])
-- ERR ROUTINE
SET @ERR_NUM = @@ERROR
SET @ROUTINE = 'STAGE4: ERROR...'
IF (@ERR_NUM <> 0) GOTO PROGRAM_ERROR
COMMIT TRAN
SELECT @END_TIME = CAST((DATEDIFF(SS,@START_TIME,GETDATE()))AS VARCHAR(100)) + ' Seconds Elapsed Time: ' + @ROUTINE
PRINT @END_TIME
STAGE5:
SET @START_TIME = GETDATE() --Start the timer
-- Temp In-Place RATHER THAN ANOTHER TEMP TABLE...
-- THIS IS THE BULK OF THE WORK IN FIGURING THE AMERITIZATION OF A LOAN...
-- NOT DOING A LOOP AS BEFORE... 45MINS, SO DOING UPDATE SET BASED QUERY HANDLES EACH ROW AS IT COMES IN
EXEC XJASN4N.p_RUNOFF4_STAGE5
SELECT @END_TIME = CAST((DATEDIFF(SS,@START_TIME,GETDATE()))AS VARCHAR(100)) + ' Seconds Elapsed Time: ' + @ROUTINE
PRINT @END_TIME
STAGE6:
--SET @START_TIME = GETDATE() --Start the timer
---- THIS IS WHERE WE QUERY THE TABLE AND GET THE PRINICPAL PAYMENTS FOR EACH ID_NUM...
--BEGIN TRAN
--
--
---- ERR ROUTINE
--SET @ERR_NUM = @@ERROR
--SET @ROUTINE = 'STAGE5: ERROR...'
--IF (@ERR_NUM <> 0) GOTO PROGRAM_ERROR
--
--COMMIT TRAN
--
--SELECT @END_TIME = CAST((DATEDIFF(SS,@START_TIME,GETDATE()))AS VARCHAR(100)) + ' Seconds Elapsed Time: ' + @ROUTINE
--PRINT @END_TIME
OK:
-- SUCCESS
SET @iRETURN = 0
--CLEAR OBJECTS
--IF OBJECT_ID('[XJASN4N].[tblCLS_OFSA]','U') IS NOT NULL
--DROP TABLE [XJASN4N].[tblCLS_OFSA]
RETURN @iRETURN
PROGRAM_ERROR:
PRINT @ROUTINE
ROLLBACK TRAN
SET @iRETURN = 1
GOTO OK
PROGRAM_NULL:
SET @UDT_ERR_MSG = 'ERROR IN ROUTINE: ' + @ROUTINE + CHAR(13) + CHAR(13)
SET @UDT_ERR_MSG = @UDT_ERR_MSG + 'NULL VALUE FOR PARAMETER: ' + @PARAMNAME
RAISERROR(@UDT_ERR_MSG,16,1)
SET @iRETURN = 1
GOTO OK
END
CREATE PROCEDURE XJASN4N.p_RUNOFF4_STAGE5
-- Add the parameters for the stored procedure here
-- ,
--
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- DECLARATIONS
DECLARE @ROUTINEVARCHAR(100)
DECLARE @ERR_NUMINT
SET @ERR_NUM= 0
DECLARE @iRETURNINT
SET @iRETURN= 0
DECLARE @UDT_ERR_MSGVARCHAR(200)
DECLARE @PARAMNAMEVARCHAR(200)
DECLARE @START_TIMEDATETIME
DECLARE @END_TIMEVARCHAR(100)
-- SPROC DECLARATIONS
DECLARE @PrevRunBalFLOAT--Overall running total
SET @PrevRunBal= 0
DECLARE @PrevGrpBalFLOAT--Running total resets when account changes
SET @PrevGrpBal= 0
DECLARE @PrevRunCntINT--Overall running count (ordinal rank)
SET @PrevRunCnt= 0
DECLARE @PrevGrpCntINT--Running count resets when account changes
SET @PrevGrpCnt= 0
DECLARE @PrevAcctIDDECIMAL(25,0) --The "anchor" and "account change detector"
SET @PrevAcctID= 0
DECLARE @NEXT_PAY_DATEDATETIME
DECLARE @BEG_BALFLOAT
SET @BEG_BAL= 0
DECLARE @TOT_PAYFLOAT
SET @BEG_BAL= 0
DECLARE @INT_PAYFLOAT
SET @INT_PAY= 0
DECLARE @PRIN_PAYFLOAT
SET @PRIN_PAY= 0
DECLARE @PrevEND_BALFLOAT
SET @PrevEND_BAL= 0
DECLARE @END_BALFLOAT
SET @END_BAL= 0
DECLARE @CALC_END_BALFLOAT
SET @CALC_END_BAL= 0
DECLARE @EVAL_IDINT
SET @EVAL_ID= 0
-- Insert statements for procedure here
STAGE5:
SET @START_TIME = GETDATE() --Start the timer
-- Temp In-Place RATHER THAN ANOTHER TEMP TABLE...
-- THIS IS THE BULK OF THE WORK IN FIGURING THE AMERITIZATION OF A LOAN...
-- NOT DOING A LOOP AS BEFORE... 45MINS, SO DOING UPDATE SET BASED QUERY HANDLES EACH ROW AS IT COMES IN
BEGIN TRAN
UPDATE [CPMTEST].[XJASN4N].[tblCLS_OFSA]
SET --===== Running Total
@BEG_BAL =CASE
WHEN [ID_NUMBER] = @PrevAcctID THEN @CALC_END_BAL
ELSE [BEGIN_BALANCE] -- Restarts total at "0 + current amount"
END,
--@END_BAL = [ENDING_BALANCE], -- CHANGE WHEN FIX INITIAL QUERY
@PRIN_PAY = [PRINCIPAL_PAYMENT],
@PrevRunBal = [RunBal] = @PrevRunBal + @PRIN_PAY,--principal payment
--===== Grouped Running Total (Reset when account changes)
@PrevGrpBal = [GrpBal] =CASE
WHEN [ID_NUMBER] = @PrevAcctID THEN @PrevGrpBal + @PRIN_PAY
ELSE @PRIN_PAY -- Restarts total at "0 + current amount"
END,
--===== Running Count (Ordinal Rank)
@PrevRunCnt = [RunCnt] = @PrevRunCnt + 1,
--===== Grouped Running Total (Ordinal Rank, Reset when account changes)
@PrevGrpCnt = [GrpCnt] =CASE
WHEN [ID_NUMBER] = @PrevAcctID THEN @PrevGrpCnt + 1
ELSE 1 -- Restarts count at "1"
END,
@TOT_PAY = [TOTAL_PAYMENT] = [XJASN4N].[fn_TOTAL_PAYMENT]([@iEVAL]
, @BEG_BAL
, [P_GROSS_RATE]
, [P_PAYMENT_INT_RATE]
, [P_CUR_PAYMENT]),
@INT_PAY = [INT_PAYMENT] = XJASN4N.p_interest_pmt(@BEG_BAL,
[P_GROSS_RATE],
[P_PAYMENT_INT_RATE]),
@PRIN_PAY = [PRINCIPAL_PAYMENT] = [XJASN4N].[fn_PRINCIPAL_PAYMENT]([@iEVAL]
, @BEG_BAL
, [P_GROSS_RATE]
, [P_PAYMENT_INT_RATE]
, [P_CUR_PAYMENT]),
@CALC_END_BAL = [ENDING_BALANCE] = [XJASN4N].[fn_ENDING_BALANCE](@BEG_BAL
,@PRIN_PAY),
[BEGIN_BALANCE] =CASE
WHEN [ID_NUMBER] = @PrevAcctID THEN @CALC_END_BAL
ELSE [BEGIN_BALANCE]
END,
--===== "Anchor" and provides for "account change detection"
@PrevAcctID = [ID_NUMBER]
FROM [CPMTEST].[XJASN4N].[tblCLS_OFSA] AS [A]
WITH (INDEX(IX_tblCLS_OFSA_ID_BI_BC),TABLOCKX)
-- ERR ROUTINE
SET @ERR_NUM = @@ERROR
SET @ROUTINE = 'STAGE5: ERROR...'
IF (@ERR_NUM <> 0) GOTO PROGRAM_ERROR
COMMIT TRAN
SELECT @END_TIME = CAST((DATEDIFF(SS,@START_TIME,GETDATE()))AS VARCHAR(100)) + ' Seconds Elapsed Time: ' + @ROUTINE
PRINT @END_TIME
OK:
--SUCCESS
SET @iRETURN = 0
--CLEAR OBJECTS
--EXIT
RETURN @iRETURN
PROGRAM_ERROR:
PRINT @ROUTINE
ROLLBACK TRAN
SET @iRETURN = 1
GOTO OK
PROGRAM_NULL:
SET @UDT_ERR_MSG = 'ERROR IN ROUTINE: ' + @ROUTINE + CHAR(13) + CHAR(13)
SET @UDT_ERR_MSG = @UDT_ERR_MSG + 'NULL VALUE FOR PARAMETER: ' + @PARAMNAME
RAISERROR(@UDT_ERR_MSG,16,1)
SET @iRETURN = 1
GOTO OK
END
October 31, 2008 at 8:50 pm
Nicely done! And thanks for the feedback especially on the performance/durations.
My only concern is the BEGIN/COMMITs you have for Stages 1 through 4... the [CPMTEST].[XJASN4N].[tblCLS_OFSA] is a "temp in place" or "expendible" table... unless you have implicit commits turned off, there's no need for the BEGIN/COMMIT pairs for each stage. There's also no need for a ROLLBACK... the table is temporary in nature. Just have the error-code drop the bugger. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply