See if the following does the trick. I have used TABLE variables instead of actual tables just for the POC.

/*

Major Assumption Here:

The "SatDayDelivery" BIT should be in the Pickup table and not in the Routes table.

The way I read it, the Routes table associates a staff member with a particular route.

It is the Pickup table that has the pickup related information, and therefore the "SatDayDelivery" should be in Pickup, not in Routes.

*/

-- Simulate the tables

DECLARE @Holiday TABLE (HolidayDate DATE)

DECLARE @Routes TABLE (RouteID INT,

StaffID INT)

DECLARE @Pickup TABLE (PickUpID INT IDENTITY (1,1),

RouteID INT,

StaffID INT,

PickupDate DATE,

SatDayDelivery BIT)

-- Holds Today's date

DECLARE @TodaysDate DATE

--Generate Test data

INSERT INTO @Holiday VALUES ('08/21/2010'), ('08/22/2010'), ('08/24/2010')

INSERT INTO @Routes VALUES (1, 1), (1, 2), (1, 3), (2, 4), (2, 5)

----Test Point# 1

--SELECT * FROM @Holiday

--SELECT * FROM @Routes

SET @TodaysDate = CONVERT(DATE, GETDATE(), 101)

--Test Point# 2 (Comment the line above and uncomment the one below to test)

--SET @TodaysDate = '08/23/2010'

INSERT INTO @Pickup (RouteID, StaffID, PickupDate, SatDayDelivery)

SELECT r.RouteID, r.StaffID, @TodaysDate AS PickupDate, CASE WHEN EXISTS (SELECT * FROM @Holiday WHERE HolidayDate = @TodaysDate) THEN 1

ELSE 0 END AS SatDayDelivery

FROM @Routes r

--Test Point# 3

SELECT * FROM @Pickup