Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Maverick_
Posting Yak Master
107 Posts |
 Posted - 2013-12-02 : 05:34:47
|
Hi guys,I was wondering if anyone knew how I could achieve the following. If I wanted to search for Jobs as a particular status (e.g. 0130) and wanted to keep the jobs at this status until it has reached 0500, 0125, or 0900 in it's subsequent status log entry, how can I write the SQL for it to achieve it? I have the following SQL which searches for the Jobs at 0130, but don't know how to develop it further to search on the requirement above. ------ SQL -------SELECT job.job_number, (SELECT MAX(jsl.job_log_number) FROM job_status_log jsl WHERE job.job_number = jsl.job_number AND jsl.status_code = '0130') as Last_Early_Warning_Status_EntryFROM action_officer, central_site, contract, cost_code, job, job_status, job_status_log, priorityWHERE central_site.site_code = job.site_code AND cost_code.cost_code = job.cost_code AND contract.contract_code = job.contract_code AND priority.priority_code = job.priority_code AND job.job_number = job_status_log.job_number AND job.job_log_number = job_status_log.job_log_number AND action_officer.officer_code = job_status_log.allocated_officer AND job_status.status_code = job_status_log.status_code AND (SELECT MAX(jsl.job_log_number) FROM job_status_log jsl WHERE job.job_number = jsl.job_number AND jsl.status_code = '0130') is not null AND job.job_status_flag <> 'A'ORDER BY job.job_number desc -----------SQL--------------In the job_status_log table above, there is a job_log_number field which increments by 1 when there is a new status log entry.If you have any info please let me know.  |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-02 : 07:32:55
|
| can you show some sample data and explain? do you mean cases where currentjobstatus = 0130 and next log entry is one of the list (0500, 0125, or 0900) ?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|
|
|
|
Searching for entries in subsequent log entries?