Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Searching for a particular entry in SQL?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Maverick_
Posting Yak Master

107 Posts

Posted - 06/11/2013 :  07:16:03  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 06/11/2013 :  07:25:02  Show Profile  Reply with Quote
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 - 06/11/2013 :  07:57:50  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 06/11/2013 :  08:09:02  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 06/11/2013 :  08:12:40  Show Profile  Reply with Quote
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 - 06/11/2013 :  08:19:38  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 06/11/2013 :  08:20:34  Show Profile  Reply with Quote
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 - 06/11/2013 :  10:30:12  Show Profile  Reply with Quote
Thanks Visakh!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 06/11/2013 :  11:45:52  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000