检查给定的月+日是否存在于包含月+日范围的数据中

Here is my query:

这是我的查询:

DECLARE @MM INT -- Current month
DECLARE @DD INT -- Current date

SET @MM = 1 -- For testing, set it to January
SET @DD = 1 -- For testing, set it to 01

SELECT xxxID, xxxFK, StartMonth, StartDate, StopMonth, StopDate, NULL AS OKorNOT
FROM xxxTable
ORDER BY xxxFK

And here is the data:

这里是数据:

xxxID            xxxFK       StartMonth  StartDate   StopMonth   StopDate    OKorNOT     
---------------- ----------- ----------- ----------- ----------- ----------- ----------- 
8                2287        11          15          1           2           NULL
4                2290        2           1           2           21          NULL
2                2306        9           15          10          31          NULL
3                2306        1           3           1           20          NULL
9                2661        11          15          1           3           NULL
10               2661        5           5           5           31          NULL
5                3778        6           2           9           5           NULL
6                3778        1           1           3           31          NULL
7                3778        5           10          5           31          NULL
1                3778        12          10          12          31          NULL

I need to populate OKorNot column with 1/0 depending on whether the given month-date lies between StartMonth-StartDate and StopMonth-StopDate. This is SQL Server 2000 by the way.

我需要用1/0填充OKorNot列，这取决于给定的月日是否位于StartMonth-StartDate和StopMonth-StopDate之间。顺便说一下，这是SQL Server 2000。

EDIT

The thing here to note that is that there are no years stored in the data and the months-dates may start in, say Nov-15 and end in Jan-15 so on Dec-31 and Jan-1 this case should return true.

这里需要注意的是，数据中没有存储年份，月数可能从11月15日开始，在1月15日结束，所以在12月31日和1月1日，这个情况应该会返回。

3 个解决方案

#1

Using integer operations only and an imaginary 384-days calendar

仅使用整数操作和虚构的384天日历。

Since your dates are combinations of month and day, I tried to create an integer for every such combination, an integer that is unique and also preserves order. To have calculations as simple as possible, we invent a new calendar where all months have exactly 32 days and we act as if our dates are from this calendar. Then to get how many days have past since 1st of January, we have the formula:

由于您的日期是月和日的组合，所以我尝试为每个这样的组合创建一个整数，这个整数是唯一的，并且保持顺序。为了尽可能简单地计算，我们发明了一种新的日历，每个月都有32天，我们的行为就好像我们的日期来自这个日历一样。然后得到从1月1日以来的天数，我们有公式:

DaysPast = 32 * month + day

DaysPast = 32 *月+日。

(OK, it should be 32 * (month-1) + (day-1) but this way it's simpler and we only want to compare dates relatively to one another, not to January 1st. And the result is still unique for every date).

(好的，应该是32 *(1月1日)+(1日)，但是这样比较简单，我们只想比较一下日期，而不是1月1日。而且结果对每个日期都是唯一的)。

Therefore, we first calculate the DaysPast for our check date:

因此，我们首先计算我们的检查日期的日期:

SET @CHECK = 32 * @MM + @DD

设置@CHECK = 32 * @MM + @DD

Then, we calculate the DaysPast for all dates (both start and stop ones) in our table:

然后，我们在我们的表中计算所有日期的DaysPast(开始和停止日期):

  ( SELECT *
         , (32 * StartMonth + StartDate) AS Start
         , (32 * StopMonth  + StopDate ) AS Stop
    FROM xxxTable
  ) AS temp

Then, we have two cases.

然后，我们有两种情况。

• First case, when Start = (8-Feb) and Stop = (23-Nov).
• 第一种情况，当开始=(8- 2月)和停止=(23- 11月)。

Then, the first condition @CHECK BETWEEN Start AND Stop will be true and the dates between Start and Stop will be OK.

然后，Start和Stop之间的第一个条件@CHECK将为true, Start和Stop之间的日期将是OK的。

The second condition will be False, so no more dates will be OK.

第二个条件是错误的，所以没有更多的日期是可以的。

• Second case, when Start = (23-Nov) and Stop = (8-Feb). :
• 第二种情况，开始=(23- 11月)，停止=(8- 2月)。:

Then, the first condition @CHECK BETWEEN Start AND Stop will be false because Start is bigger than Stop so no dates can match this condition.

然后，Start和Stop之间的第一个条件@CHECK将为false，因为Start大于Stop，所以没有日期可以匹配这个条件。

The second condition Stop < Start will be true, so we also test if
@CHECK is NOT BETWEEN (9-Feb) AND (22-Nov)
to match the dates that are before (9-Feb) or after (22-Nov).

第二个条件Stop < Start将为真，所以我们也测试@CHECK是否在(9- 2月)和(22- 11月)之间，以匹配(9- 2月)之前或之后(22- 11月)的日期。

DECLARE @CHECK INT
SET @CHECK = 32 * @MM + @DD

SELECT *
     , CASE WHEN
           @CHECK BETWEEN Start AND Stop 
           OR ( Stop < Start 
               AND @CHECK NOT BETWEEN Stop+1 AND Start-1
              )
         THEN 1
         ELSE 0
       END
       AS OKorNOT
FROM 
  ( SELECT *
         , (32 * StartMonth + StartDate) AS Start
         , (32 * StopMonth  + StopDate ) AS Stop
    FROM xxxTable
  ) AS temp
ORDER BY xxxFK

#2

It would be easier if you'd stored dates as, well, dates...

如果你能将日期存储为，嗯，日期……

Anyway, something like this. I haven't tested. And you need to deal with year boundary which I've done

不管怎么说,是这样的。我还没有测试。你需要处理我做过的年份边界

SELECT
    xxxID, xxxFK, StartMonth, StartDate, StopMonth, StopDate,
    CASE 
       WHEN
          FullStart <= FullStop AND 
            DATEADD(month, @MM-1, DATEADD(day, @DD-1, 0)) BETWEEN FullStart AND FullStop
                      THEN 1
       WHEN
          FullStart > FullStop AND 
            DATEADD(month, @MM-1, DATEADD(day, @DD-1, 0)) BETWEEN
                    FullStart AND DATEADD(year, 1, FullStop)
                      THEN 1  
       ELSE 0
    END AS OKOrNot
FROM
    (
    SELECT
        xxxID, xxxFK, StartMonth, StartDate, StopMonth, StopDate,
        DATEADD(month, StartMonth-1, DATEADD(day, StartDate-1, 0)) AS FullStart,
        DATEADD(month, StopMonth-1, DATEADD(day, StopDate-1, 0)) AS FullStop
    FROM xxxTable
    ) foo
ORDER BY xxxFK

edit: added "-1" to all values: if we're already Jan don't add another month...

编辑:添加“-1”到所有的值:如果我们已经是Jan，不要再增加一个月……

#3

SELECT *
FROM xxxTable
WHERE (StartMonth < StopMonth OR StartMonth = StopMonth AND StartDate<=StopDate)
    AND (@MM > StartMonth OR @MM = StartMonth AND @DD >= StartDate)
    AND (@MM < StopMonth OR @MM = StopMonth AND @DD <= StopDate)
    OR (StartMonth > StopMonth OR StartMonth = StopMonth AND StartDate>StopDate)
        AND ((@MM > StartMonth OR @MM = StartMonth AND @DD >= StartDate)
            OR (@MM < StopMonth OR @MM = StopMonth AND @DD <= StopDate))