I've created the T-SQL code below to get the next nearest datetime value during the next 7 days.
The ScheduleDetail table schema is:
CREATE TABLE [dbo].[ScheduleDetail]
(
[uid] [int] IDENTITY(1,1) NOT NULL,
[scheduleRef] [int] NOT NULL,
[startTime] [datetime] NOT NULL,
[stopTime] [datetime] NOT NULL,
[validSunday] [bit] NOT NULL,
[validMonday] [bit] NOT NULL,
[validTuesday] [bit] NOT NULL,
[validWednesday] [bit] NOT NULL,
[validThursday] [bit] NOT NULL,
[validFriday] [bit] NOT NULL,
[validSaturday] [bit] NOT NULL,
[validHoliday] [bit] NOT NULL
)
The MIN(CAST(CAST(...) AS nextScheduleDateTime
is to get the date from that day of the week (after today) and add it to the schedule's startTime (as the startTime and stopTime fields are actually TIME
types stored as DATETIME
's. Don't ask me why...)
The only difference between each is the value @Sunday
, @Sunday + 1
, etc. and the column names: validSunday
, validMonday
, etc.
DECLARE @waypointId INT = 100;
DECLARE @Sunday INT = 1;
SELECT MIN(nextScheduleDateTime)
FROM
SELECT MIN(CAST(CAST(DATEADD(dd, (6 - DATEPART(dw, GETDATE()) + @Sunday) % 7 + 1, GETDATE()) AS DATE) AS DATETIME) + CAST(ScheduleDetail.startTime AS TIME)) AS nextScheduleDateTime
FROM WaypointEventLog INNER JOIN
Schedule ON Schedule.scheduleRef = WaypointEventLog.ScheduleID INNER JOIN
ScheduleDetail ON Schedule.scheduleRef = ScheduleDetail.scheduleRef
WHERE WaypointID = @waypointId
AND Completed = 0
AND Schedule.scheduleType = 6
AND (validSunday = 1 AND DATEPART(dw, GETDATE()) <> @Sunday)
UNION
SELECT MIN(CAST(CAST(DATEADD(dd, (6 - DATEPART(dw, GETDATE()) + @Sunday + 1) % 7 + 1, GETDATE()) AS DATE) AS DATETIME) + CAST(ScheduleDetail.startTime AS TIME)) AS nextScheduleDateTime
FROM WaypointEventLog INNER JOIN
Schedule ON Schedule.scheduleRef = WaypointEventLog.ScheduleID INNER JOIN
ScheduleDetail ON Schedule.scheduleRef = ScheduleDetail.scheduleRef
WHERE WaypointID = @waypointId
AND Completed = 0
AND Schedule.scheduleType = 6
AND (validMonday = 1 AND DATEPART(dw, GETDATE()) <> @Sunday + 1)
UNION
etc for each day of the week.....
Is there any way to simplify this, maybe convert these to a UDF somehow? Or Similar.
I could probably do something with EXEC
and LEFT()
the column name but that feels wrong.
validMonday
etc instead ofvalidWeekday
for the 5 work-week days andvalidWeekend
for Sunday/Saturday? \$\endgroup\$ScheduleDetails
and what you expect from your query? \$\endgroup\$