SQL Challenge - Can this even be done?

I got data in a Pervasive PSQL database that I need to pull for a Crystal Report. I've been pulling my hair out trying to figure out just the right line of SQL to pull my data the way I need it. At this point, there's a part of me that thinks it can be done in pure SQL and another part of me that says I need to write some backend data pre-processor to arrange this data in the way I need. I'm not even sure it can be done; but while I'm pretty good at SQL, I'm no master.

Here's a simplified view of the data in the table I'm pulling from "TABLE_1":

Job_Number (String)	Part (String)	Sequence (Integer)	Operation (String)	Start_Time (DateTime)
1	Part A	1	SETUP	5/10/2013 8:00
1	Part A	5	INSPECT	5/10/2013 8:10
1	Part A	8	MILL	5/10/2013 9:00
1	Part A	10	SETUP	5/10/2013 14:00
1	Part A	15	CNC	5/11/2013 9:00
1	Part A	50	SHIP	5/12/2013 16:30
2	Part B	1	SETUP	5/11/2013 10:00
2	Part B	2	INSPECT	5/11/2013 11:00
2	Part B	5	SHIP	5/12/2013 8:45
3	Part C	1	SETUP	5/10/2013 10:00
3	Part C	3	CNC	5/10/2013 10:30
3	Part C	5	SHIP	5/11/2013 12:30

The data I need returned needs to look like this:

Job Number	Part	Setup Start	Operation
1	Part A	5/10/2013 8:00	MILL
1	Part A	5/10/2013 14:00	CNC
3	Part C	5/10/2013 10:00	CNC

The criteria:
I only want record listings for each "MILL" or "CNC" operation in the Job.
I need the Start Time record of the SETUP operation immediately preceeding the MILL or CNC operation.
Some data records may contain a SETUP operation but no MILL or CNC operation.

Can this even be done? Thanks in advance for any solutions/advice.

This should get you started - might even be exactly what you need...

Code:

---

Select S1.Operation,S1.Start_Time,S2.*
    From Table1 S1
    Left Join Table1 S2 on S2.JobNumber=S1.JobNumber
          and S2.Start_Time=(Select Max(S3.Start_Time) From Table1 S3 Where S3.JobNumber=S2.JobNumber and S3.Start_Time<S1.Start_Time and S3.Operation in ('SETUP'))
    Where S1.Operation in ('MILL','CNC')

---

[editi] btw - I changed it from <S2.Start_Time to <S1.Start_Time in the sub-query [/edit]

Huh... never occurred to me to try a MAX() aggregate like that in the sub query, or a sub-query in the outer join. I don't think I tried anything where I'm calling the same table three different times. :D
I'll give it a try this afternoon when I plan on taking another stab at it. Thanks much szlamany!

If pervasive doesn't like all that sub-query and what not you can always get to the same result with a couple of views that accomplish making the same sets of data.