首页 文章

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

提问于
浏览 次
3

这是我的查询：

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

这是数据：

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

我需要用1/0填充OKorNot列，具体取决于给定的月份日期是否在StartMonth-StartDate和StopMonth-StopDate之间 . 这是SQL Server 2000的方式 .

编辑

需要注意的是，数据中没有存储年份，月份日期可能从11月15日开始到1月15日结束，因此在12月31日和1月1日这个案例应该返回true .

sql sql-server tsql datetime sql-server-2000

3 回答

  • 2

    Using integer operations only and an imaginary 384-days calendar

    由于你的日期是月和日的组合，我试图为每个这样的组合创建一个整数，一个唯一的整数，也保留顺序 . 为了使计算尽可能简单，我们发明了一个新的日历，其中所有月份都有32天，我们的行为就好像我们的日期来自这个日历 . 然后，为了获得自1月1日以来已过去多少天，我们有以下公式：

    DaysPast = 32 * month + day

    （好吧，它应该是 32 * (month-1) + (day-1) 但这样更简单，我们只想比较相对于彼此的日期，而不是1月1日 . 结果对于每个日期仍然是唯一的） .

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

    SET @CHECK = 32 * @MM + @DD

    然后，我们在表格中计算所有日期（包括开始日期和结束日期）的 DaysPast ：

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

    然后，我们有两个案例 .

    • 第一种情况， Start = (8-Feb) 和 Stop = (23-Nov) .

    然后，第一个条件 @CHECK BETWEEN Start AND Stop 将为真，开始和停止之间的日期将为OK .

    第二个条件是False，所以没有更多日期可以 .

    • 第二种情况， Start = (23-Nov) 和 Stop = (8-Feb) . ：

    然后，第一个条件 @CHECK BETWEEN Start AND Stop 将为false，因为Start大于Stop，因此没有日期可以匹配此条件 .

    第二个条件 Stop < Start 将为真，所以我们也测试是否
    @CHECK 不是 BETWEEN (9-Feb) AND (22-Nov)
    匹配 (9-Feb) 之前或 (22-Nov) 之后的日期 .

    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

    如果您将日期存储为日期，则会更容易...

    无论如何，这样的事情 . 我没有测试过 . 你需要处理我已经完成的年份边界

    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

    编辑：为所有值添加“-1”：如果我们已经是Jan，请不要再添加一个月...

    回复于
  • 2
    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))
    回复于

相关问题