CODEWITHSUNDEEP
sqlsql-server
Zac Cramer
Zac Cramer

Reputation: 131

How to include an "OR" statement in SQL for "no value"

I have a SQL script that tells me if any of my employees have any open service orders that have not been updated in the past 48 hours. It looks like this (simplified) -

select ServiceOrder, RepName from NotesTable

join OrdersTable on OrdersTable.OrderNumber=NotesTable.OrderNumber

having (DATEDIFF(day,max(NotesTable.LastModified),GETDATE()) >2)

Which basically just takes todays date, subtracts the last modified date of most recent note, and spits out the numbers of service orders that haven't been modified in 48 hours.

My problem is, if a service order has no notes, this script doesn't work. So even though it hasn't been updated in 48 hours it doesn't come out on the report. What kind of "or" do I need to say "also tell me if it has no notes."

I thought about joining another table that contains a "dateopened" field and then, 

OR (DATEDIFF(day,(NotesTable.DateOpened),GETDATE()) >2)

but that returns every service order older than 48 hours, so that doesn't help.

Thoughts?

Upvotes: 1

Views: 93

Answers (4)

JeffO
JeffO

Reputation: 8043

You need to include the other table that notes are related to. Maybe this is an orders table? Hopefully, that table has some field to indicate when it was last modified.

    select ServiceOrder, RepName from Orders
    left outer join NotesTable
    on Orders.OrderID = NotesTable.OrderID


    having (DATEDIFF(day,max(Coalesce(NotesTable.LastModified,
       Orders.LastModified)),GETDATE()) >2)

You may have to do a case statement instead of Coalesce to see which of the LastModified dates is more recent.

Upvotes: 0

Lamak
Lamak

Reputation: 70648

Actually, you are still missing parts of your query (the GROUP BY, for example), but this should give you an idea of what you want:

SELECT ServiceOrder, RepName 
FROM OrdersTable 
LEFT JOIN NotesTable
    ON OrdersTable.OrderNumber = NotesTable.OrderNumber
GROUP BY ServiceOrder, RepName 
HAVING (DATEDIFF(DAY,MAX(NotesTable.LastModified),GETDATE()) >2)
OR MAX(NotesTable.LastModified) IS NULL

Upvotes: 3

Eric J. Price
Eric J. Price

Reputation: 2785

If every record in your [Orders] table has a [NotesTable] record by default then this would work.

Change [NotesTable].[LastModified] to Isnull([NotesTable].[LastModified],[NotesTable].[DateOpened])...
This will use the "LastModified" if it exists and if it does not will use the "DateOpened" instead.

Upvotes: 2

Jackie Robinson
Jackie Robinson

Reputation: 94

You could use ISNULL (http://msdn.microsoft.com/en-us/library/ms184325.aspx)

select ServiceOrder, RepName 
from NotesTable
having (DATEDIFF(day,max(ISNULL(NotesTable.LastModified,NotesTable.DateOpened )),GETDATE()) >2)

Upvotes: -1

Related Questions