Finding recurring dates. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Finding recurring dates.

Author

Topic

tsmooth
Starting Member

1 Post

Posted - 2006-09-11 : 09:22:18

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 09:30:27

[code]SELECT c.dt,
c.dayname,
c.monthname,
c.M,
c.D,
c.Y,
c.W
FROM dbo.Calendar c
WHERE c.dt >= '01/01/08' -- January 1, 2008
AND c.Y IN (2008, 2009)
AND DATEDIFF(day, '12/30/07', c.dt) % 21 = 1 -- December 30, 2007
ORDER BY c.dt[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources