Hi Everyone
I am stuck on a SQL query where I need to exclude rows in the dataset only when All the 3 combined conditions below are met-
Off in ('UAE', 'DXB') and
Datepart(weekday, workdate) <> 5 and
workdate < '2021-12-12'
I am using the And operator but it still excludes rows in the dataset if any of the above conditions are met which I don't want.
Thanks for your help.
December 23, 2021 at 4:27 pm
I really doubt it. Show us your WHERE clause, please, plus a sample of the data which is selected which you say should not have been.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 23, 2021 at 4:45 pm
Hi,
Thanks for your reply.
just as a general query, could you please help on how to exclude rows in sql if there are multiple conditions involved.
I will try to build up on that.
December 23, 2021 at 4:48 pm
The syntax you used in your post is the syntax I would use for selecting only those rows where all three conditions are satisfied.
SELECT ...
FROM ...
WHERE condition1 and condition2 and condition3
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 23, 2021 at 7:37 pm
Actually I want to exclude rows in the dataset not include based on the condition.
If you want to exclude, simply reverse your conditions. For example, rather than "IN" use "NOT IN" or instead of "<" use ">=". Or, alternately, toss some brackets around your WHERE conditions (all of them in 1 set of brackets) and toss a NOT in front. Using Phil's example:
SELECT ...
FROM ...
WHERE NOT (condition1 and condition2 and condition3)
Then if condition1, 2, and 3 are TRUE, it will get excluded. If ANY of the conditions are false, it gets included.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 23, 2021 at 9:28 pm
If you want to exclude, simply reverse your conditions. For example, rather than "IN" use "NOT IN" or instead of "<" use ">=". Or, alternately, toss some brackets around your WHERE conditions (all of them in 1 set of brackets) and toss a NOT in front. Using Phil's example:
SELECT ...
FROM ...
WHERE NOT (condition1 and condition2 and condition3)Then if condition1, 2, and 3 are TRUE, it will get excluded. If ANY of the conditions are false, it gets included.
Really Really Really Thank you so much for this life saver solution !!!
I don't have enough words to thank you my friend, really appreciate your valuable help.
Merry Christmas and a very Happy and Prosperous New Year !!!
December 29, 2021 at 7:03 pm
I am happy to help. I do recommend you look into truth tables. They can be very helpful when working with logic like this. It is VERY easy to think that if you are doing something like "x>10" that the opposite would be "x<10", but if you use a truth table, you can quickly verify the 3 values that would matter - 9, 10, and 11. Smaller, exact, and larger. For an example like that, a truth table is trivial and not needed, but as soon as you get more complicated things in there (multi-variable comparisons with a lot of calculations OR a lot of conditions), a truth table can be very helpful.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply