March 22, 2016 at 10:56 am
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
March 22, 2016 at 11:03 am
I already did change the type of @WeekDay to be varchar (not date)
March 22, 2016 at 11:16 am
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;
March 22, 2016 at 11:17 am
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
March 22, 2016 at 11:20 am
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