Mixing
Multiple Calculations Using Case
0
I have a data set where I need to perform one calculation is a column is populated, if not calculate off of a different column. I'm trying to do this by using a CASE statement, however I'm not having much luck with the syntax.
CASE WHEN [W/O] = 'O' THEN Net * (NetShare/100) ELSE Gross * (GrossShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
END AS 'TEST'
Any help would be much appreciated.
Many Thanks
sql
sql-server edited Nov 22 '18 at 12:41
MatBailie
59.7k1477111
asked Nov 22 '18 at 12:40
Carlos80Carlos80
145111
|
show 2 more comments
0
I have a data set where I need to perform one calculation is a column is populated, if not calculate off of a different column. I'm trying to do this by using a CASE statement, however I'm not having much luck with the syntax.
CASE WHEN [W/O] = 'O' THEN Net * (NetShare/100) ELSE Gross * (GrossShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
END AS 'TEST'
Any help would be much appreciated.
Many Thanks
sql
sql-server edited Nov 22 '18 at 12:41
MatBailie
59.7k1477111
asked Nov 22 '18 at 12:40
Carlos80Carlos80
145111
Sample data and desired results would help.
– Gordon Linoff
Nov 22 '18 at 12:41
1
ELSEmust be the last clause if present... like in a programming language you wouldif wo = o then ... if wo = w then ... else ... end.
– Salman A
Nov 22 '18 at 12:50
If you could explain the logic you're trying to implement, that would help a lot. We're all just sort of guessing right now.
– Eric Brandt
Nov 22 '18 at 12:51
For future reference, any error you are trying to overcome should be included in its entirety in your description. Mind reading does not work - guessing might if you are lucky (and you are this time).
– SMor
Nov 22 '18 at 13:02
Thanks all, the error I'm getting is - Incorrect syntax near the keyword 'WHEN'.
– Carlos80
Nov 22 '18 at 13:41
|
show 2 more comments
0
0
0
I have a data set where I need to perform one calculation is a column is populated, if not calculate off of a different column. I'm trying to do this by using a CASE statement, however I'm not having much luck with the syntax.
CASE WHEN [W/O] = 'O' THEN Net * (NetShare/100) ELSE Gross * (GrossShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
END AS 'TEST'
Any help would be much appreciated.
Many Thanks
sql
sql-server edited Nov 22 '18 at 12:41
MatBailie
59.7k1477111
asked Nov 22 '18 at 12:40
Carlos80Carlos80
145111
I have a data set where I need to perform one calculation is a column is populated, if not calculate off of a different column. I'm trying to do this by using a CASE statement, however I'm not having much luck with the syntax.
CASE WHEN [W/O] = 'O' THEN Net * (NetShare/100) ELSE Gross * (GrossShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
END AS 'TEST'
Any help would be much appreciated.
Many Thanks
sql
sql-server sql
sql-server edited Nov 22 '18 at 12:41
MatBailie
59.7k1477111
asked Nov 22 '18 at 12:40
Carlos80Carlos80
145111
edited Nov 22 '18 at 12:41
MatBailie
59.7k1477111
asked Nov 22 '18 at 12:40
Carlos80Carlos80
145111
edited Nov 22 '18 at 12:41
MatBailie
59.7k1477111
edited Nov 22 '18 at 12:41
MatBailie
59.7k1477111
edited Nov 22 '18 at 12:41
MatBailie
59.7k1477111
59.7k1477111
asked Nov 22 '18 at 12:40
Carlos80Carlos80
145111
asked Nov 22 '18 at 12:40
Carlos80Carlos80
145111
asked Nov 22 '18 at 12:40
Carlos80Carlos80
145111
145111
Sample data and desired results would help.
– Gordon Linoff
Nov 22 '18 at 12:41
1
ELSEmust be the last clause if present... like in a programming language you wouldif wo = o then ... if wo = w then ... else ... end.
– Salman A
Nov 22 '18 at 12:50
If you could explain the logic you're trying to implement, that would help a lot. We're all just sort of guessing right now.
– Eric Brandt
Nov 22 '18 at 12:51
For future reference, any error you are trying to overcome should be included in its entirety in your description. Mind reading does not work - guessing might if you are lucky (and you are this time).
– SMor
Nov 22 '18 at 13:02
Thanks all, the error I'm getting is - Incorrect syntax near the keyword 'WHEN'.
– Carlos80
Nov 22 '18 at 13:41
|
show 2 more comments
Sample data and desired results would help.
– Gordon Linoff
Nov 22 '18 at 12:41
1
ELSEmust be the last clause if present... like in a programming language you wouldif wo = o then ... if wo = w then ... else ... end.
– Salman A
Nov 22 '18 at 12:50
If you could explain the logic you're trying to implement, that would help a lot. We're all just sort of guessing right now.
– Eric Brandt
Nov 22 '18 at 12:51
For future reference, any error you are trying to overcome should be included in its entirety in your description. Mind reading does not work - guessing might if you are lucky (and you are this time).
– SMor
Nov 22 '18 at 13:02
Thanks all, the error I'm getting is - Incorrect syntax near the keyword 'WHEN'.
– Carlos80
Nov 22 '18 at 13:41
Sample data and desired results would help.
– Gordon Linoff
Nov 22 '18 at 12:41
Sample data and desired results would help.
– Gordon Linoff
Nov 22 '18 at 12:41
1
1
ELSE must be the last clause if present... like in a programming language you would if wo = o then ... if wo = w then ... else ... end.– Salman A
Nov 22 '18 at 12:50
ELSE must be the last clause if present... like in a programming language you would if wo = o then ... if wo = w then ... else ... end.– Salman A
Nov 22 '18 at 12:50
If you could explain the logic you're trying to implement, that would help a lot. We're all just sort of guessing right now.
– Eric Brandt
Nov 22 '18 at 12:51
If you could explain the logic you're trying to implement, that would help a lot. We're all just sort of guessing right now.
– Eric Brandt
Nov 22 '18 at 12:51
For future reference, any error you are trying to overcome should be included in its entirety in your description. Mind reading does not work - guessing might if you are lucky (and you are this time).
– SMor
Nov 22 '18 at 13:02
For future reference, any error you are trying to overcome should be included in its entirety in your description. Mind reading does not work - guessing might if you are lucky (and you are this time).
– SMor
Nov 22 '18 at 13:02
Thanks all, the error I'm getting is - Incorrect syntax near the keyword 'WHEN'.
– Carlos80
Nov 22 '18 at 13:41
Thanks all, the error I'm getting is - Incorrect syntax near the keyword 'WHEN'.
– Carlos80
Nov 22 '18 at 13:41
|
show 2 more comments
3 Answers
3
active
oldest
votes
0
I think this might just be a case of that the OP's CASE expression is malformed. The ELSE is always the last expression; it can't be in the middle. Thus you need to do:
CASE WHEN [W/O] = 'O' THEN Net * (NetShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
ELSE Gross * (GrossShare/100)
END AS TEST
answered Nov 22 '18 at 12:48
LarnuLarnu
19.8k51731
Thanks Larnu, I didn't actually know that the Else expression is always last. I now think that I'm way off with the whole Case statement as I'm trying to calculate on Net firstly but in Net is null then calculate Gross. Maybe I need a rethink, thanks
– Carlos80
Nov 22 '18 at 13:45
add a comment |
0
A CASE expression evaluates each WHEN, and if nothing matches then it uses the ELSE, which has to come after all the WHENs. Your ELSE is misplaced. Is this what you meant?
CASE
WHEN [W/O] = 'O' THEN Net * (NetShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
ELSE Gross * (GrossShare/100)
END AS 'TEST'
answered Nov 22 '18 at 12:50
Eric BrandtEric Brandt
2,70011024
add a comment |
0
Is this what you want?
(CASE WHEN [W/O] = 'O' THEN Net * (NetShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
ELSE Gross * (GrossShare/100)
END) AS TEST
answered Nov 22 '18 at 12:42
Gordon LinoffGordon Linoff
781k35310414
In the OP's example,Wdoesn't result inNet *. What theELSEis meant to mean though, I can only guess.
– MatBailie
Nov 22 '18 at 12:43
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%2f53431265%2fmultiple-calculations-using-case%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
I think this might just be a case of that the OP's CASE expression is malformed. The ELSE is always the last expression; it can't be in the middle. Thus you need to do:
CASE WHEN [W/O] = 'O' THEN Net * (NetShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
ELSE Gross * (GrossShare/100)
END AS TEST
answered Nov 22 '18 at 12:48
LarnuLarnu
19.8k51731
Thanks Larnu, I didn't actually know that the Else expression is always last. I now think that I'm way off with the whole Case statement as I'm trying to calculate on Net firstly but in Net is null then calculate Gross. Maybe I need a rethink, thanks
– Carlos80
Nov 22 '18 at 13:45
add a comment |
0
I think this might just be a case of that the OP's CASE expression is malformed. The ELSE is always the last expression; it can't be in the middle. Thus you need to do:
CASE WHEN [W/O] = 'O' THEN Net * (NetShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
ELSE Gross * (GrossShare/100)
END AS TEST
answered Nov 22 '18 at 12:48
LarnuLarnu
19.8k51731
Thanks Larnu, I didn't actually know that the Else expression is always last. I now think that I'm way off with the whole Case statement as I'm trying to calculate on Net firstly but in Net is null then calculate Gross. Maybe I need a rethink, thanks
– Carlos80
Nov 22 '18 at 13:45
add a comment |
0
0
0
I think this might just be a case of that the OP's CASE expression is malformed. The ELSE is always the last expression; it can't be in the middle. Thus you need to do:
CASE WHEN [W/O] = 'O' THEN Net * (NetShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
ELSE Gross * (GrossShare/100)
END AS TEST
answered Nov 22 '18 at 12:48
LarnuLarnu
19.8k51731
I think this might just be a case of that the OP's CASE expression is malformed. The ELSE is always the last expression; it can't be in the middle. Thus you need to do:
CASE WHEN [W/O] = 'O' THEN Net * (NetShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
ELSE Gross * (GrossShare/100)
END AS TEST
answered Nov 22 '18 at 12:48
LarnuLarnu
19.8k51731
answered Nov 22 '18 at 12:48
LarnuLarnu
19.8k51731
answered Nov 22 '18 at 12:48
LarnuLarnu
19.8k51731
answered Nov 22 '18 at 12:48
LarnuLarnu
19.8k51731
19.8k51731
Thanks Larnu, I didn't actually know that the Else expression is always last. I now think that I'm way off with the whole Case statement as I'm trying to calculate on Net firstly but in Net is null then calculate Gross. Maybe I need a rethink, thanks
– Carlos80
Nov 22 '18 at 13:45
add a comment |
Thanks Larnu, I didn't actually know that the Else expression is always last. I now think that I'm way off with the whole Case statement as I'm trying to calculate on Net firstly but in Net is null then calculate Gross. Maybe I need a rethink, thanks
– Carlos80
Nov 22 '18 at 13:45
Thanks Larnu, I didn't actually know that the Else expression is always last. I now think that I'm way off with the whole Case statement as I'm trying to calculate on Net firstly but in Net is null then calculate Gross. Maybe I need a rethink, thanks
– Carlos80
Nov 22 '18 at 13:45
Thanks Larnu, I didn't actually know that the Else expression is always last. I now think that I'm way off with the whole Case statement as I'm trying to calculate on Net firstly but in Net is null then calculate Gross. Maybe I need a rethink, thanks
– Carlos80
Nov 22 '18 at 13:45
add a comment |
0
A CASE expression evaluates each WHEN, and if nothing matches then it uses the ELSE, which has to come after all the WHENs. Your ELSE is misplaced. Is this what you meant?
CASE
WHEN [W/O] = 'O' THEN Net * (NetShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
ELSE Gross * (GrossShare/100)
END AS 'TEST'
answered Nov 22 '18 at 12:50
Eric BrandtEric Brandt
2,70011024
add a comment |
0
A CASE expression evaluates each WHEN, and if nothing matches then it uses the ELSE, which has to come after all the WHENs. Your ELSE is misplaced. Is this what you meant?
CASE
WHEN [W/O] = 'O' THEN Net * (NetShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
ELSE Gross * (GrossShare/100)
END AS 'TEST'
answered Nov 22 '18 at 12:50
Eric BrandtEric Brandt
2,70011024
add a comment |
0
0
0
A CASE expression evaluates each WHEN, and if nothing matches then it uses the ELSE, which has to come after all the WHENs. Your ELSE is misplaced. Is this what you meant?
CASE
WHEN [W/O] = 'O' THEN Net * (NetShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
ELSE Gross * (GrossShare/100)
END AS 'TEST'
answered Nov 22 '18 at 12:50
Eric BrandtEric Brandt
2,70011024
A CASE expression evaluates each WHEN, and if nothing matches then it uses the ELSE, which has to come after all the WHENs. Your ELSE is misplaced. Is this what you meant?
CASE
WHEN [W/O] = 'O' THEN Net * (NetShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
ELSE Gross * (GrossShare/100)
END AS 'TEST'
answered Nov 22 '18 at 12:50
Eric BrandtEric Brandt
2,70011024
answered Nov 22 '18 at 12:50
Eric BrandtEric Brandt
2,70011024
answered Nov 22 '18 at 12:50
Eric BrandtEric Brandt
2,70011024
answered Nov 22 '18 at 12:50
Eric BrandtEric Brandt
2,70011024
2,70011024
add a comment |
add a comment |
0
Is this what you want?
(CASE WHEN [W/O] = 'O' THEN Net * (NetShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
ELSE Gross * (GrossShare/100)
END) AS TEST
answered Nov 22 '18 at 12:42
Gordon LinoffGordon Linoff
781k35310414
In the OP's example,Wdoesn't result inNet *. What theELSEis meant to mean though, I can only guess.
– MatBailie
Nov 22 '18 at 12:43
add a comment |
0
Is this what you want?
(CASE WHEN [W/O] = 'O' THEN Net * (NetShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
ELSE Gross * (GrossShare/100)
END) AS TEST
answered Nov 22 '18 at 12:42
Gordon LinoffGordon Linoff
781k35310414
In the OP's example,Wdoesn't result inNet *. What theELSEis meant to mean though, I can only guess.
– MatBailie
Nov 22 '18 at 12:43
add a comment |
0
0
0
Is this what you want?
(CASE WHEN [W/O] = 'O' THEN Net * (NetShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
ELSE Gross * (GrossShare/100)
END) AS TEST
answered Nov 22 '18 at 12:42
Gordon LinoffGordon Linoff
781k35310414
Is this what you want?
(CASE WHEN [W/O] = 'O' THEN Net * (NetShare/100)
WHEN [W/O] = 'W' THEN (NetShare/100)
ELSE Gross * (GrossShare/100)
END) AS TEST
answered Nov 22 '18 at 12:42
Gordon LinoffGordon Linoff
781k35310414
edited Nov 23 '18 at 3:16
answered Nov 22 '18 at 12:42
Gordon LinoffGordon Linoff
781k35310414
answered Nov 22 '18 at 12:42
Gordon LinoffGordon Linoff
781k35310414
answered Nov 22 '18 at 12:42
Gordon LinoffGordon Linoff
781k35310414
781k35310414
In the OP's example,Wdoesn't result inNet *. What theELSEis meant to mean though, I can only guess.
– MatBailie
Nov 22 '18 at 12:43
add a comment |
In the OP's example,Wdoesn't result inNet *. What theELSEis meant to mean though, I can only guess.
– MatBailie
Nov 22 '18 at 12:43
In the OP's example,
W doesn't result in Net *. What the ELSE is meant to mean though, I can only guess.– MatBailie
Nov 22 '18 at 12:43
In the OP's example,
W doesn't result in Net *. What the ELSE is meant to mean though, I can only guess.– MatBailie
Nov 22 '18 at 12:43
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.
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%2f53431265%2fmultiple-calculations-using-case%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
Sample data and desired results would help.
– Gordon Linoff
Nov 22 '18 at 12:41
1
ELSEmust be the last clause if present... like in a programming language you wouldif wo = o then ... if wo = w then ... else ... end.– Salman A
Nov 22 '18 at 12:50
If you could explain the logic you're trying to implement, that would help a lot. We're all just sort of guessing right now.
– Eric Brandt
Nov 22 '18 at 12:51
For future reference, any error you are trying to overcome should be included in its entirety in your description. Mind reading does not work - guessing might if you are lucky (and you are this time).
– SMor
Nov 22 '18 at 13:02
Thanks all, the error I'm getting is - Incorrect syntax near the keyword 'WHEN'.
– Carlos80
Nov 22 '18 at 13:41