The SQL2005 version of the script is:
/*
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
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins