none
Repeating Events

    Question

  • Hello everybody.

    I wish to build a means of creating repeating events (records).
    This about a School Course Management application.

    A school Course (table) has duration, say commences in October 7th and completes in May 15th.
    There is a CourseClass table (a Course can have several Classes i.e. a Monday-Thursday class, a Tuesday-Friday class etc.).
    Every CourseClass has among others repeating Sessions (i.e. lessons). That is, a CourseClass can have a Session every Tuesday 5:00 pm to 5:45 pm and so on.

    A Session has fields such as Instructor, Subject (e.g. Maths), Weekday, Start & End time, Classroom, etc.

    I'd like to be able (after defining a Course Start/End dates) to easily create repeating Sessions (records) say, all Tuesdays 5:00 pm to 5:45 pm, in Classroom B3 from October 7th to May 15th.

    Thanks in advance,

    Corbex

    Tuesday, May 15, 2012 3:48 AM

Answers

  • You need to create  stored procedures for 1 and 2... Do you have an application that fulfill the data or is it homework?

    For 3 it could be a job or another stored procedure.... 

    1)  INSERT INTO Course (<columns>) VALUES (1,'Programming','20120516','20120521')

    2) INSERT INTO Course_Sessions (<columns>) VALUES (1,1,'Monday','08:00','12:00',20,'VB.NET','13')


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 16, 2012 6:34 AM

All replies

  • A Session table has to have CourseID column referencing Course Table...

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, May 15, 2012 5:35 AM
  • Hi Uri

    Yes indeed. I didn't mention it as I think of it as standard. I only mentioned some of the fields. CourseID and CourseClassID go without doubt as Session table FKs.

    It's the mechanism (a stored procedure I suppose) to create Sessions (records). It's like a repeating/reoccurring event in a calender.

    Thanks for you response

    Corbex

    Tuesday, May 15, 2012 6:30 AM
  • So what was a question? :-)

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, May 15, 2012 6:36 AM
  • The question is about the actual mechanism (query, stored procedure) to create the sessions (the repeating events that is) in question. I guess it should be something like an INSERT statement, but it should be reoccurring until it creates all Session table records (rows) so that it covers the period the course lasts.

    Sorry if I wasn't clear enough from the start...

    Corbex

    Tuesday, May 15, 2012 9:12 AM
  • http://www.w3schools.com/sql/sql_insert.asp

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, May 15, 2012 9:36 AM
  • Well, I understand the INSERT statement.

    The goal is a process that will "see" the course start & end dates and fill this time interval with events (sessions), say fill (create/insert) with sessions all Tuesdays, 5 pm to 5.45 pm from 10/10/2012 to 15/5/2013. That is the first session in this example will be at Tuesday 16/10/2012, next session will be at Tuesday 23/10/2012, and so on. The last session will be at Tuesday 14/5/2013. This should be done automatically:

    1. define Course Start & End Dates (the Course period).

    2. define session data for this Course (i.e. Weekday, Start & End Time, Instructor, Subject, Classroom, etc.)

    (normally a Course will have more than one Sessions, say, one in Tuesday and two sessions in Thursday).

    3. then a process will create automatically the sessions for the whole of the course's duration.

    Hope it's clear enough now. :-)

    Theodore


    • Edited by Corbex Tuesday, May 15, 2012 10:29 PM
    Tuesday, May 15, 2012 10:27 PM
  • You need to create  stored procedures for 1 and 2... Do you have an application that fulfill the data or is it homework?

    For 3 it could be a job or another stored procedure.... 

    1)  INSERT INTO Course (<columns>) VALUES (1,'Programming','20120516','20120521')

    2) INSERT INTO Course_Sessions (<columns>) VALUES (1,1,'Monday','08:00','12:00',20,'VB.NET','13')


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Wednesday, May 16, 2012 6:34 AM