asked on 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!
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!
Microsoft SQL Server.NET Programming
Last Comment
Rick0124
try
SELECT DISTINCT container_no
FROM reefer_movements
WHERE activity_date NOT BETWEEN '01/01/2011' AND '12/31/2011'
ORDER BY container_no
SELECT DISTINCT container_no
FROM reefer_movements
WHERE activity_date NOT BETWEEN '01/01/2011' AND '12/31/2011'
ORDER BY container_no
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
ORDER BY 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
ORDER BY Container_no
ASKER
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.
SELECT DISTINCT Container_no
FROM Reefer_Movements
WHERE Activity Date < '01/01/2011' AND Activity Date > '12/31/2011'
ORDER BY Container_no