none
Exclude Records in a Query

    Question

  • Ok so I am using the query below to locate records where today is not between the StartDate and EndDate fields in one of my tables, the query is working as designed and provides a list of names where today is not within the date range; but what I would like for the query to also do is if there is a record where today IS within the date range I want that name excluded from the results.

    What is happening is if I have a record for Bob in the AgentStatusJournal table with a date range of 7/1/2010 - 7/10/2010 then I have another record for Bob with a date range of 7/19/2010 - 7/24/2010 and today is 7/24/2010 I still see Bob in my results from the query below but I would like for Bob not to appear at all through the query since there is a record for Bob where today is within the StartDate and EndDate range.

    I think I've confused myself writing this up, but if you understand it and can provide advice I would really appreciate it!

    SELECT DISTINCT AgentID, AgentName

    FROM Agents INNER JOIN AgentStatusJournal ON Agents.AgentID = AgentStatusJournal.Agent

    WHERE (getdate() NOT BETWEEN StartDate and EndDate)

    Friday, July 23, 2010 8:49 PM

Answers

  • Can you try the following?

    SELECT DISTINCT AgentID, 
            AgentName 
    FROM  Agents a1
        INNER JOIN AgentStatusJournal 
         ON Agents.AgentID = AgentStatusJournal.Agent 
    WHERE (getdate() NOT BETWEEN StartDate AND EndDate) 
        AND AgentID NOT IN 
        (SELECT DISTINCT AgentID 
           FROM  Agents a 
           INNER JOIN AgentStatusJournal 
           ON Agents.AgentID = AgentStatusJournal.Agent 
            WHERE a.AgentID = a1.AgentID 
            AND NOT (getdate() NOT BETWEEN StartDate AND EndDate)) 
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Proposed as answer by Cool Mind Friday, July 23, 2010 9:14 PM
    • Marked as answer by TSS-Dev Monday, July 26, 2010 9:04 PM
    Friday, July 23, 2010 9:08 PM
    Moderator

All replies

  • Can you try the following?

    SELECT DISTINCT AgentID, 
            AgentName 
    FROM  Agents a1
        INNER JOIN AgentStatusJournal 
         ON Agents.AgentID = AgentStatusJournal.Agent 
    WHERE (getdate() NOT BETWEEN StartDate AND EndDate) 
        AND AgentID NOT IN 
        (SELECT DISTINCT AgentID 
           FROM  Agents a 
           INNER JOIN AgentStatusJournal 
           ON Agents.AgentID = AgentStatusJournal.Agent 
            WHERE a.AgentID = a1.AgentID 
            AND NOT (getdate() NOT BETWEEN StartDate AND EndDate)) 
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Proposed as answer by Cool Mind Friday, July 23, 2010 9:14 PM
    • Marked as answer by TSS-Dev Monday, July 26, 2010 9:04 PM
    Friday, July 23, 2010 9:08 PM
    Moderator
  • Try to explain in simple words. that will be more easier to understand all of us.

    if bob appears in 2 date ranges (7/1 to 7/10 and 7/19 to 7/24, you want to see him in the latest one which is 7/19 to 7/24? 

    or

    you dont want o see BOB if he appears in any of the date ranges?

    Friday, July 23, 2010 9:11 PM
  • Awesome this totally worked!! thank you so much.
    Monday, July 26, 2010 9:04 PM