Query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query question

Author

Topic

bcarney
Starting Member

7 Posts

Posted - 2013-05-21 : 09:14:17

I have a table with multiple columns that I am trying to build a query that will evaluate two of the columns and return only the data that does not meet specific parameters

Name |Status | Ageing | Last_change
A | set | 10 | 12/12/13
B | locked | 12 | 12/12/13
C | (null) | 10 | 12/12/13
D | unlocked| 99 | 12/12/13
E | Set | (null) | 12/12/13

In the above example data, I would like to evaluate the "Status" and "Ageing" Column an return any combination that does have a Status of "Set" and/or Ageing greater than "10" or null. So "A" would not be returned but B through E would be returned.

Thanks
Brian

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-21 : 10:09:27

Something like this?

SELECT * FROM tbl
WHERE ISNULL(Status,'') <> 'set'
OR ageing > 10 OR ageing IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-22 : 00:38:55

Tell us how you think below record will be returned?
C | (null) | 10 | 12/12/13

it has Status value as null and also its Ageing is not greater than 10

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bcarney
Starting Member

7 Posts

Posted - 2013-05-22 : 14:43:19

Thanks everyone I've been dealing with a PC problem since yesterday. Some addtional information I for got to mention, I am using MS SQL 2008R2.

Visakh16
C | (null) | 10 | 12/12/13
Actualy rows B,C,D,E should all return

Should return due to the Null in the second field.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-22 : 14:52:35

quote:
Originally posted by bcarney

I have a table with multiple columns that I am trying to build a query that will evaluate two of the columns and return only the data that does not meet specific parameters

Name |Status | Ageing | Last_change
A | set | 10 | 12/12/13
B | locked | 12 | 12/12/13
C | (null) | 10 | 12/12/13
D | unlocked| 99 | 12/12/13
E | Set | (null) | 12/12/13

In the above example data, I would like to evaluate the "Status" and "Ageing" Column an return any combination that does have a Status of "Set" and/or Ageing greater than "10" or null. So "A" would not be returned but B through E would be returned.

Thanks
Brian

When I posted my first reply to your question, I thought you had a "not" in there as in
"In the above example data, I would like to evaluate the "Status" and "Ageing" Column an return any combination that does not have a Status of "Set" and/or Ageing greater than "10" or null. So "A" would not be returned but B through E would be returned."

Doesn't the code I posted not work for you? If it does not, can you post whether the results should be return or not for the following cases?


Status        Ageing
null            5
null           10
null           15
null         null

ABCD            5
ABCD           10
ABCD           15
ABCD         null

set             5
set            10
set            15
set          null

bcarney
Starting Member

7 Posts

Posted - 2013-05-22 : 15:20:29

These should return... (I'm not a programer in case you had not determined it. LOL)

These should all be returned:
Status Ageing
null 5
null 10
null 15
null null

ABCD 15
ABCD null

set 15
set null

bcarney
Starting Member

7 Posts

Posted - 2013-05-22 : 15:20:37

These should return... (I'm not a programer in case you had not determined it. LOL)

These should all be returned:
Status Ageing
null 5
null 10
null 15
null null

ABCD 15
ABCD null

set 15
set null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 00:33:47

This is not as per your original explanantion as I can still see records with Status value <> 'Set' and still you getting it in output
Anyways if we assume this is really what you want, I think you can use this


SELECT * FROM tbl
WHERE Status IS NULL
OR (Status = 'Set' AND (Ageing >10 OR Ageing IS NULL))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources