To achieve the desired output, you can use a combination of JOINs and subqueries in your SQL query. Here's the query that will provide you with the records you need:
INNER JOIN (
MAX(OperNum) AS MaxOperNum
Comp = 1
) b_sub ON a.Job = b_sub.Job AND a.Suffix = b_sub.Suffix
INNER JOIN SyteLine.dbo.B b ON a.Job = b.Job AND a.Suffix = b.Suffix AND b.OperNum > b_sub.MaxOperNum AND b.Comp = 0
INNER JOIN SyteLine.dbo.C c ON b.WC = c.WC
a.Release = 'R';
In the subquery
b_sub, we first filter Table B to get the maximum
OperNum for each
Comp = 1.
Next, we join Table A with the
b_sub subquery on
Suffix to get the relevant rows from Table A.
Then, we join Table B with Table A on
Suffix and add a condition to get the
OperNum that is greater than the
MaxOperNum from the
b_sub subquery and where
Comp = 0.
Finally, we join Table C to get the
Descr based on the
WC value from Table B.
This query will provide you with the desired output, showing the open jobs with open operations and the operation number that comes after the
Comp = 1 operation for each job and suffix combination.
--please don't forget to upvote and Accept as answer if the reply is helpful--