是否可以将另一个表中的值用作DATEADD函数中的间隔? [英] Is it possible to use values from another table as the interval in a DATEADD function?

问题描述

我有一个事件表，其中包含一个日期(smalldatetime). 我有一个间隔(整数)表(整数)(前几天)，用于发送提醒的时间-DATEADD(D, *interval*, GETDATE()).

I have a table of events containing a date (smalldatetime). I have a table of intervals (int) (days before) for when a reminder should be sent - DATEADD(D, *interval*, GETDATE()).

我正在尝试编写一条SQL语句以获取今天应该发送提醒的所有事件(基于DATEADD函数中的GETDATE().这不是我首先获取所有间隔并在循环，将每个间隔作为参数传递给DATEADD函数.

I'm trying to write an SQL statement to get all the events where a reminder should be sent today (based on GETDATE() from the DATEADD function. This is instead of me first getting all the intervals and running SQL in a loop, passing each interval as a parameter into the DATEADD function.

有什么想法我会怎么做吗?

Any ideas how I'd do this?

**dbo.events**
id (int) PK
date (smalldatetime)
customerID (int)

**dbo.intervals**
id (int) PK
daysBefore (int)
customerID (int)

推荐答案

答案是肯定的.由于缺少所提供的信息，因此我无法为您绘制此查询.但是，可以.

The answer is yes. I can't draw this query for you due to lack of information provided. But yes, you can.

以这个例子为例:

MyTable:
ID    Interval    Date
1     1           10-10-2001

SELECT ID, DATEADD(D, Interval, GETDATE()) AS NewDate FROM MyTable


SELECT e.*
FROM [events] e
INNER JOIN [interval] i on e.customerID = i.customerID
WHERE e.date = DATEADD(D, i.daysBefore, 
                   DATEADD(D, 0, 
                       DATEDIFF(D, 0, GETDATE())))

这篇关于是否可以将另一个表中的值用作DATEADD函数中的间隔?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！