/*
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

/*
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 DATETIME)
DECLARE @Routes  TABLE (RouteID INT, 
                        StaffID INT)
DECLARE @Pickup  TABLE (PickUpID INT IDENTITY (1,1), 
                        RouteID  INT, 
                        StaffID  INT, 
                        PickupDate DATETIME,
                        SatDayDelivery BIT)
-- Holds Today's date
DECLARE @TodaysDate DATETIME

--Generate Test data
INSERT INTO @Holiday VALUES ('08/21/2010')
INSERT INTO @Holiday VALUES ('08/22/2010')
INSERT INTO @Holiday VALUES ('08/24/2010')
INSERT INTO @Routes  VALUES (1, 1)
INSERT INTO @Routes  VALUES (1, 2)
INSERT INTO @Routes  VALUES (1, 3)
INSERT INTO @Routes  VALUES (2, 4)
INSERT INTO @Routes  VALUES (2, 5)

----Test Point# 1
--SELECT * FROM @Holiday
--SELECT * FROM @Routes

SET @TodaysDate = CONVERT(DATETIME, CONVERT(NCHAR,GETDATE(), 112))

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