Databases
StackOverflow2013
Postsdbo
POHow to query for all events on a given day, using the SQL Server sysschedules model?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to query for all events on a given day, using the SQL Server sysschedules model?
    primarykey
    data
    text
    <p>Given this model: <a href="http://msdn.microsoft.com/en-us/library/ms178644.aspx" rel="nofollow">sysschedules</a>, 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...</p> <p>With the model given, there are certain fields that immediately jump to mind:</p> <ul> <li><strong>freq_type</strong> to get whether the even is daily, weekly, monthly, etc...</li> <li><strong>freq_interval</strong> to determine the interval, based on the freq_type</li> <li><strong>freq_relative_interval</strong> to determine if events are on 1st, 2nd, 3rd, etc... of month</li> <li><strong>freq_recurrence_factor</strong> to determine the weekly/monthly spacing between event occurrences</li> </ul> <p>If I wanted to query for all events that are today, Tuesday 28 Feb, 2012... what would that look like?</p> <p><strong>Update</strong> 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): <code>SELECT * FROM TableName WHERE (freq_type = 1 &amp;&amp; active_start_date = '2012-02-28') OR (freq_type = 4) OR (freq_type = 8 &amp;&amp; freq_interval = 4) OR (freq_type = 32 &amp;&amp; freq_interval = 3 &amp;&amp; freq_relative_interval = 16)</code></p> <ul> <li>freq_type of 1 means it's only once, get by the active_start_date</li> <li>freq_type of 4 means daily, get all those since they'd occur on this day too</li> <li>freq_type of 8 means weekly, get those events on freq_interval 4 (Tues)</li> <li>freq_type of 32 means monthly, relative - freq_interval 3 (Tues), freq_relative_interval 16 for last</li> </ul> <p>Here's what immediately jumps to mind as "<em>wrong</em>"</p> <p><strong>1.</strong> 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?</p> <p><strong>2.</strong> 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?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USChaddeus
    1. USChaddeus
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to query for all events on a given day, using the SQL Server sysschedules model?
      1. USChaddeus
      1. VTBountyStart
    2. VO
      singulars
      1. POHow to query for all events on a given day, using the SQL Server sysschedules model?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to query for all events on a given day, using the SQL Server sysschedules model?
      1. USAdrian Iftode
      1. VTFavorite
    1. COpossible duplicate of [How to get list of jobs that run on particular day in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/1245015/how-to-get-list-of-jobs-that-run-on-particular-day-in-microsoft-sql-server-2005)
      singulars
      1. POHow to query for all events on a given day, using the SQL Server sysschedules model?
      1. USTony
    2. CO@Tony - That question doesn't answer how to pick events scheduled for a specific date.
      singulars
      1. POHow to query for all events on a given day, using the SQL Server sysschedules model?
      1. USMatBailie
    3. CO@Dems - You are correct, it does not, I didn't read the other question fully. Sorry, I don't seem to be able to retract the close vote.
      singulars
      1. POHow to query for all events on a given day, using the SQL Server sysschedules model?
      1. USTony
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload