April 13, 2011 at 1:12 am
I'm building an application to manage meetings held by a number of project teams.
I'm now at the stage of trying to schedule the meetings. When I first looked at the app I thought this would be quite easy - but I'm stuck.
Take Project Team A.
They have 1 annual 'Personnel Review' meeting
They have 4 quarterly 'Budget Review' meetings (second Wednesday of every 3rd month)
They have a monthly 'Progress Review' meeting (first Tuesday of every month)
They have bi-weekly 'Team Update' meetings on Mondays and Thursdays
There are 19 Project Teams at the moment and they all do different things. Some have Budget Review meetings every 2 months. Some have Progress Review meetings every fortnight etc. etc.
The basic table structure for Meetings and Projects is:
CREATE TABLE [dbo].[tblMeeting](
[MeetingID] [int] IDENTITY(1,1) NOT NULL,
[MeetingTypeID] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblProjectTeam](
[ProjectTeamID] [int] IDENTITY(1,1) NOT NULL,
[ProjectTeamName] [varchar](100) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblProjectTeamMeetings](
[ProjectTeamMeetingID] [int] IDENTITY(1,1) NOT NULL,
[ProjectTeamID] [int] NOT NULL,
[MeetingID] [int] NOT NULL
) ON [PRIMARY]
So I have ...
a table containing a list of Meetings with MeetingTypeID from a Lookup table that contains the various meeting types ('Budget Review', 'Progress Review' etc.)
a table containing a list of the Project Teams
a table containing a list of the types of meeting that each Project Team has
My questions are:
How/where do I store, for example, the fact that Project Team A's Budget Meeting takes place on the second Wednesday of every 3rd month? ... in such a way that it makes it easy to generate the dates for the meeting for the next year ... and in such a way that it is flexible enough to cater for all the other potential meeting intervals and 'day of week / certain day of month' requirements.
I've been drawing table layouts for days now and still can't come up with anything that works.
Thanks for any help.
April 13, 2011 at 11:04 am
I think something like this might work:
CREATE TABLE [dbo].[tblMeetingTypes](
[MeetingTypeID] [int] IDENTITY(1,1) NOT NULL,
[MeetingType] [varchar(20)] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblProjectTeam](
[ProjectTeamID] [int] IDENTITY(1,1) NOT NULL,
[ProjectTeamName] [varchar](100) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblProjectTeamMeetings](
[ProjectTeamMeetingID] [int] IDENTITY(1,1) NOT NULL,
[ProjectTeamID] [int] NOT NULL,
[MeetingTypeID] [int] NOT NULL
MeetingFrequencyID INt NOT NULL
DayOfWeek tinyint NOT NULL,
WeekOfMonth tinyint NULL
) ON [PRIMARY]
CREATE TABLE dbo.MeetingFrequencies
(
MeetingFrequencyID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_MeetingFrequencies PRIMARY Key,
MeetingFrequency VARCHAR(15) NOT NULL CONSTRAINT UC_MeetingFrequency UNIQUE
)
Meeting Frequency would weekly, monthly, quarterly, yearly or whatever. Then you could use DayOfWeek to set the day that it would happen in the week and WeekOfMonth for any meeting that meets monthly or more. You might have to have 2 entries for bi-weekly meetings. So a meeting the 1st tuesday of the month would have a monthly frequency, a day of month of whatever Tuesday is for your Language settings (3 for U.S. English) and 1 for week of month.
So you could do something like this to determine if a meeting is happening today:
SELECT
*,
CASE WHEN DATEPART(weekday, CURRENT_TIMESTAMP) = DayOfWeek AND DAY(CURRENT_TIMESTAMP)/7 = WeekOfMonth THEN 'Yes'
ELSE 'No'
END AS meeting_today
FROM
[dbo].[tblProjectTeamMeetings]
You could always get more complex and you'd need more logic than what I provided for yearly meetings, etc...
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 14, 2011 at 2:52 am
Thanks very much for your reply. That's a lot simpler and more flexible than what I had come up with so far.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply