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

Question

我正在致力于在我的网络应用程序中实现短信发送部分的自动化。

SQL Fiddle Link

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

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

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

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

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

尝试使用 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)

结果

问题:

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.

应该考虑以下场景。

1. 时间(如果预订为当天上午 10:15)；如果配置在 1 小时之前，则为上午 9:15

2. 日(24 小时差异)，如果预订为上午 10:15，则第三天上午 10:15(如果在 SMSConfiguration 中配置为 3 天后)

3. 比赛周。如果预订是今天(周三)上午 10:15，则 14 天后上午 10:15(如果在 2 周后配置)。

4. 月份也与上面的逻辑相同。

Answer 1

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

数小时列表 - 每 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

几天/几周/几个月的列表 - 每天早上运行一次

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