-
May 10th, 2013, 08:27 AM
#1
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.
-
May 10th, 2013, 08:36 AM
#2
Re: SQL Challenge - Can this even be done?
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]
-
May 10th, 2013, 09:23 AM
#3
Re: SQL Challenge - Can this even be done?
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.
I'll give it a try this afternoon when I plan on taking another stab at it. Thanks much szlamany!
-
May 10th, 2013, 09:26 AM
#4
Re: SQL Challenge - Can this even be done?
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.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|