teradata, reset when, EOD_BAL_AM, running balance
up vote
0
down vote
favorite
I am going through some code. I still do not understand the below code. I do understand the second code for RUN_BAL_AM but I do not understand the below code for EOD_BAL_AM. Can someone please explain how this is calculating the EOD_BAL_AM? I have seen an explanation of RESET WHEN in Teradata which I kind of understand. However, I still do not understand why this takes MAX of RUN_BAL_AM. I would think this should take the running balance at the end of the day. I am struggling to understand this code. Some background: X.CURR_BAL_AM is the balance at the beginning of the statement cycle. Thanks
SELECT
Y.*,
MAX(Y.RUN_BAL_AM) OVER (
PARTITION BY Y.ACCT_DIM_NB
ORDER BY Y.DAY_TIME_DIM_NB, Y.TXN_POSTING_SEQ
RESET WHEN Y.CS_TXN_CD NOT IN ('072','075','079','107','111')
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS EOD_BAL_AM
FROM (
SELECT
X.*,
X.CURR_BAL_AM +
SUM(X.TXN_AM*X._PST_) OVER (
PARTITION BY X.ACCT_DIM_NB , X.TIME_DIM_NB
ORDER BY X.DAY_TIME_DIM_NB, X.TXN_POSTING_SEQ
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUN_BAL_AM
sql teradata
asked Nov 19 at 15:05
coder1167
1076
add a comment |
up vote
0
down vote
favorite
I am going through some code. I still do not understand the below code. I do understand the second code for RUN_BAL_AM but I do not understand the below code for EOD_BAL_AM. Can someone please explain how this is calculating the EOD_BAL_AM? I have seen an explanation of RESET WHEN in Teradata which I kind of understand. However, I still do not understand why this takes MAX of RUN_BAL_AM. I would think this should take the running balance at the end of the day. I am struggling to understand this code. Some background: X.CURR_BAL_AM is the balance at the beginning of the statement cycle. Thanks
SELECT
Y.*,
MAX(Y.RUN_BAL_AM) OVER (
PARTITION BY Y.ACCT_DIM_NB
ORDER BY Y.DAY_TIME_DIM_NB, Y.TXN_POSTING_SEQ
RESET WHEN Y.CS_TXN_CD NOT IN ('072','075','079','107','111')
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS EOD_BAL_AM
FROM (
SELECT
X.*,
X.CURR_BAL_AM +
SUM(X.TXN_AM*X._PST_) OVER (
PARTITION BY X.ACCT_DIM_NB , X.TIME_DIM_NB
ORDER BY X.DAY_TIME_DIM_NB, X.TXN_POSTING_SEQ
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUN_BAL_AM
sql teradata
asked Nov 19 at 15:05
coder1167
1076
1
The inner Cumulative Sum calculates a Running Total per account/day and the outer does a Cumulative Max (ignoring negative transaction amounts) per account across day boundaries with that reset. Hard to tell what/why this is actually doing? I wouldn't expect an end of day balance... You need to check the resultclosely.
– dnoeth
Nov 19 at 16:33
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I am going through some code. I still do not understand the below code. I do understand the second code for RUN_BAL_AM but I do not understand the below code for EOD_BAL_AM. Can someone please explain how this is calculating the EOD_BAL_AM? I have seen an explanation of RESET WHEN in Teradata which I kind of understand. However, I still do not understand why this takes MAX of RUN_BAL_AM. I would think this should take the running balance at the end of the day. I am struggling to understand this code. Some background: X.CURR_BAL_AM is the balance at the beginning of the statement cycle. Thanks
SELECT
Y.*,
MAX(Y.RUN_BAL_AM) OVER (
PARTITION BY Y.ACCT_DIM_NB
ORDER BY Y.DAY_TIME_DIM_NB, Y.TXN_POSTING_SEQ
RESET WHEN Y.CS_TXN_CD NOT IN ('072','075','079','107','111')
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS EOD_BAL_AM
FROM (
SELECT
X.*,
X.CURR_BAL_AM +
SUM(X.TXN_AM*X._PST_) OVER (
PARTITION BY X.ACCT_DIM_NB , X.TIME_DIM_NB
ORDER BY X.DAY_TIME_DIM_NB, X.TXN_POSTING_SEQ
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUN_BAL_AM
sql teradata
asked Nov 19 at 15:05
coder1167
1076
I am going through some code. I still do not understand the below code. I do understand the second code for RUN_BAL_AM but I do not understand the below code for EOD_BAL_AM. Can someone please explain how this is calculating the EOD_BAL_AM? I have seen an explanation of RESET WHEN in Teradata which I kind of understand. However, I still do not understand why this takes MAX of RUN_BAL_AM. I would think this should take the running balance at the end of the day. I am struggling to understand this code. Some background: X.CURR_BAL_AM is the balance at the beginning of the statement cycle. Thanks
SELECT
Y.*,
MAX(Y.RUN_BAL_AM) OVER (
PARTITION BY Y.ACCT_DIM_NB
ORDER BY Y.DAY_TIME_DIM_NB, Y.TXN_POSTING_SEQ
RESET WHEN Y.CS_TXN_CD NOT IN ('072','075','079','107','111')
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS EOD_BAL_AM
FROM (
SELECT
X.*,
X.CURR_BAL_AM +
SUM(X.TXN_AM*X._PST_) OVER (
PARTITION BY X.ACCT_DIM_NB , X.TIME_DIM_NB
ORDER BY X.DAY_TIME_DIM_NB, X.TXN_POSTING_SEQ
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUN_BAL_AM
sql teradata
sql teradata
asked Nov 19 at 15:05
coder1167
1076
asked Nov 19 at 15:05
coder1167
1076
asked Nov 19 at 15:05
coder1167
1076
asked Nov 19 at 15:05
coder1167
1076
asked Nov 19 at 15:05
coder1167
1076
1076
1
The inner Cumulative Sum calculates a Running Total per account/day and the outer does a Cumulative Max (ignoring negative transaction amounts) per account across day boundaries with that reset. Hard to tell what/why this is actually doing? I wouldn't expect an end of day balance... You need to check the resultclosely.
– dnoeth
Nov 19 at 16:33
add a comment |
1
The inner Cumulative Sum calculates a Running Total per account/day and the outer does a Cumulative Max (ignoring negative transaction amounts) per account across day boundaries with that reset. Hard to tell what/why this is actually doing? I wouldn't expect an end of day balance... You need to check the resultclosely.
– dnoeth
Nov 19 at 16:33
1
1
The inner Cumulative Sum calculates a Running Total per account/day and the outer does a Cumulative Max (ignoring negative transaction amounts) per account across day boundaries with that reset. Hard to tell what/why this is actually doing? I wouldn't expect an end of day balance... You need to check the resultclosely.
– dnoeth
Nov 19 at 16:33
The inner Cumulative Sum calculates a Running Total per account/day and the outer does a Cumulative Max (ignoring negative transaction amounts) per account across day boundaries with that reset. Hard to tell what/why this is actually doing? I wouldn't expect an end of day balance... You need to check the resultclosely.
– dnoeth
Nov 19 at 16:33
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53377440%2fteradata-reset-when-eod-bal-am-running-balance%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
The inner Cumulative Sum calculates a Running Total per account/day and the outer does a Cumulative Max (ignoring negative transaction amounts) per account across day boundaries with that reset. Hard to tell what/why this is actually doing? I wouldn't expect an end of day balance... You need to check the resultclosely.
– dnoeth
Nov 19 at 16:33