October 29, 2008 at 4:22 pm
So I read and tried the Running total that Jeff Moden posted...
Mine is updating everything the same, which seems logical cause it's an update statement...
Jeff's updated each row i thought, where mine isn't for each row of columns [Begin_Balance], [TOTAL_PAYMENT],[INT_PAYMENT],[PRINCIPAL_PAYMENT]
,[ENDING_BALANCE]....
Here is the query and some of the data from the update... (I spared the you the total of 58 rows...)
QUERY:
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
UPDATE XJASN4N.P_RUN_OFF_JAS
SET --===== Running Total
@BEG_BAL = [BEGIN_BALANCE],
@END_BAL = [ENDING_BALANCE], -- CHANGE WHEN FIX INITIAL QUERY
@PrevRunBal = RunBal = @PrevRunBal + @PRIN_PAY, --principal payment
--===== Grouped Running Total (Reset when account changes)
@PrevGrpBal = GrpBal = CASE
WHEN [PSUEDOID] = @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 [PSUEDOID] = @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] = @CALC_END_BAL,
--===== "Anchor" and provides for "account change detection"
@PrevAcctID = [PSUEDOID]
FROM XJASN4N.P_RUN_OFF_JAS
WITH (INDEX(IX_P_RUN_OFF_JAS_NUM_BANK_OBL),TABLOCKX)
RESULTS:
RowNum ID_NUMBER P_PMT_FREQ PMT_FREQ_MULT P_BANK_ID P_OBLIGOR P_OBLIGATION NEXT_PAYMENT_DATE Begin_Balance TOTAL_PAYMENT INT_PAYMENT PRINCIPAL_PAYMENT ENDING_BALANCE P_CUR_PAYMENT P_PAYMENT_INT_RATE P_GROSS_RATE iEVAL P_MATURITY_DATE NEXT_PAY_DATE2 RunBal GrpBal RunCnt GrpCnt PSUEDOID
1 1.00001E+20 1 M 2001 89051 52367513 00:00.0 481593.7566 4163.76 3297.974246 865.785754 481593.7566 4163.76 8.454858333 0.08085 3 00:00.0 00:00.0 0 0 1 1 1.00001E+21
2 1.00001E+20 1 M 2001 89051 52367513 00:00.0 481593.7566 4163.76 3297.974246 865.785754 481593.7566 4163.76 8.454858333 0.08085 3 00:00.0 00:00.0 865.785754 865.785754 2 1 1.00001E+21
3 1.00001E+20 1 M 2001 89051 52367513 00:00.0 481593.7566 4163.76 3297.974246 865.785754 481593.7566 4163.76 8.454858333 0.08085 3 00:00.0 00:00.0 1731.571508 865.785754 3 1 1.00001E+21
4 1.00001E+20 1 M 2001 89051 52367513 00:00.0 481593.7566 4163.76 3297.974246 865.785754 481593.7566 4163.76 8.454858333 0.08085 3 00:00.0 00:00.0 2597.357262 865.785754 4 1 1.00001E+21
5 1.00001E+20 1 M 2001 89051 52367513 00:00.0 481593.7566 4163.76 3297.974246 865.785754 481593.7566 4163.76 8.454858333 0.08085 3 00:00.0 00:00.0 3463.143016 865.785754 5 1 1.00001E+21
6 1.00001E+20 1 M 2001 89051 52367513 00:00.0 481593.7566 4163.76 3297.974246 865.785754 481593.7566 4163.76 8.454858333 0.08085 3 00:00.0 00:00.0 4328.92877 865.785754 6 1 1.00001E+21
7 1.00001E+20 1 M 2001 89051 52367513 00:00.0 481593.7566 4163.76 3297.974246 865.785754 481593.7566 4163.76 8.454858333 0.08085 3 00:00.0 00:00.0 5194.714524 865.785754 7 1 1.00001E+21
8 1.00001E+20 1 M 2001 89051 52367513 00:00.0 481593.7566 4163.76 3297.974246 865.785754 481593.7566 4163.76 8.454858333 0.08085 3 00:00.0 00:00.0 6060.500278 865.785754 8 1 1.00001E+21
9 1.00001E+20 1 M 2001 89051 52367513 00:00.0 481593.7566 4163.76 3297.974246 865.785754 481593.7566 4163.76 8.454858333 0.08085 3 00:00.0 00:00.0 6926.286032 865.785754 9 1 1.00001E+21
October 29, 2008 at 4:36 pm
You still haven't posted the sample data / DDL I requested in any easily usable format (see the link in my signature if you don't know what that means... and why is this a new thread? You don't need to make a new thread every time you slightly rephrase the question.
October 29, 2008 at 4:40 pm
hi,
I thought it was the right way to do it... I'll remember for next time...
October 29, 2008 at 5:06 pm
From first glance, it looks like you might be running into one of the same issues I did... in that you can't use this method(or at least my experimentation with it has failed miserably) to run recursive functions / subqueries with local variables / prior updated table values in them. Run a trace when you attempt to run that query on one of your functions. See if it's actually executing it 58 times, or if it's executing it once.
October 29, 2008 at 5:11 pm
How do I turn on the trace for this query and shut it off???
thanks,
John
October 29, 2008 at 5:30 pm
Was just thinking, the method is working becuase the RunBal and RunCnt fields are updating... I have to missing somehting...
October 29, 2008 at 5:30 pm
Open up profiler and begin a new trace. In the filters, add the exact name of one of your functions with % signs on each side of it. Pretty sure you need sysadmin privileges to do this.
October 29, 2008 at 6:07 pm
DOH...
I had to change this for some reason to
@BEG_BAL = CASE
WHEN [ID_NUMBER] = @PrevAcctID THEN @CALC_END_BAL
ELSE [BEGIN_BALANCE] -- Restarts total at "0 + current amount"
END,
where it was @BEG_BAL =[BEGIN_BALANCE],
October 29, 2008 at 8:41 pm
Are you still having trouble? I can't quite tell if you fixed your own issue, or just one and there's still something to do.
If you still have trouble - knowing what your functions are doing would be good to know (I can figure out why you're passing in the current payment amount into the IPMT and PPMT functions, or what I take them to be.) Some samples of the starting data and what the various columns mean would also be key. I can't help but think there's some extra complication in there, making it hard to see the actual issue.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 29, 2008 at 8:50 pm
Hi,
I was so excited when I figured it out, I left in a rush to celebrate...
It does work, the fix was in my last post... weird; I would like to understand it more.
When I get to work tomorrow I will post data and the code, the proper way...
Then you all can play... I still have to test with a 2nd record, to test the change in ID_NUMBER and then add a function when Maturity Date is meet...
But wow is it great, better than looping and of course supper fast...lol
October 30, 2008 at 7:46 am
Question: Can an UPDATE STAMEMENT be done on a Derived Table while that Derived Table is inserting data into a table...
So in the FROM CLAUSE of the UPDATE STATEMENT, I would be inserting data into the table that the
UPDATE STATEMENT is updating as they go in?
I'm asking because before I can do the Update Statement in my post here I have to get the data,
and that takes 17 minutes to generate 2.9million records... so my thought was try and update the records as they go into the table... maybe also putting an index or something on the 1st table I am getting data from might help? i show you...
also below is the code that generates my staging table for the UPDATE to be performed on...
create table #tempjas
(
n int
)
update #tempjas
set n = n+1
from
(
insert into #tempjas (n)
select t.n
from dbo.tally t where t.n <=10
)
select * from #tempjas
drop table #tempjas
-- this is the table where raw data resides
ALTER TABLE tblmain
ADD PRIMARY KEY NONCLUSTERED ([ID])
CREATE CLUSTERED INDEX IX_tblmain_ID_BI_BC
ON tblmain ([ID_NUMBER], [BANK_ID], [BANK_CODE])
--this is my staging table
IF OBJECT_ID('XJASN4N.P_RUN_OFF_JAS','U') IS NOT NULL
DROP TABLE XJASN4N.P_RUN_OFF_JAS
CREATE TABLE [XJASN4N].[P_RUN_OFF_JAS]
(
[RowNum] [int] IDENTITY(1,1) NOT NULL,
[ID_NUMBER] [decimal](25, 0) NULL,
[PSUEDOID] [varchar](50) NOT NULL,
[P_PMT_FREQ] [int] NULL,
[PMT_FREQ_MULT] [char](1) NULL,
[P_BANK_ID] [int] NULL,
[P_OBLIGOR] [varchar](10) NULL,
[P_OBLIGATION] [varchar](10) NULL,
[NEXT_PAYMENT_DATE] [datetime] NULL,
[Begin_Balance] [float] NULL,
[TOTAL_PAYMENT] [float] NULL,
[INT_PAYMENT] [float] NULL,
[PRINCIPAL_PAYMENT] [float] NULL,
[ENDING_BALANCE] [float] NULL,
[P_CUR_PAYMENT] [float] NULL,
[P_PAYMENT_INT_RATE] [float] NULL,
[P_GROSS_RATE] [float] NULL,
[iEVAL] [int] NULL,
[P_MATURITY_DATE] [datetime] NULL,
[NEXT_PAY_DATE2] [datetime] NULL,
[RunBal] [float] NULL,
[GrpBal] [float] NULL,
[RunCnt] [int] NULL,
[GrpCnt] [int] NULL,
)
-- then this is from Jeff Moden's stuff
ALTER TABLE XJASN4N.P_RUN_OFF_JAS
ADD PRIMARY KEY NONCLUSTERED ([PSUEDOID]) --nonclustered to resolve "Merry-go-Round"
--===== Add the "sorting index" to the table
CREATE CLUSTERED INDEX IX_P_RUN_OFF_JAS_NUM_BANK_OBL --clustered to resolve "Merry-go-Round"
ON XJASN4N.P_RUN_OFF_JAS (ID_NUMBER, [P_BANK_ID],[P_OBLIGOR],[P_OBLIGATION])
--insert into would be here...
SELECT
[A].[ID_NUMBER],([A].[ID_NUMBER] + T.N) AS [PSUEDOID], [A].[P_PMT_FREQ], [A].[PMT_FREQ_MULT]
, [A].[P_BANK_ID], [A].[P_OBLIGOR], [A].[P_OBLIGATION], [A].[NEXT_PAYMENT_DATE], [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].[iEVAL], [A].[P_MATURITY_DATE]
, (CASE [A].[PMT_FREQ_MULT]
WHEN 'M' THEN DATEADD(MONTH, (T.N - 2) + [A].[P_PMT_FREQ], [A].[NEXT_PAYMENT_DATE])
WHEN 'D' THEN DATEADD(DAY, (T.N - 2) + [A].[P_PMT_FREQ], [A].[NEXT_PAYMENT_DATE])
END) AS [NEXT_PAY_DATE2]
,NULL, NULL, NULL, NULL
FROM
DBO.TALLY T
CROSS JOIN
(
SELECT
[ID_NUMBER]
-- , [ID_NUMBER] AS [PSUEDOID]
,[PMT_FREQ] AS [P_PMT_FREQ]
,[PMT_FREQ_MULT]
,CAST(BANK_ID AS INT) AS [P_BANK_ID]
,(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) AS [OBLIGOR]
,(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) AS [OBLIGATION]
,[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)
, CAST([CUR_PAYMENT] AS FLOAT)) 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)
, CAST([CUR_PAYMENT] AS FLOAT)) 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)
,CAST([CUR_PAYMENT] AS FLOAT))) AS [ENDING BALANCE]
, CAST([CUR_PAYMENT] AS FLOAT) AS [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]
, XJASN4N.fn_RUNOFF_CONSTANTS([AMRT_TYPE_CD]) AS [iEVAL]
, [MATURITY_DATE] AS [P_MATURITY_DATE]
FROM
tblMain A
WITH (INDEX(IX_tblCLS_OFSA_ID_BI_BC),TABLOCKX)
WHERE [CUR_PAYMENT] <> 0
AND [CUR_PAR_BAL] <> 0
-- AND ID_NUMBER = 100000890510052367513
-- AND PMT_FREQ_MULT = 'M'
) [A]
WHERE
(CASE [A].[PMT_FREQ_MULT]
WHEN 'M' THEN DATEDIFF(MONTH, [A].[NEXT_PAYMENT_DATE], [A].[P_MATURITY_DATE]) + 1
WHEN 'D' THEN DATEDIFF(DAY, [A].[NEXT_PAYMENT_DATE], [A].[P_MATURITY_DATE]) + 1
END) AS [NEXT_PAY_DATE2]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply