Т-SQL для упрощения повторного запроса союзов на каждый день недели


Я создал Т-SQL-код ниже, чтобы получить следующее ближайшее значение datetime в течение следующих 7 дней.

В ScheduleDetail таблица-схема:

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
)

В MIN(CAST(CAST(...) AS nextScheduleDateTime чтобы получить дату от дня недели (после сегодня) и добавить его в расписание время начала (как StartTime и stopTime поля на самом деле TIME типы сохраняются как DATETIME'ы. Не спрашивайте меня, почему...)

Единственная разница между каждым значением @Sunday, @Sunday + 1и т. д. и имена столбцов: validSunday, validMondayи т. д.

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.....

Есть ли способ, чтобы упростить это, возможно, преобразовать их в какой-нибудь ОДС? Или Похожие.

Вероятно, я мог бы сделать что-то с EXEC и LEFT() в столбце имя, но это неправильно.



208
2
задан 30 января 2018 в 10:01 Источник Поделиться
Комментарии
1 ответ

Вопрос Синтаксиса

Производную таблицу в из пункт должен быть окружен скобками:


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

Должны быть обновлены, как это:

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

А потом после вложенных запросов:

    ) AS Subqueries

Упрощение вложенных запросов

Я полагаю (но не проверял, поскольку у меня нет ваших данных и ожидаемых выходных данных), что WHERE условия могут быть обновлены, как показано ниже, который позволит совмещать суб-запросов в один, исключая использование 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 ))

Хотя это только содержит одну таблицу scheduleDetailвы можете увидеть это показано в этот SQL скрипку. Не стесняйтесь, чтобы обновить эту скрипку и предоставить более подробную информацию о других таблиц, а также данных в этих таблицах.

Программный способ генерации выше запроса

Один подход вы могли бы взять это, чтобы определить курсор, и использовать это, чтобы перебрать каждое поле, добавление суб-запросы должны быть соединены через UNION для длинной строки. Изначально я думал varchar(5000) будет необходимо, но это было до того как я открыл UNIONмогут быть исключены.

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

Которые могут быть использованы для создания табличное значение функция или вид.

2
ответ дан 8 февраля 2018 в 07:02 Источник Поделиться