Hello,

I am having a little trouble with some sub queries, being new to this what I am trying to do is to select the first status of a job where its status code = ‘q’, its log number and date the job was logged in the first status.

Then I want to select another status =’p’ its log number and logged date so I can compare the date difference in my report to see if the status p was reached from in 28 days, also

This info all comes from the same table and job enquiry number, wat happens is that each time the status is updated and changed.

I have been going down the lines of this code but as its borrowed from another report which did similar things but told me the next status (+1) rather than when the next status hit P.

Here’s my code I am working with so far….am I going down the right lines?


SELECT DISTINCT

job_status_log.allocated_officer,
job_status_log.status_code,
job_status_log.job_number,
job_status_log.job_log_number,
job_status_log.log_effective_date,


(SELECT NVL(jsl.job_log_number +1,jsl.job_log_number)
FROM job_status_log jsl
WHERE
jsl.job_log_number = job_status_log.job_log_number and
jsl.job_number = job_status_log.job_number) as Next_Log_No,

(SELECT MIN(jsl.log_effective_date)
FROM job_status_log jsl
WHERE
jsl.job_log_number = job_status_log.job_log_number +1 and
jsl.job_number = job_status_log.job_number) as Next_LogEff_Date,

(SELECT MIN(jsl.status_code)
FROM job_status_log jsl
WHERE
jsl.job_log_number = job_status_log.job_log_number +1 and
jsl.job_number = job_status_log.job_number) as Next_Enq_StatCode

FROM
job_status_log,
job

WHERE

job_status_log.status_code IN('Q') AND
job.job_number = job_status_log.job_number

Any ideas would be great!

Lucy