SQL Server Forums
Profile | Register | 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?
 New Topic  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
52249 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
52249 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
52249 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
52249 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
52249 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000