Select contracts that are only in the notification period or are expired

I have a SQL Server 2005 table called 'Contracts':

CREATE TABLE Contracts (
    ContractNo int not null,
    ExpirationDate smalldatetime not null,
    NotificationInAdvancedDays int not null
)

I want to display only the contracts that are within the notification period (ExpirationDate+DaysNotificationInAdvanced) or contracts th开发者_开发问答at are expired (ExpirationDate<CurrentDate).

Have a look at the DateAdd function in SQL Server.

With it, you can do this in your where clause:

where (GetDate() > DateAdd(DAY, DaysNotificationAdvanced * -1, ExpirationDate) OR GetDate() > ExpirationDate)

Since there is no DateSubtract function in SQL Server, I mulitplied DaysNotificationAdvanced by -1 to subtract it from ExpirationDate.

Update: As JNK mentions in the comments, you don't really need to have the second condition in the where clause " OR GetDate() > ExpirationDate" if you are looking to return the contracts that are in the notification period or later.

Perhaps you could enhance the select list in the query to indicate whether the contract is in the notification period or expired:

SELECT ContractNo, 
       ExpirationDate,
       DaysNotificationAdvanced
       CASE WHEN GETDATE() > ExpirationDate THEN 'Expired' 
       ELSE 'About To Expire' END AS ContractStatus
FROM Contracts
WHERE GetDate() > DateAdd(DAY, DaysNotificationAdvanced * -1, ExpirationDate

You can achieve it like this:

SELECT * 
FROM Contracts 
WHERE GETDATE() > DATEADD(day,-1*daysInAdvance,expirationDate) 
OR expirationDate < GETDATE()