Checking for where a datetime column has a null va

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

SQL Server 2000 Forums

Transact-SQL (2000)

Checking for where a datetime column has a null va

Author

Topic

netsports
Starting Member

11 Posts

Posted - 2005-08-22 : 16:40:40

I'm trying to filter out sales reps in a resultset that are no longer with a company, whereas 'Start' is when the salesrep was hired ( a datetime datatype ) and "Terminated' is when a salesrep was terminated ( a datetime datatype).
Nulls are allowed in the Terminated column. If that column is null, then a salesrep still is employed. But when I want to include it in a where clause:

WHERE (QueriedDate = '20050707') AND (SalesReps.Terminated = 'null'), the syntax is invalid . What function can i use in this sql string to temporarily change 'Terminated' into something useable so that I can check for nulls in that column.
Thanks
.NetSports

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-22 : 16:51:38

SalesReps.Terminated IS NULL

Tara

netsports
Starting Member

11 Posts

Posted - 2005-08-22 : 19:27:35

quote:
Originally posted by tduggan

SalesReps.Terminated IS NULL

Tara

Thanks Tara. I'm also doing a filter in an adjacent sql query that eliminates a salerep from the resultset if they have a sale that day
(Orders.ID is the fieldname , a primary key ):

WHERE (Terminated IS NULL OR
Terminated > '20050707') AND (Start < '20050707') AND (NOT (Orders.ID >= 1))

does not give me an error, it brings up no data available (when I know there is data for this criteria). I know there's a better way to check and see if a salesrep has an orders.id by it in this prospective resultset, whereas in this particular query, i want to eliminate that salesrep from this resultset.
Thanks
NetSports

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-22 : 19:57:24

Are you sure you have your parentheses and ANDs in the right order there? If you do your line breaks to match the logic, you are getting:

WHERE (Terminated IS NULL OR Terminate > '20050707')
AND Start < '20050707'
AND NOT(Orders.ID >=1)

In fact, as I write it, the last line also translates as: Orders.ID < 1. I'll bet you don't have any Order IDs that are less than 1. Is that supposed to be a COUNT of Orders.ID?

---------------------------
EmeraldCityDomains.com

asarak
Starting Member

36 Posts

Posted - 2005-08-23 : 00:21:12

Try to use

WHERE (QueriedDate = '20050707') AND (SalesReps.Terminated is NULL)

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-23 : 10:58:26

Your where clause is equivalent to:

WHERE (Terminated IS NULL OR Terminated > '20050707')
  AND Start < '20050707' 
  AND Orders.ID < 1

Are you sure you have order(s) with ID < 1?

netsports
Starting Member

11 Posts

Posted - 2005-08-23 : 14:52:42

quote:
Originally posted by mmarovic

Your where clause is equivalent to:
WHERE (Terminated IS NULL OR Terminated > '20050707')
AND Start < '20050707'
AND Orders.ID < 1
Are you sure you have order(s) with ID < 1?

Yeah, I kind of figured comparing the Orders.ID with a value would be a loss cause. What I need to do is find out if a salesrep in this resultset has an Orders.ID attached to it, and if it does, not include it in the resultset.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-23 : 15:50:11

You could do a GROUP BY ... HAVING COUNT(Orders.ID) > 0

OR you could do a subquery:

FROM ... t1
WHERE ...
AND NOT EXISTS (SELECT Orders.ID FROM orders t2 WHERE t1.SalesRepID = t2.SalesRepID)

---------------------------
EmeraldCityDomains.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources