Получить записи на основе текущей даты и настроенных данных

Ответ 1

Попробуйте упрощенную версию FIDDLE, удалили союз и использовали условия OR

СПИСОК ДЛЯ ЧАСОВ - RUN Каждые 15 минут

DECLARE @currentTime smalldatetime = '2014-07-12 11:15:00'



SELECT  B.Id AS BookingId, C.Id, C.Name,
        B.StartTime AS BookingStartTime,@currentTime As CurrentTime,  SMS.SMSText
FROM    Bookings B INNER JOIN
        SMSConfiguration SMS ON SMS.CategoryId = B.CategoryId OR SMS.CategoryId IS NULL
        LEFT JOIN Category C ON C.Id=B.CategoryId
WHERE   
        (DATEDIFF(MINUTE, @currentTime, B.StartTime) = SMS.Duration*60 AND SMS.DurationType=1 AND BeforeAfter=0)
        OR
        (DATEDIFF(MINUTE, B.StartTime, @currentTime) = SMS.Duration*60 AND SMS.DurationType=1 AND BeforeAfter=1)

Order BY B.Id

GO

СПИСОК ДЛЯ ДНЕЙ/НЕДЕЛЕЙ/МЕСЯЦЕВ - RUN Каждый день Утро Однажды

DECLARE @currentTime smalldatetime = '2014-07-12 08:00:00'

SELECT  B.Id AS BookingId, C.Id, C.Name,
        B.StartTime AS BookingStartTime,@currentTime As CurrentTime,  SMS.SMSText
FROM    Bookings B INNER JOIN
        SMSConfiguration SMS ON SMS.CategoryId = B.CategoryId OR SMS.CategoryId IS NULL
        LEFT JOIN Category C ON C.Id=B.CategoryId
WHERE   
    (((DATEDIFF(DAY, @currentTime, B.StartTime) = SMS.Duration AND SMS.DurationType=2)
    OR (DATEDIFF(DAY, @currentTime, B.StartTime) = SMS.Duration*7 AND SMS.DurationType=3)
    OR (DATEDIFF(DAY, @currentTime, B.StartTime) = SMS.Duration*30 AND SMS.DurationType=4))
     AND BeforeAfter=0)

    OR
    (((DATEDIFF(DAY, B.StartTime, @currentTime) = SMS.Duration AND SMS.DurationType=2)
    OR (DATEDIFF(DAY, @currentTime, B.StartTime) = SMS.Duration*7 AND SMS.DurationType=3)
    OR (DATEDIFF(DAY, @currentTime, B.StartTime) = SMS.Duration*30 AND SMS.DurationType=4))
     AND BeforeAfter=1)

Order BY B.Id

Ответ 2

Кажется, вы забыли выбрать несколько столбцов для возврата.

DECLARE @currentTime smalldatetime = '2014-07-12 09:15:00'; 

SELECT [col1], [col2], [etcetera]
FROM

Bookings B
INNER JOIN SMSConfiguration SMS ON SMS.CategoryId=B.CategoryId
WHERE DATEDIFF(hour,B.StartTime,@currentTime)=1

Ответ 3

Просто swap the dates in DATEDIFF функция

Измените DATEDIFF (час, B.StartTime, @currentTime) на DATEDIFF (час, @currentTime, B.StartTime) - только для HOUR

Поскольку ваш возвращает значение в Отрицательный (-1).

DECLARE @currentTime smalldatetime = '2014-07-12 09:15:00'

SELECT  B.Id AS BookingId, @currentTime AS CurrentTime,
        B.StartTime AS BookingStartTime,  SMS.SMSText
FROM    Bookings B INNER JOIN
        SMSConfiguration SMS ON SMS.CategoryId = B.CategoryId OR SMS.CategoryId IS NULL
WHERE   (SMS.CategoryId IS NOT NULL AND 
         DATEDIFF(HOUR, @currentTime, B.StartTime) = SMS.Duration) OR 
        (SMS.CategoryId IS NULL AND 
         (DATEDIFF(DAY, B.StartTime, @currentTime) = SMS.Duration OR 
          DATEDIFF(MONTH, B.StartTime, @currentTime) = SMS.Duration))

Ответ 4

Это работает

DECLARE @currentTime smalldatetime
set @currentTime= '2014-07-12 09:15:00'



SELECT B.CategoryId FROM
Bookings B
INNER JOIN SMSConfiguration SMS ON SMS.CategoryId=B.CategoryId
WHERE DATEDIFF(hh,B.StartTime,@currentTime)=1

В вашем скрипте sql были две проблемы.

1) вы не установили значение currentTime

2) в вашем операторе select были столбцы

Примечание. Я взял B.CategoryId как один из столбцов для извлечения записей, вы можете изменить его в соответствии с вашим требованием

Ответ 5

Вы можете использовать вложенные выражения CASE для достижения ваших результатов в одном запросе. попробуйте это,

    DECLARE @currentTime smalldatetime = '2014-07-12 11:15:00'





    SELECT  B.Id AS BookingId,
    SMS.Duration,
    B.StartTime AS BookingStartTime,
    @currentTime As CurrentTime,  
    SMS.SMSText
    FROM Bookings B INNER JOIN
    SMSConfiguration SMS 
    ON SMS.CategoryId = B.CategoryId 
    OR SMS.CategoryId IS NULL


    WHERE 
       SMS.Duration = 
       CASE 
       WHEN SMS.DurationType = 1 THEN  --'SMS CONFIGURED FOR HOURS BASIS'


           CASE WHEN BeforeAfter = 0 THEN 
                DATEDIFF(HOUR, @currentTime, B.StartTime)       
           ELSE 
                DATEDIFF(HOUR, B.StartTime, @currentTime)
           END 

       WHEN SMS.DurationType = 2 THEN  --'SMS CONFIGURED FOR DAY BASIS'


           CASE WHEN BeforeAfter = 0 THEN 
              DATEDIFF(DAY, @currentTime, B.StartTime)         
          ELSE 
              DATEDIFF(DAY, B.StartTime, @currentTime)
          END 

     WHEN SMS.DurationType = 3 THEN  --'SMS CONFIGURED FOR WEEK BASIS'


          CASE WHEN BeforeAfter = 0 THEN 
              DATEDIFF(DAY, @currentTime, B.StartTime)/7        
          ELSE 
              DATEDIFF(DAY, B.StartTime, @currentTime)/7 
          END 

      ELSE 
          CASE WHEN BeforeAfter = 0 THEN -- 'SMS CONFIGURED FOR MONTH BASIS'
              dbo.FullMonthsSeparation(@currentTime, B.StartTime)        
          ELSE 
              dbo.FullMonthsSeparation(B.StartTime, @currentTime) 
          END    

      END 

    ORDER BY BOOKINGID;

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

Ответ 6

Try:

DECLARE @currentTime smalldatetime = '2014-07-12 09:15:00'

SELECT  B.Id AS BookingId, 
        B.StartTime AS BookingStartTime,  
        @currentTime CURRENT_DT,
        SMS.SMSText
FROM    Bookings B INNER JOIN
        SMSConfiguration SMS ON SMS.CategoryId = B.CategoryId OR SMS.CategoryId IS NULL
WHERE  (    SMS.DurationType = 1 
        AND @currentTime = DATEADD(HOUR, (2*SMS.BeforeAfter-1)*SMS.Duration, B.StartTime))
     OR 
       (    SMS.DurationType = 2
        AND @currentTime = DATEADD(DAY, (2*SMS.BeforeAfter-1)*SMS.Duration, B.StartTime))
     OR
       (    SMS.DurationType = 3
        AND @currentTime = DATEADD(WEEK, (2*SMS.BeforeAfter-1)*SMS.Duration, B.StartTime))
     OR
       (    SMS.DurationType = 4
        AND @currentTime = DATEADD(MONTH, (2*SMS.BeforeAfter-1)*SMS.Duration, B.StartTime))

2 * SMS.BeforeAfter-1 преобразует Before (0) в -1 и After (1) в 1. Таким образом, добавление или вычитание времени из ResStartTime

[изменить]

Вышеупомянутое решение должно работать с вашей моделью данных, как есть. Ниже я предлагаю упростить решение, изменив вашу модель данных.

Вариант 1: изменить метаданные до и после

http://sqlfiddle.com/#!3/6e9e9/1

Вместо Before = 0 и After = 1 используйте Before = -1 и After = 1. Затем они могут использоваться для изменения направления любого смещения даты

Например:

INSERT INTO [dbo].[SMSConfiguration]
           ([CategoryId],[SMSText],[BeforeAfter],[Duration],[DurationType],[IsActive])
     VALUES
           (1,'Before 1 hour',-1,1,1,1)-- 

INSERT INTO [dbo].[SMSConfiguration]
           ([CategoryId],[SMSText],[BeforeAfter],[Duration],[DurationType],[IsActive])
     VALUES
           (NULL,'After 1 hour (no category id))',1,1,1,1)

Обновленный запрос:

DECLARE @currentTime smalldatetime = '2014-07-12 09:15:00'

SELECT  B.Id AS BookingId, 
        B.StartTime AS BookingStartTime,  
        @currentTime CURRENT_DT,
        SMS.SMSText
FROM    Bookings B INNER JOIN
        SMSConfiguration SMS ON SMS.CategoryId = B.CategoryId OR SMS.CategoryId IS NULL
WHERE  (    SMS.DurationType = 1 
        AND @currentTime = DATEADD(HOUR, SMS.BeforeAfter*SMS.Duration, B.StartTime))
     OR 
       (    SMS.DurationType = 2
        AND @currentTime = DATEADD(DAY, SMS.BeforeAfter*SMS.Duration, B.StartTime))
     OR
       (    SMS.DurationType = 3
        AND @currentTime = DATEADD(WEEK, SMS.BeforeAfter*SMS.Duration, B.StartTime))
     OR
       (    SMS.DurationType = 4
        AND @currentTime = DATEADD(MONTH, SMS.BeforeAfter*SMS.Duration, B.StartTime))

Вариант 2: Разрешить 30 дней = 1 месяц

http://sqlfiddle.com/#!3/808cd/1

В дополнение к изменению до/после, если 30-дневный период достаточно близок для представления месяца, то все типы продолжительности могут быть преобразованы в часы. Вопрос в том, насколько конкретными будут ваши клиенты в отношении точности. Если вы отключены на день или два в правиле "за 3 месяца до", я не думаю, что у вас будет много жалоб.

Итак, ваша таблица DurationType может выглядеть так:

CREATE TABLE [dbo].[DurationType](
[Id] [int] NOT NULL PRIMARY KEY,
[DurationType] VARCHAR(10) NOT NULL,
[HourConversion] [int] NOT NULL
)
GO

INSERT INTO [dbo].[DurationType]([Id],[DurationType],[HourConversion])
       VALUES(1,'Hours',1)
INSERT INTO [dbo].[DurationType]([Id],[DurationType],[HourConversion])
       VALUES(2,'Days',24)
INSERT INTO [dbo].[DurationType]([Id],[DurationType],[HourConversion])
       VALUES(3,'Weeks',24*7)
INSERT INTO [dbo].[DurationType]([Id],[DurationType],[HourConversion])
       VALUES(4,'Months',24*30)

С этими изменениями запрос может быть изменен на:

DECLARE @currentTime smalldatetime = '2014-07-12 09:15:00'

SELECT  B.Id AS BookingId, 
        B.StartTime AS BookingStartTime,  
        @currentTime CURRENT_DT,
        SMS.SMSText
FROM    Bookings B 
INNER JOIN
        SMSConfiguration SMS ON SMS.CategoryId = B.CategoryId OR SMS.CategoryId IS NULL
INNER JOIN
        DurationType DT ON DT.Id = SMS.DurationType
WHERE  @currentTime = DATEADD(HOUR, SMS.BeforeAfter*SMS.Duration*DT.HourConversion, B.StartTime)