user6731224 user6731224 - 3 months ago 7
SQL Question

Stored procedure to update column based on Min Date

I have been able to find similar issues to one I am having, but not that helps me to resolve!

I have a table that contains job data (

tablea
), schema as follows:

CREATE TABLE [dbo].[tablea](
[ID] [int] IDENTITY(1,1) NOT NULL,
[JobNo] [varchar](32) NOT NULL,
[JobDesc] [varchar](255) NULL,
[RequiredDate] [datetime] NULL


Certain jobs within this table provide components for other jobs in the same table, going forward I will call the jobs that provide components "Production Jobs" and the job receiving the components "sub jobs" - there is nothing within the table that links the rows.

There is another table (
tableb
)

CREATE TABLE [dbo].[tableb](
[ID] [int] IDENTITY(1,1) NOT NULL,
[JobNo] [varchar](32) NOT NULL CONSTRAINT [DF_JobProducts_JobNo] DEFAULT (''),
[SubJobNo] [varchar](32) NULL


This contains the "production job" job number (
tableb.JobNo
), and in the same row, the job number of the "sub job" (
tableb.SubJobNo
)

nb if a "production job" provides components for 7 x "sub jobs" there will be 7 x rows in tableb

I have an objective to execute a stored procedure that will update the required date column of the "production job" row of tablea with the EARLIEST date of the required date column of the associated "sub job". When executing the stored procedure, a unique identifier will be used as the variable - this will be the title of the "production job".

My attempt is as follows:

CREATE PROCEDURE [dbo].[UpdateDate]
@Title varchar(32),
AS
BEGIN
SET NOCOUNT ON;

UPDATE ta1
SET ta1.RequiredDate = ta2.RequiredDate
FROM tablea ta1
INNER JOIN tableb tb ON ta1.JobNo = tb.JobNo
INNER JOIN
(SELECT JobNo, MIN(RequiredTime)
GROUP BY JobNo
FROM tablea) ta2 ON tb.SubJobNo = ta2.JobNo
WHERE ta1.JobDesc = @Title


This has got me stumped so any help or pointers would be greatly appreciated!

Answer

If you are trying to say that tabalea relates to tableb then back to tablea to get to required time then just change the cte to something like this:

CREATE PROCEDURE [dbo].[UpdateDate] 
    @Title varchar(32),
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH cte AS (
       SELECT
          ta.JobNo
          ,MIN(ta2.RequiredTime) as NewRequiredDate
       FROM
          tablea ta
          INNER JOIN tableb tb
          ON ta.JobNo = tb.JobNo
          INNER JOIN tablea ta2
          ON tb.SubJobNo = ta2.JobNo
       WHERE
          ta.JobDesc = @Title
       GROUP BY
          ta.JobNo
    )

    UPDATE ta
    SET RequiredDate = c.NewRequiredDate
    FROM tablea ta
    INNER JOIN cte c
    ON ta.JobNo = c.JobNo
Comments