Author |
Topic |
Maverick_
Posting Yak Master
107 Posts |
Posted - 2013-06-11 : 07:16:03
|
Hi all,I have a SQL query to write but I am not sure how to write the query.I need a list of Jobs (table: job) where in its job status log history (table: job status log) I want it to return the position of the latest job status log number (field: jobstatuslog.job_status_log_number) where the job status code is 0150 (field: jobstatuslog.job_status_code).Is there a way to write an SQL that extracts this information? The output should be job number (from table: job) and another field which is jobstatuslog.job_status_log_number to show which log number is the last entry where job status code is 0150. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-11 : 07:25:02
|
its a straightforward group by statementSELECT j.job_number,MAX(l.job_status_log_number) AS latestlognoFROM Job jINNER JOIN jobstatuslog lOn l.job_number = j.job_numberWHERE l.job_status_code = '0150'GROUP BY j.job_number ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Maverick_
Posting Yak Master
107 Posts |
Posted - 2013-06-11 : 07:57:50
|
Hi Visakh,Thanks for your reply! :-)I managed to tweak my report based on yoursSELECT 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 = '0150') as Latest_Log_NoFROM jobNow the next thing I need to do is check after the max job_log_number where its found 0150, I need it to search subsequent log entries to check if it passed through a status of 0325.Is there a way to do this? :-) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-11 : 08:09:02
|
sorry didnt get that. you mean get occurance of log entry for 0325 ,if one exists, after last 0150 entry?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-11 : 08:12:40
|
this will give you jobnumbers which has occurance of 0325 status after last 0150 SELECT j.job_number,MAX(l.job_status_log_number) AS latestlognoFROM Job jINNER JOIN jobstatuslog lOn l.job_number = j.job_numberGROUP BY j.job_numberHAVING MAX(CASE WHEN l.job_status_code = '0150' THEN l.job_status_log_number END) < MAX(CASE WHEN l.job_status_code = '0325' THEN l.job_status_log_number END) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Maverick_
Posting Yak Master
107 Posts |
Posted - 2013-06-11 : 08:19:38
|
Hi Visakh,Yes you interpreted it right, that's what I meant. If 0325 exists after 0150 :-) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-11 : 08:20:34
|
cool.. Hope last suggestion gave you what you were looking for!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Maverick_
Posting Yak Master
107 Posts |
Posted - 2013-06-11 : 10:30:12
|
Thanks Visakh! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-11 : 11:45:52
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|