For SQL experts: manage automatically updates using a frequency value
in [Server Configuration]
|
Prev: Setting a condition for MORE THAN ONE attribute to list ANOTHER di
Next: For SQL experts: manage automatically updates using a frequencyvalue
From: roberta on 23 May 2010 08:46 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 23 May 2010 13:02 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 23 May 2010 15:09 > 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 24 May 2010 17:48
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 |