根据当前日期和配置数据获取记录 [英] Get records based on Current Date and Configured Data

问题描述

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

，删除Union并使用OR条件

小时列表-每15分钟运行一次

  DECLARE @currentTime smalldatetime = '2014-07-12 11:15:00'
 
 
 
选择B.Id AS BookingId，C.Id，C.Name，
 B.StartTime AS BookingStartTime，@ currentTime作为CurrentTime，SMS.SMSText 
来自预订B内部联接
 SMSConfiguration SMS ON上的SMS.CategoryId = B.CategoryId或SMS.CategoryId是NULL 
左联接类别C ON C .Id = B.CategoryId 
 WHERE 
（DATEDIFF（MINUTE，@currentTime，B.StartTime）= SMS.Duration * 60 AND SMS.DurationType = 1 AND Befo reAfter = 0）
或
（DATEDIFF（MINUTE，B.StartTime，@currentTime）= SMS.Duration * 60 AND SMS.DurationType = 1 AND BeforeAfter = 1）
 
通过B.Id订购
 
 GO 
  

列出天数/每周/每月-每天早上运行一次

  DECLARE @currentTime smalldatetime ='2014-07-12 08： 00:00'
 
选择B.Id AS BookingId，C.Id，C.Name，
 B.StartTime AS BookingStartTime，@ currentTime作为CurrentTime，SMS.SMSText 
 FROM预订B内联接
 SMS配置SMS.CategoryId上的SMS = B.CategoryId或SMS.CategoryId为空
左联接类别C上C.Id = B.CategoryId 
 WHERE 
（ （（（DATEDIFF（DAY，@currentTime，B.StartTime）= SMS.Duration AND SMS.DurationType = 2）
或（DATEDIFF（DAY，@currentTime，B.StartTime）= SMS.Duration * 7 AND SMS。 DurationType = 3）
或（DATEDIFF（DAY，@currentTime，B.StartTime）= SMS.Duration * 30 AND SMS.DurationType = 4））
 AND BeforeAfter = 0）
 
或
（（（（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））
和BeforeAfter = 1）
 
按B.Id 
  

排序

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

SQL Fiddle Link

DurationType table stores whether the sms should be sent out an interval of Hours, Days, Weeks, Months. Referred in 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

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.

Tried SQL using UNION

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

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.

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

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

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.

4. Month also same logic like above.

解决方案

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

LIST FOR HOURS - RUN Every 15 mins

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

这篇关于根据当前日期和配置数据获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！