This noob needs some help

  • I am not exactly a "noob" as I am a .net developer, however I lack in SQL query knowledge and need some help with a select statement. I created a stored procedure that gets appointments in the future based on the date today. If it is Monday through Wednesday, it can safely get appointments that are two days out. However if the date that this procedure is run is on Thursday or Friday, I need to go out 4 days to skip the weekend. So I have this and let me know my syntax errors and if this can be handled a better way. Thanks!

    DECLARE @StartDate DATE

    DECLARE @WeekDay DateTime

    SET @WeekDay = DATENAME(weekday, GETDATE())

    SELECT CASE @WeekDay

    WHEN 'Thursday' THEN @StartDate=GETDATE()+4

    WHEN 'Friday' THEN @StartDate=GETDATE()+4

    ELSE @StartDate=GETDATE()+2

    END

  • I already did change the type of @WeekDay to be varchar (not date)

  • You were very close Brad, you just don't need to set the variable equal to each time for the case statement.

    Just set it equal to at the start and then do the case.

    Try this out and see what I mean:

    DECLARE @StartDate DATE;

    DECLARE @WeekDay VARCHAR(10);

    SET @WeekDay = DATENAME(weekday, GETDATE());

    SELECT @startdate =

    CASE @WeekDay

    WHEN 'Thursday' THEN GETDATE()+4

    WHEN 'Friday' THEN GETDATE()+4

    ELSE GETDATE()+2

    END;

    SELECT @WeekDay, @StartDate;

  • Quick suggestion based on the fact that Zero date is Monday 1900-01-01.

    😎

    SELECT

    CONVERT(

    DATE

    ,DATEADD(

    DAY

    ,CASE

    WHEN DATEDIFF(DAY,0,GETDATE()) % 7 < 3 THEN 2

    ELSE 4

    END

    ,GETDATE()

    )

    ,0

    ) AS NEXT_AVAILABLE_APPOINTMENT_DAY

    --QUICK TEST

    DECLARE @NDATE DATE = '2016-03-24';

    SELECT

    CONVERT(

    DATE

    ,DATEADD(

    DAY

    ,CASE

    WHEN DATEDIFF(DAY,0,@NDATE) % 7 < 3 THEN 2

    ELSE 4

    END

    ,@NDATE

    )

    ,0

    ) AS NEXT_AVAILABLE_APPOINTMENT_DAY

    Edit: Typo

  • Thanks! And yes, it works

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply