2

I am looking to query for all open and won opps, but i dont want to use the stagename as there multiple closed-Lost stages and there could couple more added later. I dont want to change the query everytime there is a new stage added in.

WHERE stagename not IN('Closed-Lost','Closed-Lost-NoBudget')

is there a way to get the records without explicitly specifying stagename?

Improve this question

2 Answers 2

Reset to default
6

If you have the stage properly linked to the Won-Lost, maybe by...

SELECT ID FROM Opportunity WHERE IsWon = true OR IsClosed = false
Improve this answer
2
  • Adding IsWon purely makes this answer less efficient.
    – Adrian Larson ♦
    Nov 26, 2017 at 16:51
  • @Adrian Larson, can you elaborate a bit more your thought and reasons? (So everybody can benefit from it in the future)
    – Sergio Alcocer
    Dec 16, 2017 at 12:22
4

How about selecting all, that don't contain "Closed" in their stage name:

WHERE stagename LIKE '%Won%' OR (NOT stagename LIKE '%Closed%')
Improve this answer
2
  • This would include closed/won as well. i need to have closedwon and exclude only closed lost ones
    – Prady
    Nov 7, 2017 at 18:12
  • @Prady updated my answer to include all wons. You could also write 'Closed-Lost%' if they all have the same prefix
    – itsmebasti
    Nov 7, 2017 at 18:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .