Finding recurring dates.

T Smooth

Using SQL Server 2000 here and I have a table named "Calendar" that is an auxillary table created using scripts found on the web. Basically, the calendar contains an entry for every day for the next several years that has a datetime field, fields for the day of the month, month of the year, the year, the week of the year, etc. I'm trying to write a query or set of queries to return the recurring dates if the user of a web app decides to schedule something on a recurring basis much like recurring appointments in Outlook Calendar. The following query is what I am using to attempt to return the every 3rd Sunday starting on 01/01/08. SELECT c.dt, c.dayname, c.monthname, c.M, c.D, c.Y, c.W FROM dbo.Calendar c WHERE c.dayname IN ('Sunday') AND c.dt >= '01/01/08' AND c.Y IN (2008, 2009) AND 0 = ((SELECT COUNT(*) FROM dbo.Calendar c2 WHERE c.dt >= c2.dt AND c.dayname = c2.dayname) % 3) ORDER BY c.dt GO The query is returning every 3 sunday's but it's starting with Jan. 13th (the 2nd sunday) instead of Jan. 20th (the 3rd Sunday). After that, it is returning every 3rd Sunday but it is off by 1 week because of where it's starting. What am I doing wrong? Also, what kind of modifications might I need to make to this to support the other recurring event patterns that can be found in Outlook? I've searched the web a lot for recurring events and all I can seem to find is how to create the auxillary calendar table that I created but no info on how to retrieve the dates that a recurring event should fall on.

Eric Dahlvang

T-Smooth wrote:

Using SQL Server 2000 here and I have a table named "Calendar" that is an auxillary table created using scripts found on the web.

Please post the script for creating the Calendar table.

--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters

T Smooth

I generated the calendar using scripts taken from this FAQ. Search the pdf for "calendar table" and it should take you to the right FAQ question. There are scripts to add in all of the holidays as well. You first need to create a numbers table with a sufficient amount of numbers in there as well. http://www.aspfaq.com/downloads/ASPFAQ-2006-04-18.pdf[^] Here's the scripts if you don't want to look at the pdf. It will give you a table consisting of 30 years starting with 01/01/2000: CREATE TABLE dbo.Numbers ( Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED ) WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 20000 BEGIN INSERT dbo.Numbers DEFAULT VALUES END GO CREATE TABLE dbo.Calendar ( dt SMALLDATETIME NOT NULL PRIMARY KEY CLUSTERED, isWeekday BIT, isHoliday BIT, Y SMALLINT, FY SMALLINT, Q TINYINT, M TINYINT, D TINYINT, DW TINYINT, monthname VARCHAR(9), dayname VARCHAR(9), W TINYINT ) GO INSERT Calendar(dt) SELECT DATEADD(DAY, Number, '20000101') FROM dbo.Numbers WHERE Number <= 10957 ORDER BY Number GO UPDATE dbo.Calendar SET isWeekday = CASE WHEN DATEPART(DW, dt) IN (1,7) THEN 0 ELSE 1 END, isHoliday = 0, Y = YEAR(dt), FY = YEAR(dt), /* -- if our fiscal year -- starts on May 1st: FY = CASE WHEN MONTH(dt) < 5 THEN YEAR(dt)-1 ELSE YEAR(dt) END, */ Q = CASE WHEN MONTH(dt) <= 3 THEN 1 WHEN MONTH(dt) <= 6 THEN 2 WHEN MONTH(dt) <= 9 THEN 3 ELSE 4 END, M = MONTH(dt), D = DAY(dt), DW = DATEPART(DW, dt), monthname = DATENAME(MONTH, dt), dayname = DATENAME(DW, dt), W = DATEPART(WK, dt) GO ALTER TABLE dbo.Calendar ADD UTCOffset TINYINT NULL GO SET NOCOUNT ON DECLARE @dt SMALLDATETIME DECLARE @offset TINYINT SET @offset = 5 DECLARE c CURSOR LOCAL STATIC READ_ONLY FOR SELECT dt FROM dbo.Calendar ORDER BY dt OPEN c FETCH NEXT FROM c INTO @dt WHILE @@FETCH_STATUS = 0 BEGIN IF DATENAME(dw, @dt)='Sunday' AND DATEPART(DAY, @dt) <= 7 AND DATENAME(MONTH, @dt) = 'April' SET @offset = 4 IF DATENAME(dw, @dt)='Sunday' AND DATEPART(DAY, @dt) >= 25 AND DATENAME(MONTH, @dt) = 'October' SET @offset = 5 UPDATE dbo.Calendar SET UTCOffset = @offset WHERE dt = @dt FETCH NEXT FROM c INTO @dt END CLOSE c DEALLOCATE c GO CREATE FUNCTION dbo.ISOWeek ( @dt SMALLDATETIME ) RETURNS TINYINT AS BEGIN DECLARE @ISOweek TINYINT SET @ISOweek = DATEPART(WEEK,@dt)+1 -DATEPART(WE

Eric Dahlvang

SELECT c.dt, c.dayname, c.monthname, c.M, c.D, c.Y, c.W
FROM dbo.Calendar c
WHERE c.dayname IN ('Sunday')
AND c.dt >= '01/01/08'
AND c.Y IN (2008, 2009)
AND 2 = (SELECT COUNT(*) FROM dbo.Calendar c2
WHERE c.y = c2.y
AND c.m = c2.m
AND c2.dt < c.dt
AND c.dayname = c2.dayname)
ORDER BY c.dt

--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters

T Smooth

This only gets me the 2nd sunday of each month, not necessarily every 2 weeks from a certain start date.

Eric Dahlvang

Oh, I guess I misunderstood. Sorry.

SELECT c.dt, c.dayname, c.monthname, c.M, c.D, c.Y, c.W
FROM dbo.Calendar c,
(select top 1 dt + 14 as dt
from calendar c3
where dayname = 'Sunday' and dt >= '01/08/08' order by dt) as c2
WHERE c.dt >= c2.dt
AND c.Y IN (2008, 2009)
AND datediff(dd,c2.dt,c.dt ) % 21 = 0
ORDER BY c.dt

--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters

Michael Potter

Not sure why you would want to set up a table containing each date when the calculations are just as easy to write. Have you considered adding a few more fields to your table: 1) DayNameIndexForMonth (i.e. 1st Sunday, 2nd Sunday, 3rd Sunday) 2) DayOfYear (i.e. This is the 74th day of the year) 3) Quarter (i.e. This day is in the 3rd Quarter) This might make your queries a lot easier. On the other hand, you can always calculate on the fly. With only 365 iterations per year, loops should execute quickly enough. Knowing that the 3rd 'Day Name' of every month will fall between the 15th and 21st day you can code it like this:

CREATE PROCEDURE RecurringDates_ThirdSunday
(
    @Start DATETIME,
    @End DATETIME
)
AS
    DECLARE @curDt DATETIME
    SET @curDt = @Start
    
    DECLARE @t TABLE (GoodDate DATETIME NOT NULL)
    
    WHILE @curDt <= @End
    BEGIN
        IF DATEPART(dw,@curDt) = 1
        BEGIN 
            IF DATEPART(dd,@curDt) BETWEEN 15 AND 21
                BEGIN
                    INSERT INTO @t (GoodDate) VALUES (@curDt)
                END
        END
    
        SET @curDt = DATEADD(dd,1,@CurDt)
    END
    
    SELECT *
    FROM @t

Eric Dahlvang

I guess he wants every third Sunday from the start date, not every third Sunday of the month. I had the same misunderstanding. But, I like your method better than creating a table with every date between now and some arbitrary future date.

CREATE PROCEDURE RecurringDates_ThirdSunday
(
@Start DATETIME,
@End DATETIME
)
AS
DECLARE @curDt DATETIME
SET @curDt = @Start

DECLARE @t TABLE (GoodDate DATETIME NOT NULL)

WHILE DATEPART(dw,@curDt) != 1
BEGIN
    SET @curDt = DATEADD(dd,1,@CurDt)
END

SET @curDt = DATEADD(dd,14,@CurDt) 

WHILE @curDt <= @End
BEGIN
INSERT INTO @t (GoodDate) VALUES (@curDt)
    SET @curDt = DATEADD(dd,21,@CurDt)
END

SELECT \*
FROM @t

--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters