How to check two SQL records for with one condition?
0
I want to add another column to this table which raises a says 'Cash to Electronic' in front on the PersonID if the mode for 1st installment is 'Cash' and mode for 2nd installment in 'Electronic'.
PersonID InstalmentNumber DateOfInstalment Mode
50023467 2 15-Mar-12 Electronic
50023467 1 15-Feb-12 Electronic
50023468 2 5-Apr-12 Cash
50023468 1 5-Mar-12 Cash
50023469 2 15-Mar-12 Electronic
50023469 2 15-Mar-12 Electronic
50023469 1 15-Feb-12 Electronic
50023469 1 15-Feb-12 Cash
50027967 2 15-Mar-12 Electronic
Expected Output:
PersonID InstalmentNumber DateOfInstalment Mode Flag
50023467 2 15-Mar-12 Electronic
50023467 1 15-Feb-12 Electronic
50023468 2 5-Apr-12 Cash
50023468 1 5-Mar-12 Cash
50023469 2 15-Mar-12 Electronic
50023469 2 15-Mar-12 Electronic
50023469 1 15-Feb-12 Cash
50023469 1 15-Feb-12 Cash Cash To Electronic
50027967 2 15-Mar-12 Electronic
sql oracle case
edited Jul 29 '13 at 14:41
a_horse_with_no_name
291k46443537
asked Jul 29 '13 at 13:02
MontyPython
1,42092644
add a comment |
0
I want to add another column to this table which raises a says 'Cash to Electronic' in front on the PersonID if the mode for 1st installment is 'Cash' and mode for 2nd installment in 'Electronic'.
PersonID InstalmentNumber DateOfInstalment Mode
50023467 2 15-Mar-12 Electronic
50023467 1 15-Feb-12 Electronic
50023468 2 5-Apr-12 Cash
50023468 1 5-Mar-12 Cash
50023469 2 15-Mar-12 Electronic
50023469 2 15-Mar-12 Electronic
50023469 1 15-Feb-12 Electronic
50023469 1 15-Feb-12 Cash
50027967 2 15-Mar-12 Electronic
Expected Output:
PersonID InstalmentNumber DateOfInstalment Mode Flag
50023467 2 15-Mar-12 Electronic
50023467 1 15-Feb-12 Electronic
50023468 2 5-Apr-12 Cash
50023468 1 5-Mar-12 Cash
50023469 2 15-Mar-12 Electronic
50023469 2 15-Mar-12 Electronic
50023469 1 15-Feb-12 Cash
50023469 1 15-Feb-12 Cash Cash To Electronic
50027967 2 15-Mar-12 Electronic
sql oracle case
edited Jul 29 '13 at 14:41
a_horse_with_no_name
291k46443537
asked Jul 29 '13 at 13:02
MontyPython
1,42092644
what is the expected output ?
– t-clausen.dk
Jul 29 '13 at 13:12
@MontyPython Did you consider my answer?
– Parado
Jul 29 '13 at 14:21
add a comment |
0
0
0
I want to add another column to this table which raises a says 'Cash to Electronic' in front on the PersonID if the mode for 1st installment is 'Cash' and mode for 2nd installment in 'Electronic'.
PersonID InstalmentNumber DateOfInstalment Mode
50023467 2 15-Mar-12 Electronic
50023467 1 15-Feb-12 Electronic
50023468 2 5-Apr-12 Cash
50023468 1 5-Mar-12 Cash
50023469 2 15-Mar-12 Electronic
50023469 2 15-Mar-12 Electronic
50023469 1 15-Feb-12 Electronic
50023469 1 15-Feb-12 Cash
50027967 2 15-Mar-12 Electronic
Expected Output:
PersonID InstalmentNumber DateOfInstalment Mode Flag
50023467 2 15-Mar-12 Electronic
50023467 1 15-Feb-12 Electronic
50023468 2 5-Apr-12 Cash
50023468 1 5-Mar-12 Cash
50023469 2 15-Mar-12 Electronic
50023469 2 15-Mar-12 Electronic
50023469 1 15-Feb-12 Cash
50023469 1 15-Feb-12 Cash Cash To Electronic
50027967 2 15-Mar-12 Electronic
sql oracle case
edited Jul 29 '13 at 14:41
a_horse_with_no_name
291k46443537
asked Jul 29 '13 at 13:02
MontyPython
1,42092644
I want to add another column to this table which raises a says 'Cash to Electronic' in front on the PersonID if the mode for 1st installment is 'Cash' and mode for 2nd installment in 'Electronic'.
PersonID InstalmentNumber DateOfInstalment Mode
50023467 2 15-Mar-12 Electronic
50023467 1 15-Feb-12 Electronic
50023468 2 5-Apr-12 Cash
50023468 1 5-Mar-12 Cash
50023469 2 15-Mar-12 Electronic
50023469 2 15-Mar-12 Electronic
50023469 1 15-Feb-12 Electronic
50023469 1 15-Feb-12 Cash
50027967 2 15-Mar-12 Electronic
Expected Output:
PersonID InstalmentNumber DateOfInstalment Mode Flag
50023467 2 15-Mar-12 Electronic
50023467 1 15-Feb-12 Electronic
50023468 2 5-Apr-12 Cash
50023468 1 5-Mar-12 Cash
50023469 2 15-Mar-12 Electronic
50023469 2 15-Mar-12 Electronic
50023469 1 15-Feb-12 Cash
50023469 1 15-Feb-12 Cash Cash To Electronic
50027967 2 15-Mar-12 Electronic
sql oracle case
sql oracle case
edited Jul 29 '13 at 14:41
a_horse_with_no_name
291k46443537
asked Jul 29 '13 at 13:02
MontyPython
1,42092644
edited Jul 29 '13 at 14:41
a_horse_with_no_name
291k46443537
asked Jul 29 '13 at 13:02
MontyPython
1,42092644
edited Jul 29 '13 at 14:41
a_horse_with_no_name
291k46443537
edited Jul 29 '13 at 14:41
a_horse_with_no_name
291k46443537
edited Jul 29 '13 at 14:41
a_horse_with_no_name
291k46443537
291k46443537
asked Jul 29 '13 at 13:02
MontyPython
1,42092644
asked Jul 29 '13 at 13:02
MontyPython
1,42092644
asked Jul 29 '13 at 13:02
MontyPython
1,42092644
1,42092644
what is the expected output ?
– t-clausen.dk
Jul 29 '13 at 13:12
@MontyPython Did you consider my answer?
– Parado
Jul 29 '13 at 14:21
add a comment |
what is the expected output ?
– t-clausen.dk
Jul 29 '13 at 13:12
@MontyPython Did you consider my answer?
– Parado
Jul 29 '13 at 14:21
what is the expected output ?
– t-clausen.dk
Jul 29 '13 at 13:12
what is the expected output ?
– t-clausen.dk
Jul 29 '13 at 13:12
@MontyPython Did you consider my answer?
– Parado
Jul 29 '13 at 14:21
@MontyPython Did you consider my answer?
– Parado
Jul 29 '13 at 14:21
add a comment |
2 Answers
2
active
oldest
votes
2
Try this way:
select distinct t.PersonID,t.InstalmentNumber,t.DateOfInstalment,t.Mode,
case
when x.PersonID is not null
and t.Mode = 'Cash'
and t.InstalmentNumber = 1
then 'Cash to Electronic'
else null
end as Flag
from tab t
left join tab x on x.PersonID = t.PersonID AND
x.Mode = 'Electronic' AND
x.InstalmentNumber = 2
Sql Fiddle Demo
answered Jul 29 '13 at 13:08
Parado
21.8k74867
add a comment |
0
Probablylate to the party, but I would use a window function
select
distinct PersonID,
InstalmentNumber,
DateOfInstalment,
Mode,
case
when lead(Mode) over(partition by PersonID order by InstalmentNumber) = 'Cash' then 'Cash to Electronic'
else null
end as Flag
from tab
answered Nov 22 at 17:22
Ruben Helsloot
294315
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
});
}
});
draft saved
draft discarded
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%2f17924827%2fhow-to-check-two-sql-records-for-with-one-condition%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
2
Try this way:
select distinct t.PersonID,t.InstalmentNumber,t.DateOfInstalment,t.Mode,
case
when x.PersonID is not null
and t.Mode = 'Cash'
and t.InstalmentNumber = 1
then 'Cash to Electronic'
else null
end as Flag
from tab t
left join tab x on x.PersonID = t.PersonID AND
x.Mode = 'Electronic' AND
x.InstalmentNumber = 2
Sql Fiddle Demo
answered Jul 29 '13 at 13:08
Parado
21.8k74867
add a comment |
2
Try this way:
select distinct t.PersonID,t.InstalmentNumber,t.DateOfInstalment,t.Mode,
case
when x.PersonID is not null
and t.Mode = 'Cash'
and t.InstalmentNumber = 1
then 'Cash to Electronic'
else null
end as Flag
from tab t
left join tab x on x.PersonID = t.PersonID AND
x.Mode = 'Electronic' AND
x.InstalmentNumber = 2
Sql Fiddle Demo
answered Jul 29 '13 at 13:08
Parado
21.8k74867
add a comment |
2
2
2
Try this way:
select distinct t.PersonID,t.InstalmentNumber,t.DateOfInstalment,t.Mode,
case
when x.PersonID is not null
and t.Mode = 'Cash'
and t.InstalmentNumber = 1
then 'Cash to Electronic'
else null
end as Flag
from tab t
left join tab x on x.PersonID = t.PersonID AND
x.Mode = 'Electronic' AND
x.InstalmentNumber = 2
Sql Fiddle Demo
answered Jul 29 '13 at 13:08
Parado
21.8k74867
Try this way:
select distinct t.PersonID,t.InstalmentNumber,t.DateOfInstalment,t.Mode,
case
when x.PersonID is not null
and t.Mode = 'Cash'
and t.InstalmentNumber = 1
then 'Cash to Electronic'
else null
end as Flag
from tab t
left join tab x on x.PersonID = t.PersonID AND
x.Mode = 'Electronic' AND
x.InstalmentNumber = 2
Sql Fiddle Demo
answered Jul 29 '13 at 13:08
Parado
21.8k74867
edited Jul 29 '13 at 14:21
answered Jul 29 '13 at 13:08
Parado
21.8k74867
answered Jul 29 '13 at 13:08
Parado
21.8k74867
answered Jul 29 '13 at 13:08
Parado
21.8k74867
21.8k74867
add a comment |
add a comment |
0
Probablylate to the party, but I would use a window function
select
distinct PersonID,
InstalmentNumber,
DateOfInstalment,
Mode,
case
when lead(Mode) over(partition by PersonID order by InstalmentNumber) = 'Cash' then 'Cash to Electronic'
else null
end as Flag
from tab
answered Nov 22 at 17:22
Ruben Helsloot
294315
add a comment |
0
Probablylate to the party, but I would use a window function
select
distinct PersonID,
InstalmentNumber,
DateOfInstalment,
Mode,
case
when lead(Mode) over(partition by PersonID order by InstalmentNumber) = 'Cash' then 'Cash to Electronic'
else null
end as Flag
from tab
answered Nov 22 at 17:22
Ruben Helsloot
294315
add a comment |
0
0
0
Probablylate to the party, but I would use a window function
select
distinct PersonID,
InstalmentNumber,
DateOfInstalment,
Mode,
case
when lead(Mode) over(partition by PersonID order by InstalmentNumber) = 'Cash' then 'Cash to Electronic'
else null
end as Flag
from tab
answered Nov 22 at 17:22
Ruben Helsloot
294315
Probablylate to the party, but I would use a window function
select
distinct PersonID,
InstalmentNumber,
DateOfInstalment,
Mode,
case
when lead(Mode) over(partition by PersonID order by InstalmentNumber) = 'Cash' then 'Cash to Electronic'
else null
end as Flag
from tab
answered Nov 22 at 17:22
Ruben Helsloot
294315
answered Nov 22 at 17:22
Ruben Helsloot
294315
answered Nov 22 at 17:22
Ruben Helsloot
294315
answered Nov 22 at 17:22
Ruben Helsloot
294315
294315
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f17924827%2fhow-to-check-two-sql-records-for-with-one-condition%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
what is the expected output ?
– t-clausen.dk
Jul 29 '13 at 13:12
@MontyPython Did you consider my answer?
– Parado
Jul 29 '13 at 14:21