Select a l list that is not in a table

I am programing in VS 2008. I have a small list of dates that are stored in memory and a table that also contains dates. I would like to return those dates in my list that are not in the table. I know I could loop through the dates and check each one against the table. But I was hoping to do this in one select statement. Can it be done? And if so how?
I will use letters rather than actual dates to reduce my typing here.

List of dates = a, b, c, d, e, f
Datetable contains  c, e

Desired result set = a, b, d, f

If this list of dates were in its own table rather than memoryI could do

Select DesiredDate From DateTable1 where DesiredDate not in( Select DesiredDate from DateTable2)

Is there any way to accomplish this without first putting my date list into a table?
thanks,
pat
mpdillonAsked:
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.

Aneesh RetnakaranDatabase AdministratorCommented:

Select DesiredDate From DateTable1 dt1
where NOT Exists ( Select 1 from DateTable2 dt2 where dt1.DesiredDate = Dt2.DesiredDate  )

0
dampseyCommented:
Select DesiredDate From DateTable1 where DesiredDate not in (c,e)

try this
you may need to use convert(datetime, c) or similar syntax

if c and e were strings the syntax should be

Select DesiredDate From DateTable1 where DesiredDate not in ('c','e')
0
MilleniumaireCommented:
Have a look at the following web page (arrays and lists in sql server 2005) which shows how to handle lists of strings in SELECT statements.  The Comma Separated List of Values section may help:

http://www.sommarskog.se/arrays-in-sql-2005.html#CSV

The author suggests building a generic function that is called from your sql statement and you simply supply the list of values to the function.



0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

mpdillonAuthor Commented:
Unfortunately my list is not in a table. And it is the values from my list that I need returned. The values in the table are to be excluded.
The only way I see of doing this which is referenced in the article is to create a table and first insert my list into the table then do a query against the two tables. Is this correct?
0
JimFiveCommented:
Putting in a temp table or table variable would be an acceptable method, you could also do something like:

select 'a' WHERE 'a' not in (Select * from datetable)
union
select 'b' where 'a' not in (select * from datetable)
...
0
JimFiveCommented:
oops, that should be:
select 'a' WHERE 'a' not in (Select * from datetable)
union
select 'b' where 'b' not in (select * from datetable)
...
0
MilleniumaireCommented:
Yes, the function creates a table and this table is then used (seemlessly).  You could change the call to the function to be more specific to your application.

For example, create the function something like the following:

CREATE FUNCTION iter$simple_datelist_to_tbl (@list nvarchar(MAX))
   RETURNS @tbl TABLE (dateval nvarchar(20) NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (nvarchar)
         VALUES (substring(@list, @pos + 1, @valuelen))
      SELECT @pos = @nextpos
   END
  RETURN
END

Now call the function using values as follows:

SELECT d.DesiredDate
   FROM   DateTable1 d
  WHERE NOT EXISTS (SELECT 'x'
                                     FROM iter$simple_datelist_to_tbl(@ids) i
                                     WHERE p.DesiredDate = i.ThisDate)

Where @ids is your list of comma separated dates as a string.
All comparisons are done as character strings.
I haven't been able to test the above code as I don't currently have access to SQL Server.
This looks like an overly complicated solution, but once the function has been created, it can be used to access any list of dates.
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
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 2005

From novice to tech pro — start learning today.