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.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2008-10-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2008-11-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2008-12-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2009-01-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2009-02-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2009-03-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2009-04-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2009-05-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2009-06-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2009-07-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2009-08-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2009-09-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2009-10-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2009-11-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2009-12-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2010-01-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2010-02-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2010-03-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2010-04-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2010-05-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2010-06-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2010-07-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2010-08-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2010-09-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2010-10-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2010-11-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2010-12-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2011-01-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2011-02-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2011-03-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2011-04-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2011-05-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2011-06-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2011-07-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2011-08-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2011-09-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2011-10-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2011-11-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2011-12-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2012-01-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2012-02-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2012-03-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2012-04-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2012-05-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2012-06-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2012-07-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2012-08-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2012-09-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2012-10-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2012-11-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2012-12-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2013-01-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2013-02-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2013-03-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2013-04-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2013-05-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.542362171
2013-06-25 00:00:00.000 3303.85236217137 NULL 3303.85236217137 859.90763782863 482459.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_date 483319.45 4163.76 3303.85236217137 859.90763782863 480015.597637829
v_next_pmt_date 30000 3466.49401875 133.16401875 3333.33 26666.67
INNER QUERY DATA:
1 483319.45 4163.76 3 v_total_pmt 4163.76 8.085000 0.0808500000 8.45485833333333 3303.85236217137 p_interest_pmt/p_principal_pmt 3303.85236217137 859.90763782863 480015.597637829
2 30000 3333.33 2 p_principal_pmt 3333.33 5.250000 0.0525000000 8.45485833333333 133.16401875 v_total_pmt 3466.49401875 3333.33 26666.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] / 100 AS [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_pmt AS [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:
1 2008-09-25 00:00:00.000 483319.45 4163.76 3303.85236217137 859.90763782863 482459.542362171 4163.76 8.45485833333333 0.08085
2 2008-12-09 00:00:00.000 30000 3466.49401875 133.16401875 3333.33 26666.67 3333.33 8.45485833333333 0.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://qa.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://qa.sqlservercentral.com/Forums/Topic593999-8-1.aspx
--Jeff Moden
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
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
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
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]
(
-- ROWID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
[ID_NUMBER] DECIMAL(25, 0) NOT NULL
,BANK_ID DECIMAL(14, 0)
,BANK_CODE VARCHAR(5)
,PMT_FREQ INT
,PMT_FREQ_MULT CHAR(1)
,NEXT_PAYMENT_DATE DATETIME
,ACCRUAL_BASIS_CD INT
,AMRT_TYPE_CD DECIMAL(5, 0)
,CUR_GROSS_RATE FLOAT
,CUR_PAR_BAL FLOAT
,CUR_PAYMENT FLOAT
,MATURITY_DATE DATETIME
,LAST_PAYMENT_DATE DATETIME
,[@iEVAL] INT
,OBLIGOR VARCHAR(100)
,OBLIGATION VARCHAR(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 @ROUTINE VARCHAR(100)
DECLARE @ERR_NUM INT
SET @ERR_NUM = 0
DECLARE @iRETURN INT
SET @iRETURN = 0
DECLARE @UDT_ERR_MSG VARCHAR(200)
DECLARE @PARAMNAME VARCHAR(200)
DECLARE @START_TIME DATETIME
DECLARE @END_TIME VARCHAR(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 @ROUTINE VARCHAR(100)
DECLARE @ERR_NUM INT
SET @ERR_NUM = 0
DECLARE @iRETURN INT
SET @iRETURN = 0
DECLARE @UDT_ERR_MSG VARCHAR(200)
DECLARE @PARAMNAME VARCHAR(200)
DECLARE @START_TIME DATETIME
DECLARE @END_TIME VARCHAR(100)
-- SPROC DECLARATIONS
DECLARE @PrevRunBal FLOAT --Overall running total
SET @PrevRunBal = 0
DECLARE @PrevGrpBal FLOAT --Running total resets when account changes
SET @PrevGrpBal = 0
DECLARE @PrevRunCnt INT --Overall running count (ordinal rank)
SET @PrevRunCnt = 0
DECLARE @PrevGrpCnt INT --Running count resets when account changes
SET @PrevGrpCnt = 0
DECLARE @PrevAcctID DECIMAL(25,0) --The "anchor" and "account change detector"
SET @PrevAcctID = 0
DECLARE @NEXT_PAY_DATE DATETIME
DECLARE @BEG_BAL FLOAT
SET @BEG_BAL = 0
DECLARE @TOT_PAY FLOAT
SET @BEG_BAL = 0
DECLARE @INT_PAY FLOAT
SET @INT_PAY = 0
DECLARE @PRIN_PAY FLOAT
SET @PRIN_PAY = 0
DECLARE @PrevEND_BAL FLOAT
SET @PrevEND_BAL = 0
DECLARE @END_BAL FLOAT
SET @END_BAL = 0
DECLARE @CALC_END_BAL FLOAT
SET @CALC_END_BAL = 0
DECLARE @EVAL_ID INT
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply