Check if given month+date is present in the data containing range of month+date

Question

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.

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.

Answer 1

Using integer operations only and an imaginary 384-days calendar

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:

DaysPast = 32 * month + day

(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).

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

SET @CHECK = 32 * @MM + @DD

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

  ( 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).

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

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

• Second case, when Start = (23-Nov) and Stop = (8-Feb). :

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.

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).

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