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...
Changed to
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...
Without knowing the data layout, you have a couple of things to be careful of. Starting with the parameters being passed in. Using NChar is sets the string to the full length and is not good. You can change these to nvarchar and it will only store values at their length (i.e. if Value = 'Job1' then it wail store 4 bytes (+2 for double-byte so actually 6 bytes) not 50 like you have). You should review the datatypes and use the proper ones.
as for your insert, you were close. The &, double quotes and the parenthesis are wrong. I changed the & to + but this will return null if Jobdescription or @CMonth is null, but you can look into Cast if you need to
INSERT INTO ActiveTemp (Job, WorkDescription)
SELECT @StartJob , JobDescription + ' ' + @CMonth
FROM ActiveJob WHERE active = 0
Rather fuzzy on the details, but something like this should get you started:
/*These SETtings are VERY important. Be sure to include them!*/
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE mysp_ProofOfJob
@Job int,
@CMonth nvarchar(50) = N''
AS
SET NOCOUNT ON;
IF @CMonth = N'' OR @CMonth IS NULL
BEGIN
SET @CMonth = DATENAME(MONTH, GETDATE())
END /*IF*/
INSERT INTO ActiveTemp (Job, WorkDescription)
SELECT MaxJob + 1 AS Job, JobDescription + ' ' + @CMonth AS WorkDescription
FROM ActiveJob
CROSS JOIN (
SELECT MAX(Job) AS MaxJob
FROM ActiveJob
) AS cj1
WHERE Job = @Job
/*end of proc*/
GO
That looks great...I can see the approach taken. However I am a bit confussed by the note:
/These SETtings are VERY important. Be sure to include them!/
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
Why are these so important? I see they are in my SP as I use the auto tools to build it. But why?
Is there a good reference, not that I don't appreciate the help, I should invest in to help me on this journey? I am finding a lot out by simple trial and error and google. But I think I need a bit more of a guide somehow as I move forward. I am just very aware I can easily burn cash on books to find after reading 5 I at last find a good reference...and I don't have that much money
So first we tell the procedure the fields and target table to insert into being:
INSERT INTO ActiveTemp(Job, WorksDescription)
Then we tell it to find the Maximum Job value in ActiveJob?
This is a bit confusing. But lets accept that for a minute as just the way it is written and the language being what it is. However how can it get this field MaxJob from ActiveJob? There is no field called Job or similar in ActiveJob.
Active job simply lists the Job Description (and some other bits and pieces) but no job.
Then I am getting completely lost. As we are creating MaxJob from a field that doesn't exist in ActiveJob.
Perhaps its my bad language or explanation.
ActiveTemp is the target table and contains two fields being Job and Description.
ActiveJob contains the description less the month.
I am inserting all records in ActiveJobs into ActiveTemp incrementing the variable @Job (which is passed in as a nvarChar because of the way the company wants it). Sorry if this is already answered and I am simply not seeing it.
Can you provide the CREATE TABLE and INSERT INTO scripts with some sample data? Also, based on the sample data, what do you want/expect to be INSERTed into ActiveTemp?
People on the forum are trying to help you, but you give them very little to work on. They are all in the dark, trying to guess how your situation looks like.
Active Jobs Table
ActiveJobID as pk
JobDescription = "Office Cleaning " next "Factory Clean "
Rest is not helpful we aren't interested in this.
ActiveTemp Table
Job as pk
WorkDescription
Input variable for job input from paper form StartJob is CP12343
What I am wanting is in ActiveTemp
Job Work Description
CP 12343 Office Cleaning February 2020
CP 12344 Factory Cleaning February 2020
CP 12345 Some Other Cleaning February 2020
etc
So far what I have thanks to everyone above and a bit of mucking about on my own after:
INSERT INTO ActiveTemp (Job, WorkDescription)
SELECT MaxJob + 1 as Job, JobDescription +' '+ @CMonth AS WorkDescription
FROM ActiveJob
CROSS JOIN (SELECT MAX(Job) AS MaxJob FROM ActiveTemp)as cj1
WHERE MaxJob =@StartJob
Working through the previous posts this is very close to correct. It is giving
Job Work Description
CP 12343 Office Cleaning February 2020
CP 12344 Factory Cleaning February 2020
CP 12344 Some Other Cleaning February 2020
CP 12344 Etc
CP 12344 Etc
Very very close but not incrementing the numbers quite right...which is something to do with the last two lines that I am not following correctly.
I tried to come up with Sample Data .. Hope this helps
please click arrow to the left for DROP CREATE Sample Data
drop table #ActiveJobs
go
create table #ActiveJobs
(
ActiveJobID int ,
JobDescription varchar(100)
)
go
insert into #ActiveJobs select 1,'Office Cleaning'
insert into #ActiveJobs select 2,'Factory Clean'
go
select 'Sample Data Active Jobs',* from #ActiveJobs
go
drop table #ActiveTemp
go
create table #ActiveTemp
(
Job int ,
WorkDescription varchar(100)
)
go
insert into #ActiveTemp select 1, 'Hope this works'
insert into #ActiveTemp select 2, 'Fine No Probs'
insert into #ActiveTemp select 3, 'Sure Sweety'
go
select 'Sample Data Active Temp',* from #ActiveTemp
go
Still fuzzy, but, based on your last post, try this:
INSERT INTO ActiveTemp (Job, WorkDescription)
SELECT MaxJob + row_num AS Job, WorkDescription
FROM (SELECT MAX(Job) AS MaxJob FROM ActiveTemp) AS cj1
CROSS JOIN (
SELECT JobDescription + ' ' + @CMonth AS WorkDescription,
ROW_NUMBER() OVER(ORDER BY JobDescription) AS row_num
FROM ActiveJob
) AS AJ
ActiveTemp
CREATE TABLE [dbo].[ActiveTemp](
[TempID] [int] IDENTITY(1,1) NOT NULL,
[Job] nchar NOT NULL,
[WorkDescription] nvarchar NOT NULL,
CONSTRAINT [PK_ActiveTemp] PRIMARY KEY CLUSTERED
(
[TempID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Input is @StartJob and @CMonth
Physically @StartJob = 124567 and @CMonth =February 2020 (if this is January)
Output I am wanting to add to ActiveTemp (assume there are hundreds of thousands of other entries in here) is:
And then I want to send this back as an output from the SP as in just the new entries created I hope that makes sense. I don't know how else to explain this.
Ok, the table structures with the sample data are extremely helpful. I believe this really will do what you want to do, based on your last posts.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE mysp_ProofOfJob
@StartJob int,
@CMonth nvarchar(50) = N''
AS
SET NOCOUNT ON;
IF @CMonth = N'' OR @CMonth IS NULL
BEGIN
SET @CMonth = DATENAME(MONTH, GETDATE())
END /*IF*/
INSERT INTO ActiveTemp (Job, WorkDescription)
SELECT @StartJob + row_num AS Job, WorkDescription
FROM (
SELECT JobDescription + ' ' + @CMonth AS WorkDescription,
ROW_NUMBER() OVER(ORDER BY JobDescription) AS row_num
FROM ActiveJob
) AS AJ
SELECT *
FROM ActiveTemp
WHERE Job > @StartJob
ORDER BY Job
/*end of proc*/
GO
Thanks Scott. I just figured out the @StartJob and was just able to check the table output. This is doing exactly what I needed...except for the last little thing. I hope your help here will finish this...and then I can actually start to use this tool in my work even though it is only a tiny bit built.
