How to skip date where holiday code = 1

Question

Using SQL Server 2000

Table1

Date        Holiday

23-02-2009    0 
24-02-2009    1
25-02-2009    0

Table2

ID Date

01 23-02-2009     
01 24-02-2009    
02 25-02-2009     
…,

Here I want to skip the date where Holiday =1

Expected Output

01 23-02-2009
02 25-02-2009

How to make a query for this condition?

Answer 1

No idea what data types you're using, but at first glance it looks as simple as:

select Table2.ID, Table2.Date from 
Table2 inner join Table1 on Table2.Date = Table1.Date
where Table1.Holiday <> 1

or

select Table2.ID, Table2.Date
from Table2
Where Table2.Date not in (Select Date From Table1 Where Holiday = 1)

Answer 2

Try this

SELECT  *
FROM    TABLE2 t2 
WHERE   t2.Date NOT IN (SELECT Date FROM TABLE1 WHERE Holiday = 1)

If table has no other value than marking holidays, why have the holiday column, and why have dates that are not holidays?

we have an holiday table as

CREATE TABLE [dbo].[METADATA_CALENDAR](
    [CalendarDate] [datetime] NOT NULL,
    [Description] [nvarchar](500) NULL,
    [CalendarType] [nvarchar](100) NOT NULL,
    [CalendarLocal] [nvarchar](50) NOT NULL,
)

which can allow you various calendar types, and for various countries.

Answer 3

Take your pick:

SELECT t2.id, 
       t2.date
  FROM TABLE_2 t2
  JOIN TABLE_1 t1 ON t1.date = t2.date
                 AND t1.holiday = 0

...or using EXISTS:

SELECT t2.id, 
       t2.date
  FROM TABLE_2 t2
 WHERE EXIST(SELECT NULL
              FROM TABLE_1 t1
             WHERE t1.date = t2.date
               AND t1.holiday = 0)

Answer 4

Don't know DB vendor specific details, but something like:

select t2.date 
from table2 t2 
where t2.date in (select t1.date 
                  from table1 t1 
                  where t1.holiday <> 1)