Mixing
ETL Transformation of Generic Transaction into Bet and Win
I've got a problem in my database, understanding a generic Transaction if its a Bet or Win. Currently it consists both in one transaction.
I've an additional field which can help -> Bet Amount, which is constant.
Here is how the table looks like:
Basically what you see in Amount Field in the first transaction, is the Amount the customer starts with. Whenever the amount of next transactions are higher compare to its previous transaction then the specific Bet has got a Win too, if its lower then its only a Bet.
My need is to create an ETL process which will Create this Table:
Hope you can help me write an efficient SQL Server code in order to create the requested Table.
Thanks in advance,
sql-server ssis etl asked Nov 19 '18 at 13:57
erezlale
1911214
add a comment |
I've got a problem in my database, understanding a generic Transaction if its a Bet or Win. Currently it consists both in one transaction.
I've an additional field which can help -> Bet Amount, which is constant.
Here is how the table looks like:
Basically what you see in Amount Field in the first transaction, is the Amount the customer starts with. Whenever the amount of next transactions are higher compare to its previous transaction then the specific Bet has got a Win too, if its lower then its only a Bet.
My need is to create an ETL process which will Create this Table:
Hope you can help me write an efficient SQL Server code in order to create the requested Table.
Thanks in advance,
sql-server ssis etl asked Nov 19 '18 at 13:57
erezlale
1911214
add a comment |
I've got a problem in my database, understanding a generic Transaction if its a Bet or Win. Currently it consists both in one transaction.
I've an additional field which can help -> Bet Amount, which is constant.
Here is how the table looks like:
Basically what you see in Amount Field in the first transaction, is the Amount the customer starts with. Whenever the amount of next transactions are higher compare to its previous transaction then the specific Bet has got a Win too, if its lower then its only a Bet.
My need is to create an ETL process which will Create this Table:
Hope you can help me write an efficient SQL Server code in order to create the requested Table.
Thanks in advance,
sql-server ssis etl asked Nov 19 '18 at 13:57
erezlale
1911214
I've got a problem in my database, understanding a generic Transaction if its a Bet or Win. Currently it consists both in one transaction.
I've an additional field which can help -> Bet Amount, which is constant.
Here is how the table looks like:
Basically what you see in Amount Field in the first transaction, is the Amount the customer starts with. Whenever the amount of next transactions are higher compare to its previous transaction then the specific Bet has got a Win too, if its lower then its only a Bet.
My need is to create an ETL process which will Create this Table:
Hope you can help me write an efficient SQL Server code in order to create the requested Table.
Thanks in advance,
sql-server ssis etl sql-server ssis etl asked Nov 19 '18 at 13:57
erezlale
1911214
asked Nov 19 '18 at 13:57
erezlale
1911214
asked Nov 19 '18 at 13:57
erezlale
1911214
asked Nov 19 '18 at 13:57
erezlale
1911214
asked Nov 19 '18 at 13:57
erezlale
1911214
1911214
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
I'm not sure I'd try to do this purely in SSIS, but here's a query that should do the right thing:
select *,
case
when LAG(Amount, 1, Amount) over (order by TransactionID) = Amount then 'Bet'
else 'Win'
end
from dbo.Transactions;
I'm using the LAG() function to look back one row at the value for the Amount column in the previous row (where "previous" is defined by the ordering of TransactionID†). If that value is the same, consider it a Bet otherwise it's a Win.
† It seems like you'd want to consider games in isolation. If that's the case, change the OVER() clause on the LAG() function to partition by GameID order by TransactionID.
answered Nov 19 '18 at 17:28
Ben Thul
22.2k32546
add a comment |
Use the script below as SQL Command in your OLEDB Source
SELECT TransactionID, DateTime, GameID, CASE WHEN ISNULL(Amount, '') = '' THEN
'StartGame' WHEN Amount > BetAmount THEN 'win' ELSE 'bet' END AS
TransactionTypeID, BetAmount, Amount
FROM [your table name]
edited Nov 20 '18 at 10:01
Yahfoufi
81111129
answered Nov 19 '18 at 16:10
iMajek
434
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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%2f53376224%2fetl-transformation-of-generic-transaction-into-bet-and-win%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I'm not sure I'd try to do this purely in SSIS, but here's a query that should do the right thing:
select *,
case
when LAG(Amount, 1, Amount) over (order by TransactionID) = Amount then 'Bet'
else 'Win'
end
from dbo.Transactions;
I'm using the LAG() function to look back one row at the value for the Amount column in the previous row (where "previous" is defined by the ordering of TransactionID†). If that value is the same, consider it a Bet otherwise it's a Win.
† It seems like you'd want to consider games in isolation. If that's the case, change the OVER() clause on the LAG() function to partition by GameID order by TransactionID.
answered Nov 19 '18 at 17:28
Ben Thul
22.2k32546
add a comment |
I'm not sure I'd try to do this purely in SSIS, but here's a query that should do the right thing:
select *,
case
when LAG(Amount, 1, Amount) over (order by TransactionID) = Amount then 'Bet'
else 'Win'
end
from dbo.Transactions;
I'm using the LAG() function to look back one row at the value for the Amount column in the previous row (where "previous" is defined by the ordering of TransactionID†). If that value is the same, consider it a Bet otherwise it's a Win.
† It seems like you'd want to consider games in isolation. If that's the case, change the OVER() clause on the LAG() function to partition by GameID order by TransactionID.
answered Nov 19 '18 at 17:28
Ben Thul
22.2k32546
add a comment |
I'm not sure I'd try to do this purely in SSIS, but here's a query that should do the right thing:
select *,
case
when LAG(Amount, 1, Amount) over (order by TransactionID) = Amount then 'Bet'
else 'Win'
end
from dbo.Transactions;
I'm using the LAG() function to look back one row at the value for the Amount column in the previous row (where "previous" is defined by the ordering of TransactionID†). If that value is the same, consider it a Bet otherwise it's a Win.
† It seems like you'd want to consider games in isolation. If that's the case, change the OVER() clause on the LAG() function to partition by GameID order by TransactionID.
answered Nov 19 '18 at 17:28
Ben Thul
22.2k32546
I'm not sure I'd try to do this purely in SSIS, but here's a query that should do the right thing:
select *,
case
when LAG(Amount, 1, Amount) over (order by TransactionID) = Amount then 'Bet'
else 'Win'
end
from dbo.Transactions;
I'm using the LAG() function to look back one row at the value for the Amount column in the previous row (where "previous" is defined by the ordering of TransactionID†). If that value is the same, consider it a Bet otherwise it's a Win.
† It seems like you'd want to consider games in isolation. If that's the case, change the OVER() clause on the LAG() function to partition by GameID order by TransactionID.
answered Nov 19 '18 at 17:28
Ben Thul
22.2k32546
answered Nov 19 '18 at 17:28
Ben Thul
22.2k32546
answered Nov 19 '18 at 17:28
Ben Thul
22.2k32546
answered Nov 19 '18 at 17:28
Ben Thul
22.2k32546
22.2k32546
add a comment |
add a comment |
Use the script below as SQL Command in your OLEDB Source
SELECT TransactionID, DateTime, GameID, CASE WHEN ISNULL(Amount, '') = '' THEN
'StartGame' WHEN Amount > BetAmount THEN 'win' ELSE 'bet' END AS
TransactionTypeID, BetAmount, Amount
FROM [your table name]
edited Nov 20 '18 at 10:01
Yahfoufi
81111129
answered Nov 19 '18 at 16:10
iMajek
434
add a comment |
Use the script below as SQL Command in your OLEDB Source
SELECT TransactionID, DateTime, GameID, CASE WHEN ISNULL(Amount, '') = '' THEN
'StartGame' WHEN Amount > BetAmount THEN 'win' ELSE 'bet' END AS
TransactionTypeID, BetAmount, Amount
FROM [your table name]
edited Nov 20 '18 at 10:01
Yahfoufi
81111129
answered Nov 19 '18 at 16:10
iMajek
434
add a comment |
Use the script below as SQL Command in your OLEDB Source
SELECT TransactionID, DateTime, GameID, CASE WHEN ISNULL(Amount, '') = '' THEN
'StartGame' WHEN Amount > BetAmount THEN 'win' ELSE 'bet' END AS
TransactionTypeID, BetAmount, Amount
FROM [your table name]
edited Nov 20 '18 at 10:01
Yahfoufi
81111129
answered Nov 19 '18 at 16:10
iMajek
434
Use the script below as SQL Command in your OLEDB Source
SELECT TransactionID, DateTime, GameID, CASE WHEN ISNULL(Amount, '') = '' THEN
'StartGame' WHEN Amount > BetAmount THEN 'win' ELSE 'bet' END AS
TransactionTypeID, BetAmount, Amount
FROM [your table name]
edited Nov 20 '18 at 10:01
Yahfoufi
81111129
answered Nov 19 '18 at 16:10
iMajek
434
edited Nov 20 '18 at 10:01
Yahfoufi
81111129
edited Nov 20 '18 at 10:01
Yahfoufi
81111129
edited Nov 20 '18 at 10:01
Yahfoufi
81111129
81111129
answered Nov 19 '18 at 16:10
iMajek
434
answered Nov 19 '18 at 16:10
iMajek
434
answered Nov 19 '18 at 16:10
iMajek
434
434
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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%2f53376224%2fetl-transformation-of-generic-transaction-into-bet-and-win%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
