Update the last Operation in a Job

31 Aug 2006 9:53 PM

Deb Struble

I have a sql jobroute table that consists a variety of information which
include the fields job number, operation number, and last code. I need to
be able to locate the last operation for each job and update the last code
field with a "Y". Here is what I have:
Job        Operation        Last Code
1            10
1            20
1            30
2            10
2            30

This is what the outcome would look like:
Job        Operation        Last Code
1            10
1            20
1            30                    Y
2            10
2            30                    Y

I have tried writing a variety of scripts to run in query analyzer but I
can't seem to get them to work right. Does anyone have any suggestions?

Thanks in advance!
Deb

31 Aug 2006 10:25 PM

Ryan

Is this what you are looking for?

Select j.name, run_status
from msdb..sysjobhistory h (nolock)
    inner join msdb..sysjobs j (nolock)
        on j.job_id = h.job_id
    inner join (Select job_id, max(instance_id) MaxInst
                    from msdb..sysjobhistory h (nolock)
                    --Step_id 0 is always the job outcome
                    Where step_id = 0
                    Group by job_id) m
        on m.job_id = h.job_id
            and m.maxInst = h.Instance_ID

The Job_Status definitions can be found in BOL, by looking up the
sysjobhistory table

Ryan
www.ddrs.net

Show quote

"Deb Struble" wrote:

> I have a sql jobroute table that consists a variety of information which
> include the fields job number, operation number, and last code. I need to
> be able to locate the last operation for each job and update the last code
> field with a "Y". Here is what I have:
> Job        Operation        Last Code
> 1            10
> 1            20
> 1            30
> 2            10
> 2            30
>
> This is what the outcome would look like:
> Job        Operation        Last Code
> 1            10
> 1            20
> 1            30                    Y
> 2            10
> 2            30                    Y
>
> I have tried writing a variety of scripts to run in query analyzer but I
> can't seem to get them to work right. Does anyone have any suggestions?
>
> Thanks in advance!
> Deb
>
>
>
>
>
>

31 Aug 2006 10:26 PM

Erland Sommarskog

Deb Struble (dstru***@paladinbrands.com) writes:
Show quote

> I have a sql jobroute table that consists a variety of information which
> include the fields job number, operation number, and last code. I need to
> be able to locate the last operation for each job and update the last code
> field with a "Y". Here is what I have:
> Job        Operation        Last Code
> 1            10
> 1            20
> 1            30
> 2            10
> 2            30
>
> This is what the outcome would look like:
> Job        Operation        Last Code
> 1            10
> 1            20
> 1            30                    Y
> 2            10
> 2            30                    Y
>
> I have tried writing a variety of scripts to run in query analyzer but I
> can't seem to get them to work right. Does anyone have any suggestions?

UPDATE tbl
SET    [Last Code] = 'Y'
FROM   tbl a
JOIN   (SELECT Job, Operation = MAX(Operation)
        FROM   tbl
        GROUP BY Job) AS b ON a.Job = b.Job
                           AND a.Operation = b.Operation

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

31 Aug 2006 11:27 PM

Arnie Rowland

This example should point you in a direction that works for you.

DECLARE @MyTable table
   (    RowID      int       IDENTITY
      , JobID      int
      , Operation int
      , LastCode   char(1)
   )

INSERT INTO @MyTable VALUES ( 1, 10, NULL )
INSERT INTO @MyTable VALUES ( 1, 20, NULL )
INSERT INTO @MyTable VALUES ( 1, 30, NULL )
INSERT INTO @MyTable VALUES ( 2, 10, NULL )
INSERT INTO @MyTable VALUES ( 2, 30, NULL )

UPDATE @MyTable
   SET LastCode = 'Y'
   FROM @MyTable m
      JOIN ( SELECT
                 JobID
               , Operation = max( Operation )
             FROM @MyTable
             GROUP BY JobID ) m2
         ON (   m.JobID = m2.JobID
            AND m.Operation = m2.Operation
            )

SELECT *
FROM   @MyTable

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Deb Struble" <dstru***@paladinbrands.com> wrote in message news:uUylwfUzGHA.4452@TK2MSFTNGP05.phx.gbl...
>I have a sql jobroute table that consists a variety of information which
> include the fields job number, operation number, and last code. I need to
> be able to locate the last operation for each job and update the last code
> field with a "Y". Here is what I have:
> Job        Operation        Last Code
> 1            10
> 1            20
> 1            30
> 2            10
> 2            30
>
> This is what the outcome would look like:
> Job        Operation        Last Code
> 1            10
> 1            20
> 1            30                    Y
> 2            10
> 2            30                    Y
>
> I have tried writing a variety of scripts to run in query analyzer but I
> can't seem to get them to work right. Does anyone have any suggestions?
>
> Thanks in advance!
> Deb
>
>
>
>
>

1 Sep 2006 11:02 PM

Hugo Kornelis

On Thu, 31 Aug 2006 16:53:45 -0500, Deb Struble wrote:

Show quote

>I have a sql jobroute table that consists a variety of information which
>include the fields job number, operation number, and last code. I need to
>be able to locate the last operation for each job and update the last code
>field with a "Y". Here is what I have:
>Job        Operation        Last Code
>1            10
>1            20
>1            30
>2            10
>2            30
>
>This is what the outcome would look like:
>Job        Operation        Last Code
>1            10
>1            20
>1            30                    Y
>2            10
>2            30                    Y
>
>I have tried writing a variety of scripts to run in query analyzer but I
>can't seem to get them to work right. Does anyone have any suggestions?

Hi Deb,

Erland and Arnie gave you solutions that use a proprietary Transact-SQL
extension of the UPDATE syntax. This version is ANSI-compliant:

UPDATE tbl
SET    LastCode = 'Y'
WHERE Operation = (SELECT MAX(a.Operation)
                    FROM   tbl AS a
                    WHERE a.Job = tbl.Job);

--
Hugo Kornelis, SQL Server MVP