Create a query that only shows active records based on start date, duration, and true/false

Question

I'm trying to piece together a dynamic view that will give a list of all emails that are still being rejected.

Table structure:

• EmailName - varchar(150) - An email address
• StartRejection - Date - Day to start rejecting email
• Duration - Small Int - Duration of rejection in days
• IsIndefinate - Bit - Is the rejection 'non date range specific' but indefinate

Answer 1

Guess, you're looking for t-sql function DATEADD.

So the query you need may be looks like

SELECT  [EmailName]
FROM    [YourTable]
WHERE   [IsIndefinate] = 1 OR
        @date BETWEEN [StartRejection] AND DATEADD(day, [StartRejection], [Duration])

where @date is you [StartDate] parameter. This query returns emails which are IsIndefinate or in between the [StartRejection] and [StartRejection] + [Duration] dates (correct my assumption if I understand your question incorrectly).

Answer 2

Try this out. Note that I used smalldatetime for StartRejection as some readers may not be on SQL 2008+.

DECLARE @EmailRejection TABLE (EmailName varchar(150), StartRejection smalldatetime, Duration smallint, IsIndefinate bit)
DECLARE @Today smalldatetime, @Duration smallint
SET @Today = CONVERT(smalldatetime, CONVERT(varchar, GETDATE(),112))
SET @Duration = 5

INSERT INTO @EmailRejection(EmailName, StartRejection, Duration, IsIndefinate) VALUES('[email protected]', @Today-3, @Duration, 0)
INSERT INTO @EmailRejection(EmailName, StartRejection, Duration, IsIndefinate) VALUES('[email protected]', @Today-4, @Duration, 0)
INSERT INTO @EmailRejection(EmailName, StartRejection, Duration, IsIndefinate) VALUES('[email protected]', @Today-5, @Duration, 0)
INSERT INTO @EmailRejection(EmailName, StartRejection, Duration, IsIndefinate) VALUES('[email protected]', @Today-6, @Duration, 0)
INSERT INTO @EmailRejection(EmailName, StartRejection, Duration, IsIndefinate) VALUES('[email protected]', @Today-10, @Duration, 1)

SELECT * FROM @EmailRejection
WHERE (IsIndefinate = 1) OR (IsIndefinate = 0 AND StartRejection > DATEADD(dd, 0-Duration, GETDATE()))

IsIndefinate (sic should be IsIndefinite)
/spellingpolice