SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bcarney
Starting Member

USA
7 Posts

Posted - 05/21/2013 :  09:14:17  Show Profile  Reply with Quote
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

Edited by - bcarney on 05/21/2013 09:24:36

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 05/21/2013 :  10:09:27  Show Profile  Reply with Quote
Something like this?
SELECT * FROM tbl
WHERE ISNULL(Status,'') <> 'set'
OR ageing > 10 OR ageing IS NULL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/22/2013 :  00:38:55  Show Profile  Reply with Quote
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
Go to Top of Page

bcarney
Starting Member

USA
7 Posts

Posted - 05/22/2013 :  14:43:19  Show Profile  Reply with Quote
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.

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 05/22/2013 :  14:52:35  Show Profile  Reply with Quote
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
Go to Top of Page

bcarney
Starting Member

USA
7 Posts

Posted - 05/22/2013 :  15:20:29  Show Profile  Reply with Quote
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
Go to Top of Page

bcarney
Starting Member

USA
7 Posts

Posted - 05/22/2013 :  15:20:37  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/23/2013 :  00:33:47  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000