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)