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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Searching for a particular entry in SQL?

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 statement


SELECT j.job_number,MAX(l.job_status_log_number) AS latestlogno
FROM Job j
INNER JOIN jobstatuslog l
On l.job_number = j.job_number
WHERE l.job_status_code = '0150'
GROUP BY j.job_number


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 yours

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 = '0150') as Latest_Log_No
FROM
job


Now 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? :-)
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 latestlogno
FROM Job j
INNER JOIN jobstatuslog l
On l.job_number = j.job_number
GROUP BY j.job_number
HAVING 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 :-)
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-06-11 : 10:30:12
Thanks Visakh!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 11:45:52
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -