Posted by mcolson on 01/07/08 23:19

I am trying to select the max for each Schedule_Number when Process
Description = 'Exit Cold Rinse'. In the following table, 2:00 and
4:00 should be returned for 12345_001 and 12345_002 respectively. I
have tried to join the two queries and would like to use the current
Schedule_Number as one of the criteria when determining the max.
Below is some code that I've used thus far? Does anyone have
suggestions?

*Schedule_Number * Process_Description * TMDT
*12345_001 * Exit Cold Rinse * 1/07/08 01:00:00 PM
*12345_001 * Enter Cold Rinse * 1/07/08 01:30:00 PM
*12345_001 * Exit Cold Rinse * 1/07/08 02:00:00 PM
*12345_002 * Enter Cold Rinse * 1/07/08 02:30:00 PM
*12345_002 * Exit Cold Rinse * 1/07/08 03:00:00 PM
*12345_002 * Enter Cold Rinse * 1/07/08 03:30:00 PM
*12345_002 * Exit Cold Rinse * 1/07/08 04:00:00 PM

Select *
From
(Select distinct Schedule_Number
From dbo.Process_Data
WHERE left(Schedule_Number,5) = '12345') as Query1
left join
(Select *
From dbo.Process_Data
Where TMDT =
(SELECT Max(TMDT)
FROM dbo.Process_Data
WHERE Process_Description = 'Exit Cold Rinse' and
Query1.Schedule_Number = Query2.Schedule_Number)) as Query2
on Query1.Schedule_Number=Query2.Schedule_Number

• Next in forum: Re: FOR XML EXPLICIT can't get element 4 going
• Prev in forum: Re: FOR XML EXPLICIT can't get element 4 going
• Thread view: Select Max values from queries for multiple Schedule_Number

[Reply to this message]