zawhtway (8/21/2010)

---

Hi,

Here is the code I have done and tested and seems working.

Correct me if something is wrong. I'll have some more to do

use MobilePOD

Declare @chkHoliday int

Declare @chkTomorrow int

declare @DayName varchar(20)

Declare @DayNo int

SELECT @chkHoliday = count(*) from holidays where CONVERT(CHAR(10),freedate,101) = CONVERT(CHAR(10),GETDATE(),101)

SELECT @chkTomorrow = count(*) from holidays where CONVERT(CHAR(10),freedate,101) = CONVERT(CHAR(10),GETDATE()+1,101)

set @DayNo=(DATEPART(dw, getdate()) + @@DATEFIRST) % 7

Insert into Pickup (StaffID, RouteID, PickupDate)

select StaffID, RouteID, (getdate())

from Routes

where @chkHoliday = 0

Insert into Pickup (StaffID, RouteID, PickupDate)

select StaffID, RouteID, (getdate()+1)

from Routes

where @chkHoliday = 0 and Routes.SatDayDelivery = 1 and@DayNo = 6

Insert into Pickup (StaffID, RouteID, PickupDate)

select StaffID, RouteID, (getdate()+2)

from Routes

where @chkHoliday = 0 and Routes.SatDayDelivery = 1 and @chkTomorrow = 1 and @DayNo = 5