根据当前日期和配置数据获取记录

I am working on automating the SMS sending part in my web application.

我正致力于在我的Web应用程序中自动化SMS发送部分。

SQL Fiddle Link

SQL小提琴链接

DurationType table stores whether the sms should be sent out an interval of Hours, Days, Weeks, Months. Referred in SMSConfiguration

DurationType表存储短信是否应以小时，天，周，月的间隔发送。在SMSConfiguration中引用

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

Bookings table Contains the original Bookings data. For this booking I need to send the SMS based on configuration.

预订表包含原始预订数据。对于此预订，我需要根据配置发送短信。

SMS Configuration. Which defines the configuration for sending automatic SMS. It can be send before/after. Before=0, After=1. DurationType can be Hours=1, Days=2, Weeks=3, Months=4

短信配置。其中定义了发送自动SMS的配置。它可以在之前/之后发送。在= 0之前，之后= 1。 DurationType可以是Hours = 1，Days = 2，Weeks = 3，Months = 4

Now I need to find out the list of bookings that needs SMS to be sent out at the current time based on the SMS Configuration Set.

现在我需要根据SMS配置集找出需要在当前时间发送短信的预订列表。

Tried SQL using UNION

使用UNION尝试SQL

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


-- 'SMS CONFIGURED FOR HOURS BASIS'

SELECT  B.Id AS BookingId, 
        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   (DATEDIFF(HOUR, @currentTime, B.StartTime) = SMS.Duration AND SMS.DurationType=1 AND BeforeAfter=0)
        OR
        (DATEDIFF(HOUR, B.StartTime, @currentTime) = SMS.Duration AND SMS.DurationType=1 AND BeforeAfter=1)


--'SMS CONFIGURED FOR DAYS BASIS'

UNION

SELECT  B.Id AS BookingId, 
        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 (DATEDIFF(DAY, @currentTime, B.StartTime) = SMS.Duration AND SMS.DurationType=2 AND BeforeAfter=0)
        OR
      (DATEDIFF(DAY, B.StartTime, @currentTime) = SMS.Duration AND SMS.DurationType=2 AND BeforeAfter=1)
--'SMS CONFIGURED FOR WEEKS BASIS'

UNION

SELECT  B.Id AS BookingId, 
        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 (DATEDIFF(DAY, @currentTime, B.StartTime)/7 = SMS.Duration AND SMS.DurationType=3 AND BeforeAfter=0)
        OR
      (DATEDIFF(DAY, B.StartTime, @currentTime)/7 = SMS.Duration AND SMS.DurationType=3 AND BeforeAfter=1)
--'SMS CONFIGURED FOR MONTHS BASIS'

UNION

SELECT  B.Id AS BookingId, 
        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   (dbo.FullMonthsSeparation(@currentTime, B.StartTime) = SMS.Duration AND SMS.DurationType=4 AND BeforeAfter=0)
         OR
        (dbo.FullMonthsSeparation(B.StartTime, @currentTime) = SMS.Duration AND SMS.DurationType=4 AND BeforeAfter=1)

Result

结果

Problem:

问题：

The SQL procedure will be running every 15 mins. Current query keep returning days/weeks/months records even for the current time '2014-07-12 11:30:00', '2014-07-12 11:45:00', etc

SQL过程将每15分钟运行一次。即使是当前时间'2014-07-12 11：30：00'，'2014-07-12 11:45:00'，当前查询仍保持返回天/周/月记录等

I want a single query that takes care of all Hours/Days/Weeks/Months calculation and I should be get records only one time when they meet the correct time. Otherwise I will be sending sms again and again every 15 mins for day/week/months records matched.

我想要一个单独的查询来处理所有小时/天/周/月计算，我应该只在他们满足正确时间时获得一次记录。否则，我将每隔15分钟一次又一次地发送短信，以获得匹配的日/周/月记录。

It should consider the following scenarios.

它应该考虑以下场景。

1. Hour, If booking is 10:15 A.M same day 9:15 A.M if it is before 1 hour configured

   小时，如果预订是10:15 A.M当天9:15 A.M如果在1小时之前配置

2. Day(24 Hour difference), If booking is 10:15 A.M 3rd day morning 10:15 A.M if it is configured after 3 days in SMSConfiguration

   日（24小时差异），如果预订是10:15 A.M第3天早上10:15 A.M如果在SMSConfiguration中3天后配置

3. Match Week. If booking is 10:15 A.M today(Wednesday) morning then after 14 days morning 10:15 A.M if it is configured after 2 weeks.

   比赛周。如果预订时间是今天（星期三）上午10:15，那么在14天早上10点15分之后，如果在2周后配置，则预订。

4. Month also same logic like above.

   月也和上面的逻辑相同。

6 个解决方案

#1

Try the simplified version FIDDLE , removed Union and used OR conditions

尝试简化版FIDDLE，删除Union并使用OR条件

LIST FOR HOURS - RUN Every 15 mins

小时清单 - 每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

LIST FOR DAYS/WEEKS/MONTHS - RUN Every Day Morning Once

列表/周/月 - 每天早晨运行一次

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

You seem to have forgotten to pick some columns to return.

你好像忘记选择一些专栏来回归。

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

This works

这很有效

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

There were two problems in your sql fiddle

你的sql小提琴有两个问题

1) you didn't set the value of currentTime

1）您没有设置currentTime的值

2) there were columns in your select statement

2）select语句中有列

Note: I took B.CategoryId as one of the column to fetch records, you can change it as per your requirement

注意：我将B.CategoryId作为获取记录的列之一，您可以根据您的要求进行更改

#4

Just swap the dates in DATEDIFF function

只需在DATEDIFF函数中交换日期即可

Change DATEDIFF(hour, B.StartTime, @currentTime) to DATEDIFF(hour, @currentTime, B.StartTime) - for HOUR only

将DATEDIFF（小时，B.StartTime，@ currentTime）更改为DATEDIFF（小时，@ currentTime，B.StartTime） - 仅限小时

Because yours one return the value in Negative (-1).

因为你的一个返回负值（-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))

#5

You can use the nested CASE expressions for achieve your results in single query. please try this,

您可以使用嵌套的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;

you can automate in two schedules one for getting booking details of Hourly sms and other one for Daily/Weekly/Monthly booking details.

您可以在两个时间表中自动完成一个用于获取每小时短信的预订详细信息，另一个用于每日/每周/每月预订详细信息。

#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))

The 2*SMS.BeforeAfter-1 converts Before(0) to -1 and After(1) to 1. Thus, adding or subtracting time from the BookingStartTime

2 * SMS.BeforeAfter-1将Before（0）转换为-1，将After（1）转换为1.因此，从BookingStartTime中添加或减去时间

[Edit]

[编辑]

The above solution should work with your data model as is. Below I propose simplifying the solution by changing your data model.

上述解决方案应该与您的数据模型一起使用。下面我建议通过更改数据模型来简化解决方案。

Option 1: Change Before and After meta data

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

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

Instead of Before = 0 and After = 1, use Before = -1 and After = 1. Then these can be used to change the direction of any date offset

而不是Before = 0和After = 1，使用Before = -1和After = 1.然后这些可以用来改变任何日期偏移的方向

For example:

例如：

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)

Updated query:

更新的查询：

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

Option 2: Allow 30-days = 1 month

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

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

On top of the Before/After change, if a 30 day period is close enough to represent a month, then all duration types could be converted to hours. The question is how particular your customers will be in regard to precision. If you are off by a day or two on a "3 month before" rule, I don't think you will have many complaints.

在更改之前/之后，如果30天的时间足以表示一个月，那么所有持续时间类型都可以转换为小时。问题是您的客户在精确度方面的具体程度。如果你在“3个月前”规则的一两天内离开，我认为你不会有很多抱怨。

So your DurationType table could look like this

所以你的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)

With these changes, the query could be modified to:

通过这些更改，可以将查询修改为：

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)