Grouping to get the Last entry made

[Gary Nelson]

In Access2000 (frontend) SQL Server (backend), I have a report that I
created a report which is based upon an order number. I was asked to add a
field to this report which would provide the status of a component that is
being manufactured. The field is "PlatesCompleted". The department that
makes the plates has several entries in the table which are all based upon
the order number. I only want to list the entry when the plate is complete
on the report. This would be the last entry that the department makes. My
dilemma is the code to place in the stored procedure in order to pull only
the last entry onto the report.

If you need it, I have listed the stored procedure below. The table and
fields I need the last entry is: Cover PrePress Detail Entry.End

Cover PrePress Detail Entry.StatusID

Alter PROCEDURE billm.spBMCoverSchedule AS

SELECT dbo.BookAll.JobNum,

dbo.[Cover Print Process].CvrPrntProcessID,

dbo.[Cover Print Process].CvrPrntProcess, dbo.BookAll.DateIn,

dbo.[Scheduling - AD].dtPrintCvr2,

dbo.[Scheduling - AD].dtPrintTxt2,

dbo.[Scheduling - AD].TxtPrfStatus,

dbo.[Scheduling - AD].CvrPrfStatus, dbo.BookAll.Author,

dbo.Publisher.PublisherCode,

dbo.[cover PrePress Detail Entry].[End],

dbo.[Cover PrePress Detail Entry].StatusID,

dbo.[Binding Style].BindStyleAbbr, dbo.BookAll.TotalWidth,

dbo.BookAll.AllCovers + dbo.BookAll.ExtraCvrs AS Quantity,

dbo.[Cover Colors].CvrColor,

dbo.[Scheduling - AD].dtBNBInvRec,

dbo.[Scheduling - AD].dtBound,

dbo.[Scheduling - AD].dtShipped,

dbo.[Scheduling - AD].dtBound2,

dbo.[Scheduling - AD].BNBDelivery,

dbo.[Proof Job Type].ProofCode,

dbo.[Scheduling-LoadedDates].PrintCoverDue, Hold, HotJob,



'JobDueDate' =

CASE

WHEN [Hold] = 1 THEN '9/9/99'

WHEN ([DaysOnHold] Is Not Null AND [CustomerDelay] = 1 And [Take Off Hold] =
1)

THEN (dbo.BoundBook.BoundBook + [DaysOnHold])

ELSE (dbo.BoundBook.BoundBook)

END,

'CoverStatus' =

CASE

WHEN [CvrPrfStatus] = '28' THEN 'RTP, Needs Plates'

ELSE 'RTP'

END,

'Text Status' =

CASE

WHEN [dtPrintTxt2] is not null THEN 'Text Printed'

When [dtPrintTxt2] is null and [TxtPrfStatus] = '12' Then 'RTP'

ELSE 'Not RTP'


END,

'Plate Status' =

CASE

WHEN ([StatusID] = 20) And [End] Is Not Null THEN 'Plate Made'

ELSE 'Plate Not Made'


END

FROM dbo.BookAll INNER JOIN

dbo.[Scheduling - AD] ON

dbo.BookAll.JobNum = dbo.[Scheduling - AD].JobNum INNER JOIN

dbo.[Job - ED] ON

dbo.BookAll.JobNum = dbo.[Job - ED].JobNum INNER JOIN

dbo.[Cover Prepress Detail Entry] ON

dbo.BookAll.JobNum = dbo.[Cover Prepress Detail Entry].JobNum

INNER JOIN

dbo.Publisher ON

dbo.BookAll.PublisherID = dbo.Publisher.PublisherId INNER JOIN

dbo.[Cover Print Process] ON

dbo.BookAll.CvrPrntProcessID = dbo.[Cover Print Process].CvrPrntProcessID
LEFT OUTER JOIN

dbo.[Proof Job Type] ON

dbo.[Scheduling - AD].CvrPrfStatus = dbo.[Proof Job Type].ProofCodeID INNER
JOIN

dbo.[Scheduling-LoadedDates] ON

dbo.BookAll.JobNum = dbo.[Scheduling-LoadedDates].JobNum LEFT

OUTER JOIN

dbo.BoundBook ON

dbo.BookAll.JobNum = dbo.BoundBook.JobNum LEFT OUTER JOIN

dbo.[Hot Jobs] ON

dbo.BookAll.JobNum = dbo.[Hot Jobs].JobNum LEFT OUTER JOIN

dbo.[Cover Colors] ON

dbo.BookAll.CvrColorID = dbo.[Cover Colors].CvrColorID LEFT OUTER

JOIN

dbo.[Binding Style] ON

dbo.BookAll.BindStyleID = dbo.[Binding Style].BindStyleID LEFT OUTER JOIN

dbo.tblHoldJobs ON

dbo.BookAll.JobNum = dbo.tblHoldJobs.JobNumber WHERE (dbo.[Scheduling -
AD].dtBNBInvRec IS NULL) AND

(dbo.[Scheduling - AD].dtBound IS NULL) AND

(dbo.[Scheduling - AD].dtShipped IS NULL) AND

(dbo.[Scheduling - AD].dtBound2 IS NULL) AND

(dbo.[Scheduling - AD].BNBDelivery IS NULL) AND

(dbo.BookAll.DateIn > CONVERT(DATETIME,

'2002-12-31 00:00:00', 102)) AND

(dbo.[Scheduling - AD].dtPrintCvr2 IS NULL) AND

(dbo.[Cover Print Process].CvrPrntProcessID = 1 OR

dbo.[Cover Print Process].CvrPrntProcessID = 2 OR

dbo.[Cover Print Process].CvrPrntProcessID = 3) AND

(dbo.[Scheduling - AD].CvrPrfStatus = 12 OR

dbo.[Scheduling - AD].CvrPrfStatus = 20 OR

dbo.[Scheduling - AD].CvrPrfStatus = 26 OR

dbo.[Scheduling - AD].CvrPrfStatus = 28)

AND dbo.BookAll.PODScanOnly is null