Retrieve records that are not in specified timeframe

Hello, Experts!

I am still a newbie in SQL Server and I need your help.  I have a table with the following structure:

Container_no, nvarchar(12)
Line, nchar(10)
Activity_Date, datetime
Activity, nvarchar(50)

Each container could have 50 or more activity records with different dates.  I need to be able to retrieve the container numbers that do not have activity between the dates 01/01/2011 and 12/31/2011.  I tried the following script but doesn't work:

SELECT DISTINCT Container_no
  FROM Reefer_Movements
  WHERE NOT EXISTS (SELECT Container_no, Activity_Date
  FROM Reefer_Movements
  WHERE Activity_Date BETWEEN '01/01/2011' AND '12/31/2011')
  ORDER BY Container_no

Any help would be very much appreciated.

Thanks!
Rick0124Programmer / AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

armchair_scouseCommented:
How about

SELECT DISTINCT Container_no
FROM Reefer_Movements
WHERE Activity Date <  '01/01/2011' AND Activity Date > '12/31/2011'
ORDER BY Container_no
0
HainKurtSr. System AnalystCommented:
try

SELECT DISTINCT container_no
FROM   reefer_movements
WHERE  activity_date NOT BETWEEN '01/01/2011' AND '12/31/2011'
ORDER  BY container_no
0
Rick0124Programmer / AnalystAuthor Commented:
Thanks, Experts!
I tried both scripts but the first one still gets records that have activity for the given dates, while the second one doesn't return any records.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rick0124Programmer / AnalystAuthor Commented:
I'm still checking the results but I think I got the script right.

SELECT DISTINCT Container_no
FROM Reefer_Movements
WHERE Container_no NOT IN (SELECT Container_no
FROM Reefer_Movements
WHERE Activity_Date BETWEEN '01/01/2011' AND '12/01/2011')
ORDER BY Container_no
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
deightonprogCommented:
SELECT DISTINCT Container_no
  FROM Reefer_Movements
  WHERE NOT EXISTS (SELECT 0
  FROM Reefer_Movements RM
  WHERE RM.Activity_Date BETWEEN '01/01/2011' AND '12/31/2011' AND RM.Container_no = Reefer_Movements.Container_no)
  ORDER BY Container_no
0
Rick0124Programmer / AnalystAuthor Commented:
I checked random containers to see if their activity does not fall between the specified dates and so far, they don't.  Out of a total of more than 110k distinct containers, the query result gave me a little less than 6k distinct containers.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.