I am very new to SQL. I did a Udemy course a while ago, other than that I havent done much with it, so fairly new to this as a concept even. I have done some work in MS Access so some is straight forward...some not so much.
I run a cleaning type business. The issue I have is I am wanting to generate the following months jobs. I have a table listing the active jobs. However what I physically do is I add the month name and year to the batch as I am physically raising the jobs. I am wanting to make a hybrid between the companies job card system and automation.
Conceptually I want to input a Job Number from the job card system...lets say 124656. Then in SQL walk the active jobs table, grab the job description and the current job number being 124656 + n and insert both into another table. To test the concept I have made a temp table in SQL and attempted to create a stored procedure along the line I think might work.
CREATE PROCEDURE mysp_ProofOfJob -- Add the parameters for the stored procedure here @StartJob nchar(50) = 0, @CMonth nchar(100) = 0 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here INSERT INTO ActiveTemp (Job, WorkDescription) SELECT (@StartJob , JobDescription & " " & @CMonth) FROM ActiveJob WHERE active = 0 END GO
This attempt is resulting in compile errors. I thought or hoped it would insert the variable start job being 124656 into ActiveTemp field Job and the JobDescription "Clean Office" plus the variable Cmonth into the WorkDescription field in ActiveJob as "Clean office January".
One of the challenges being new to this is not even knowing what I should search for in Google to find the help that may be freely available...so in this instance I am stuck. I would really appreciate some guidance or tell me I am using the wrong tool, or wrong concepts or here is some training you need to do or ?????
Okay got a bit further...
INSERT INTO ActiveTemp (Job, WorkDescription) VALUES (@StartJob , (SELECT JobDescription FROM ActiveJob WHERE Active = 0 AND ActiveJobID=6) + ' ' + @CMonth)
This is something like what I am wanting and indeed pushes a record into my temp table. Now I need to increment the @StartJob and increment the ActiveJobID till end of the records that are active...