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