From: roberta on
Hi! I would like to create a SP for automatically manage "course
updates" on "Courses" table

I've a table with a list of Courses

CREATE TABLE [dbo].[Courses](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](200) NULL,
[IDCourse] [int] NULL,
[Update] [bit] NULL,
[RequiredUpdate] [bit] NULL,
[FrequencyUpdate] [int] NULL,
[ConcurrencyId] [timestamp] NULL,
CONSTRAINT [PK_Corsi] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Each course can has updates (with a frequency of X days).... for example
I can do a course today and have an update each 100 days from today

If tried to create this fields for manage the informations that I need:

Courses.Update is used for know if this a "course" of an "update of a
course"
Courses.IDCourse is the course father (used only for updates)
Courses.RequiredUpdate tell me if each course requires an update or not
(used only for courses, not for updates)
Courses.FrequencyUpdate is used for know the frequency of each update
(used only for courses, not for updates)



I've a table that allow me to know the courses linked to each user

CREATE TABLE [dbo].[UsersCourses](
[ID] [int] IDENTITY(1,1) NOT NULL,
[IDUser] [int] NULL,
[IDCourse] [int] NULL,
[IDCourseFather] [int] NULL,
[DateCourse] [datetime] NULL,
[ConcurrencyId] [timestamp] NULL,
CONSTRAINT [PK_DipendentiCorsi] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


UsersCourse.IDCourseFather used only for updates (for know the IDCorse
of each update


Now....

When I start to use DB, in courses table I don't have "courses updates"
records.... but only courses... also in users table I've only courses
linked to each user

do you remember that for each course I know the frequency of each update
and I know for each course if it's required an update

I would like create a SP that with a parameter YEAR execute this:

- create in Courses table X records of update courses for the year
selected (I think that we have to add a new field CourseYear for know if
I've already executed the SP for the selected year)
- for users that has a linked course (with RequiredUpdate=true) create X
records for the updates in the year

I can change tables adding new fields but I don't know how to create this SP

I think that the best solution is calculate +100 (for example) value on
last course/update (in each User/Course) .. because If I've a frequency
(for example) of 250, one year I could have 1 update and the second year
2 updates


Can you help me?
From: Dan Guzman on
There is a thread in microsoft.public.sqlserver.programming from "Sarah"
with this exact same question. I suggest you follow the thread there.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/



From: Dan Guzman on
> I still have this problem but I can't find a solution... :-((((

I see that Erland has asked you some questions in the Programming group
thread and is awaiting a response. Maybe answering that response will help
find a solution :-)

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/



From: Erland Sommarskog on
roberta (roby(a)tis.it) writes:
> I've not found replies in forums.asp.net
>
> do you mean here??

Moral: don't post your question in multiple forums. This may mean that while
someone is typing an answer in one forum, there is already a good response
in another. And you cannot keep track of where you posted your question
yourself.

I posted my reply in microsoft.public.sqlserver.programming.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx