I am trying to figure out the last recorded date for a particular processID for a specific JobNumber.
This query returns all JobNumbers from a table that meet the filter, and I am getting the recorded dates from a different table.
The problem is the table with the dates may have multiple recorded times by ProcessID and JobNumber.
See in code sample in the CASE statment, I try the MAX but it returns the maximum number for all JobNumber that have ProcessID = 0
How can I dynamically filter by Jobnumber if I don't know the jobnumbers until query exection?
convert(varchar(10),DueDate,101) as DueDate,
when 1 then (Select Convert(varchar(20),MAX([StopTime]),120) as [StopTime] from [JobStepsStartStopTimes] where [Processid] = 0)
when 0 then ''
as [PUT ON COMPUTER]
where  = 0 and convert(varchar(10),DueDate,101) = convert(varchar(10),getdate() + 1,101)
order by Ccustno asc