How to query for all events on a given day, using the SQL Server sysschedules model?

Given this model: sysschedules, and assuming there were several events in the DB with varying combinations of recurrence patterns: daily, daily every 3 days, weekly on Tues. every 2 weeks, monthly on the 28th, monthly on the second Tues of every 2 months, yearly on Feb 28th, etc...

With the model given, there are certain fields that immediately jump to mind:

freq_type to get whether the even is daily, weekly, monthly, etc...
freq_interval to determine the interval, based on the freq_type
freq_relative_interval to determine if events are on 1st, 2nd, 3rd, etc... of month
freq_recurrence_factor to determine the weekly/monthly spacing between event occurrences

If I wanted to query for all events that are today, Tuesday 28 Feb, 2012... what would that look like?

Update Here's what I've come up with so far (in this example I want all "events" that occur on Feb 28 (which is a Tues): SELECT * FROM TableName WHERE (freq_type = 1 && active_start_date = '2012-02-28') OR (freq_type = 4) OR (freq_type = 8 && freq_interval = 4) OR (freq_type = 32 && freq_interval = 3 && freq_relative_interval = 16)

freq_type of 1 means it's only once, get by the active_start_date
freq_type of 4 means daily, get all those since they'd occur on this day too
freq_type of 8 means weekly, get those events on freq_interval 4 (Tues)
freq_type of 32 means monthly, relative - freq_interval 3 (Tues), freq_relative_interval 16 for last

Here's what immediately jumps to mind as "wrong"

1. What about events that are weekly, but more than one day, including Tuesday? For example, a weekly event which occurs every Tues/Thurs, will have a freq_interval of 20. How would I rewrite the query above to account for all possible combinations of freq_interval which can be Tues?

2. What about events which occur every n weeks/months? How do I know if it's this week/month's "on or off" time-frame?

Solution

Wasn't able to fully test. This doesn't cover every possibility. For instance, if an event happens every 3 days, some calculating will need to be done for the the dates and days, etc.

SELECT     * 
FROM       msdb.dbo.sysschedules 
WHERE   (active_start_date = 20120228) 
    OR (freq_type = 4 AND freq_interval = 1)  --every day
    OR (freq_type = 4 AND freq_interval = 14 AND datename(dw, convert(datetime, convert(varchar(8), active_start_date), 112)) = 'Tuesday') --every 14 days, starting on a Tuesday
        OR (freq_type = 8 AND ((freq_interval & 4) = 4))  -- every Tuesday
        OR (freq_type = 16 AND freq_interval = 28) -- every month on the 28th
        OR (freq_type = 32 AND freq_interval = 3 
            AND (freq_relative_interval = 8 OR freq_relative_interval = 16))  -- monthly on a Tuesday of the Fourth or Last week