Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2008
    Posts
    4

    Unanswered: how to code TSQL?

    Hi,
    I am very new and never write a TSQL code before. I started learning from yesterday.
    I have those tables.
    Holiday
    - HolidayDate
    All the Holiday dates in a year will be populated.
    Routes
    - RouteID(Identity), StaffID, SatDayDelivery(bit)
    Pickup
    - PickUpID(Identity), RouteID, StaffID, PickupDate


    What I want to do is ---
    1) Copy all records from Routes to Pickup in every weekdays.
    2) And copy the (SatDayDelivery = True) records on Friday or the day before Holiday.

    After learning I know how to copy the data but I don't know how to do complex things. Still learning..

    Any help will be greatly appreciated.

    rgds,
    zhtway

  2. #2
    Join Date
    Feb 2008
    Posts
    4
    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.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •