Tricky SQL

• Rob Sawnic

  SSC Eights!

  Points: 940

  More actions

  March 11, 2004 at 8:40 am

  #107692

  I want to set a holiday flag on a temp table if a date on my temp table(@Orig_Calendar) is in the holiday schedule. The following sql works fine. 

  UPDATE @Orig_Calendar

     SET holiday = 1

     WHERE orig_start IN (SELECT 

     holiday_date from HOLIDAY_SCHEDULE_DATES hsd, @Orig_Calendar oc

     WHERE hsd.holiday_schedule_id = @holiday_sched)

  HOWEVER, if the holiday falls on a Sunday, then I want to flag the following Monday instead of the Sunday on the temp table (@Orig_Calendar) if a following Monday exists. How can I do this?

   

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  March 11, 2004 at 9:11 am

  #498315

  You could use the DATEPART(dw,date) or DATENAME(dw,date) function.  For best readability, perhaps something like this:

  UPDATE c SET Holiday = 1

  FROM @Orig_Calendar c JOIN Holiday_Schedule_Dates h ON c.Orig_Start =

    CASE DATENAME(dw,h.Holiday_Date) WHEN 'Sunday' THEN h.Holiday_Date + 1 ELSE h.Holiday_Date END

   AND h.Holiday_Schedule_Id = @Holiday_Sched

  
  
  --Jonathan