Блог

Я хочу запросить большую таблицу (более 50 тыс. строк, 200 строк / день) с условием where для вычисляемого значения.

В моей таблице у меня есть 2 столбца, которые содержат сдвиг (1 или 2 или 3) и дату (например: 2019-07-29 00:00:00.000);

Я хочу выбрать все строки из текущего сдвига.

Я хочу создать функцию, которая будет вычислять дату начала сдвига и дату окончания, чем будет сравнивать, если GETDATE() находится между ними, если true, то вернет строку.

Я пытаюсь создать эту функцию:

 Create function IsCurrentShift(@shift int,@shiftDate DateTime)
returns bit
as
Begin
    Declare @result DateTime2,
     @hours int,
     @shiftStartDate DateTime2,
     @shiftDueDate DateTime2

 set @hours =   (CASE 
    when @shift = 1 then 6
    when @shift = 2 then 14 
    when @shift = 3 then 22 
    else 6
    end)

 set @shiftStartDate = DATEADD(hour,@hours,@shiftDate)
 set @shiftDueDate=DATEADD(hour,8,@shiftStartDate)

 if @shiftStartDate < GETDATE() and @shiftDueDate > GETDATE()
        set @result = 1
 else 
        set @result = 0

    Return @result
End
 

Очевидно, не выполняется (я понятия не имею, как исправить).

Ошибка:

Сообщение 206, уровень 16, состояние 2, процедура IsCurrentShift, строка 22 [Строка запуска пакета 0]
Столкновение типов операндов: int несовместим с datetime2

Сообщение 206, уровень 16, состояние 2, процедура IsCurrentShift, строка 24 [Строка запуска пакета 0]
Столкновение типов операндов: int несовместим с datetime2

Эту функцию я хочу назвать так:

 Select * 
From tbl 
Where IsCurrentShift(shift, date) = 1;
 

У меня есть 2 вопроса:

1. Есть лучший подход к фильтрации моей таблицы? для получения текущих производственных заказов?
2. Можете ли вы помочь мне исправить эту функцию?

Моя таблица выглядит так:

 CREATE TABLE [dbo].[ProductionOrders]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Date] [datetime] NOT NULL,
    [ShiftNumber] [int] NOT NULL,
    ...
) 
 

Я хочу использовать эту функцию в представлении.

Скалярных функций лучше избегать, если только вы не используете SQL Server 2019, и даже тогда…

Вы можете записать это непосредственно в свой запрос или создать для этого встроенную табличную функцию (гораздо более производительную, чем скалярная функция).

 CREATE FUNCTION IsCurrentShift
    (@shift int, @shiftDate DateTime)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
(SELECT
    Result = CASE WHEN v2.shiftStartDate < GETDATE() and v2.shiftDueDate > GETDATE()
        THEN 1 ELSE 0 END
    FROM (SELECT hours = 
        CASE 
        when @shift = 1 then 6
        when @shift = 2 then 14 
        when @shift = 3 then 22 
        else 6
        end,
        ) v1
    CROSS APPLY (SELECT
        shiftStartDate = DATEADD(hour, v1.hours, @shiftDate),
        shiftDueDate =   DATEADD(hour,8   v1.hours, @shiftDate)
    ) v2;
 

Табличная функция логически возвращает целую таблицу каждый раз, когда она вызывается (на практике она встроена, поэтому намного быстрее). В этом случае мы возвращаем только одну вычисляемую строку. Таким образом, вы можете либо использовать его в коррелированном подзапросе, например, так:

 SELECT *
FROM tbl
WHERE (SELECT Result FROM IsCurrentShift(shift,date)) = 1;
 

Или вы можете также APPLY получить результат. Это даст вам новый столбец в вашем результирующем наборе:

 SELECT *
FROM tbl
CROSS APPLY IsCurrentShift(shift,date) IsCurrent
WHERE IsCurrent.Result = 1;
 

Вы также можете изменить функцию, чтобы она фактически возвращала время начала и окончания смены.

Вот номер текущего сдвига:

 hour(dateadd(hour, -6, getdate())) / 8   1
 

Откорректируйте шесть часов назад (поскольку смена 1 начинается в 6 утра.) Извлеките час и целое число разделите на восемь (все смены имеют одинаковую длину.)