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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
MilleniumaireConnect With a Mentor Commented:
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
 
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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
 
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
All Courses

From novice to tech pro — start learning today.