T-SQL simplify repeated query unions for each day of the week

Question

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.

Answer 1

Syntax Issue

The derived table in the FROM clause needs to be surrounded by parentheses:

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

Should be updated like this:

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

And then after the sub-queries:

    ) AS Subqueries

Simplifying sub-queries

I believe (but haven't tested, since I don't have your data and expected output) that the WHERE conditions can be updated like below, which would allow combining the sub-queries into one, eliminating the usage of UNION:

DECLARE @waypointId INT = 100;   
DECLARE @Sunday INT = 1;

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 ) OR 
        ( validMonday  = 1 AND  DATEPART(dw, GETDATE()) <> @Sunday + 1) OR 
        ( validTuesday = 1 AND  DATEPART(dw, GETDATE()) <> @Sunday + 2 ) OR 
        ( validWednesday = 1 AND  DATEPART(dw, GETDATE()) <> @Sunday + 3 ) OR 
        ( validThursday = 1 AND  DATEPART(dw, GETDATE()) <> @Sunday + 4 ) OR 
        ( validFriday = 1 AND  DATEPART(dw, GETDATE()) <> @Sunday + 5) OR 
        ( validSaturday = 1 AND  DATEPART(dw, GETDATE()) <> @Sunday + 6 ))

While it only contains the one table scheduleDetail, you can see this illustrated in this SQL fiddle. Feel free to update that fiddle and provide more details about the other tables, plus the data in those tables.

Programmatic way of generating the query above

One approach you could take is to define a CURSOR, and use that to loop through each field, adding the sub-queries to be joined via UNION to a long string. Originally I was thinking a varchar(5000) would be necessary but that was before I discovered the UNIONs could be eliminated.

DECLARE @name VARCHAR(50) -- field name 
DECLARE @rowNum varchar(10) -- row number 
DECLARE @sql VARCHAR(5000) = '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 ('

DECLARE db_cursor CURSOR FOR  -- fetch columns to loop over
SELECT COLUMN_NAME, ROW_NUMBER() OVER(ORDER BY ORDINAL_POSITION ASC) - 1 AS RowNum 
FROM INFORMATION_SCHEMA.columns 
WHERE table_NAME = 'ScheduleDetail' and COLUMN_NAME like 'valid%' and COLUMN_NAME not like '%holiday' 
ORDER BY ORDINAL_POSITION

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name, @rowNum  

WHILE @@FETCH_STATUS = 0   
BEGIN   
    --append WHERE condition
   SET @sql  = @sql + ' ('+ @name + ' = 1 AND DATEPART(dw, GETDATE()) <> @Sunday + ' + @rowNum + ' )'+char(10)+char(13)   -- + CR + LF

   FETCH NEXT FROM db_cursor INTO @name, @rowNum  
   if @@FETCH_STATUS=0 -- all except last condition
    BEGIN
        SET @sql  = @sql + 'OR'+char(10)+char(13)   -- + CR + LF
    END
    ELSE 
    BEGIN -- last condition
        SET @sql  = @sql + ')'+char(10)+char(13)   -- + CR + LF
    END
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

print @sql

That could be used to generate a Table-valued function or View.

Answer 2

You could rewrite it as a common table expression (CTE) using the relational operator UNPIVOT.

Test records

Results

Example SQL

PRINT '====================================================================='
PRINT 'Create temp table...'
PRINT '====================================================================='

IF OBJECT_ID('tempdb..#schedule_detail') IS NOT NULL DROP TABLE #schedule_detail
GO

    CREATE TABLE #schedule_detail
    (
      [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
    )

PRINT '====================================================================='
PRINT 'Insert test data...'
PRINT '====================================================================='

    INSERT INTO #schedule_detail (scheduleRef, startTime, stopTime, validSunday, validMonday, validTuesday, validWednesday, validThursday, validFriday, validSaturday, validHoliday) VALUES(101, '01-Jan-1900 08:00', '01-Jan-1900 13:00', 1, 0, 0, 0, 0, 0, 0, 0);
    INSERT INTO #schedule_detail (scheduleRef, startTime, stopTime, validSunday, validMonday, validTuesday, validWednesday, validThursday, validFriday, validSaturday, validHoliday) VALUES(102, '01-Jan-1900 09:00', '01-Jan-1900 14:00', 0, 0, 1, 0, 0, 0, 0, 0);
    INSERT INTO #schedule_detail (scheduleRef, startTime, stopTime, validSunday, validMonday, validTuesday, validWednesday, validThursday, validFriday, validSaturday, validHoliday) VALUES(103, '01-Jan-1900 10:00', '01-Jan-1900 15:00', 0, 0, 1, 0, 0, 0, 0, 0);
    INSERT INTO #schedule_detail (scheduleRef, startTime, stopTime, validSunday, validMonday, validTuesday, validWednesday, validThursday, validFriday, validSaturday, validHoliday) VALUES(104, '01-Jan-1900 11:00', '01-Jan-1900 16:00', 0, 0, 0, 1, 0, 0, 0, 0);
    INSERT INTO #schedule_detail (scheduleRef, startTime, stopTime, validSunday, validMonday, validTuesday, validWednesday, validThursday, validFriday, validSaturday, validHoliday) VALUES(105, '01-Jan-1900 12:00', '01-Jan-1900 17:00', 0, 0, 0, 0, 1, 0, 0, 0);
    INSERT INTO #schedule_detail (scheduleRef, startTime, stopTime, validSunday, validMonday, validTuesday, validWednesday, validThursday, validFriday, validSaturday, validHoliday) VALUES(106, '01-Jan-1900 13:00', '01-Jan-1900 18:00', 0, 0, 1, 0, 0, 0, 0, 0);
    INSERT INTO #schedule_detail (scheduleRef, startTime, stopTime, validSunday, validMonday, validTuesday, validWednesday, validThursday, validFriday, validSaturday, validHoliday) VALUES(107, '01-Jan-1900 14:00', '01-Jan-1900 19:00', 0, 0, 0, 0, 0, 0, 1, 1);

PRINT '====================================================================='
PRINT 'Unpivot data...'
PRINT '====================================================================='

;WITH
schedule_detail_pivot
AS
(
    SELECT 
          [uid]
        , [scheduleRef]
        , [startTime]
        , [stopTime]
        , [day_of_week]
        , [validHoliday]
        , [nextScheduleDateTime] = DATEADD(ms, DATEDIFF(ms, '00:00:00', [startTime]), CONVERT(DATETIME, DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), [day_of_week] - 2)))
    FROM
        (
          SELECT 
              [uid]
            , [scheduleRef]
            , [startTime]
            , [stopTime]
            , [Sunday] = ([validSunday] + 0) * [validSunday]
            , [Monday] = ([validMonday] + 1) * [validMonday]
            , [Tuesday] = ([validTuesday] + 2) * [validTuesday]
            , [Wednesday] = ([validWednesday] + 3) * [validWednesday]
            , [Thursday] = ([validThursday] + 4) * [validThursday]
            , [Friday] = ([validFriday] + 5) * [validFriday]
            , [Saturday] = ([validSaturday] + 6) * [validSaturday]
            , [validHoliday] 
          FROM #schedule_detail
        ) AS cp
        UNPIVOT 
        (
          day_of_week FOR days_of_week IN ([Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday])
        ) AS tbl
    WHERE 
        1=1
        AND [day_of_week] != 0
)
SELECT 
      [uid]
    , [scheduleRef]
    , [startTime]
    , [stopTime]
    , [day_of_week]
    , [validHoliday]
    , [nextScheduleDateTime]
FROM 
    schedule_detail_pivot