WHERE IF tomorrow = saturday, look at monday, ELSE look at tomorrow

Hey,

Thanks for taking the time to have a look at my query.

I basically need to pull all appointments set for tomorrows date, or if tomorrows date is a Saturday then give me all for Monday.

I've never had an SQL training but the below is what I have from a number of google's. Any help appreciated.

SELECT x FROM x
WHERE
JobStatuses.Description NOT LIKE N'Cancelled'
AND IF DATEPART(dateadd(day, datediff(day, 0, getdate()), 1)) = 7
Appointments.StartDateTime >= dateadd(day, datediff(day, 0, getdate()), 3)
ELSE Appointments.StartDateTime >= dateadd(day, datediff(day, 0, getdate()), 1)
AND IF DATEPART(dateadd(day, datediff(day, 0, getdate()), 1)) = 7
Appointments.StartDateTime < dateadd(day, datediff(day, 0, getdate()), 4)
ELSE Appointments.StartDateTime < dateadd(day, datediff(day, 0, getdate()), 2)

Thank you

is this part of a stored procedure. if so you can clean things up in the where clause by preparing the date filter before doing the actual query.

So in the stored procedure you can prepare a variable as follows

declare @startdate datetime ;
select @startdate = 
--Sat. depends what your DATEFIRST is
case datepart(weekday, dateadd(dd,1, getdate()) ) = 7 
then dateadd(dd,2, getdate()) --monday
else dateadd(dd,1, getdate())
end

then use that variable in the filter. cleans things up maybe?

Works correctly with any datefirst setting:

FROM x
CROSS APPLY (
    SELECT DATEDIFF(DAY, 0, GETDATE()) % 7 AS todays_day, 4 AS Friday
) AS alias1
WHERE
JobStatuses.Description NOT LIKE N'Cancelled'
AND Appointments.StartDateTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 
        CASE WHEN todays_day = Friday THEN 3 ELSE 1 END) AND
    Appointments.StartDateTime <  DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 
        CASE WHEN todays_day = Friday THEN 4 ELSE 2 END)