Query Optimization Help
I have two tables, I won't list all the fields, just the one's I'm concerned with.
Job_Order_Master - 58 Total Fields, 180,441 records
----------------
JobOrderNumber
OpenDate
DefaultProfitCenter
Primary Key(JobOrderNumber)
Job_Order_Detail - 36 Total Field, 5,900,574 records
----------------
JobOrderNumber
PhaseNumber
PartNumber
RecordType
IssueDate
LineItem
Primary Key (JobOrderNumber, PhaseNumber, PartNumber, RecordType, IssueDate, LineItem)
I have no control over table structure, so I have to work with what I got. This is complicated to me because of the RecordType field. Every record will have a 1 or a 2 for this field, with 1 meaning the record is a part that has been estimated for the job, while 2 means the part has been issued to the job. My goal is to generate a list of parts that have been estimated but not issued. In other words if I have 3 detail records that look like below...
OrderNumber - PhaseNumber - PartNumber - RecordType
100000 - 100 - 56Y - 1
100000 - 100 - 78G - 1
100000 - 100 - 78G - 2
... I just need to select the 100000 - 100 - 56Y - 1 record, not the other two. To further complicate it, I only want records that have an IssueDate for an estimate record that is later than the Open Date for the job. My biggest issue with testing my ideas is that it takes over a quarter of an hour to test these queries. I don't know of any syntax which allows you to make comparisons against other rows within the query, so I've assumed that I will need to join the Detail table to itself.
I've started by using the below query.
SELECT JD1.JobOrderNumber, JD1.PhaseNumber, JD1.PartNumber,
JD1.IssueDate, JD1.RecordType AS RT1, JD2.RecordType AS RT2,
COUNT(JD1.JobOrderNumber) AS Count
FROM Job_Order_Detail AS JD1 JOIN Job_Order_Detail AS JD2
ON JD1.JobOrderNumber = JD2.JobOrderNumber AND
JD1.PhaseNumber = JD2.PhaseNumber AND
JD1.PartNumber = JD2.PartNumber
GROUP BY JD1.JobOrderNumber, JD1.PhaseNumber, JD1.PartNumber
My results from that would be the job number, phase number, part number, and issue date, with either a 0 0, or 1 1 for the RT1 and RT2 fields. All 0 0 will be a count of 1, and all 1 1 records will have a count of 1 or 4. If there's only an estimate record it will have 1 record to group (1-1), while the parts with both issue and estimate will have 4 records to group (1-1, 1-2, 2-1, 2-2).
I thought I could wrap a second select query around the previous.
SELECT TempJD.JobOrderNumber, TempJD.PhaseNumber, TempJD.PartNumber, TempJD.IssueDate
FROM (PREVIOUS QUERY) AS TempJD JOIN Job_Order_Master AS JOM
ON TempJD.JobOrderNumber = JOM.JobOrderNumber
WHERE JOM.DefaultProfitCenter = 200 AND
TempJD.IssueDate > JOM.OpenDate AND
(TempJD.Count = 1 AND TempJD.RT1 = 1)
ORDER BY TempJD.JobOrderNumber, TempJD.PhaseNumber, TempJD.PartNumber
I'm wondering if anyone can give me a bit of guidance? I really would like to avoid a query that takes a ludicrously long period of time to execute.