Home » SQL & PL/SQL » SQL & PL/SQL » Excluding data (Oracle 10g (I think))
Excluding data [message #362675] Wed, 03 December 2008 13:04 Go to next message
darlene.mack
Messages: 2
Registered: December 2008
Location: San Marcos, TX
Junior Member
I am trying to create a report that ages service request data. Here's my basic SQL:

select
service_request_number
, new_sr_urgency
, max(sr_changes_creation_date)
, new_sr_status

from Noetix_sys.CSG0_Service_Request_Audits
where 1=1
and new_group_name = 'EAGLE TIER 3'
and new_sr_status <> 'Closed'
and new_sr_urgency is not null
group by service_request_number
, new_sr_urgency
, new_sr_status

Seems straight forward enough but there can be several entries for each service request, with changes in status. Once the last status changes to closed that service request should be excluded. What happens with the above SQL is it will include all the various previous statuses. The only thing that it excludes is the closed status.

I have tried using CASE and DECODE in various ways but cannot get the programming exacly right. I know it's possible, but could use a little help. Thanks.



Darlene


Re: Excluding data [message #362683 is a reply to message #362675] Wed, 03 December 2008 14:35 Go to previous messageGo to next message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
Hi,

I think I understand what you want.

Maybe something like:

select
service_request_number
, new_sr_urgency
, max(sr_changes_creation_date)
, new_sr_status
from Noetix_sys.CSG0_Service_Request_Audits
where new_group_name = 'EAGLE TIER 3'
and new_sr_urgency is not null
and service_request_number NOT IN
(select x.service_request_number
from Noetix_sys.CSG0_Service_Request_Audits x
where x.new_sr_status = 'Closed')
group by service_request_number
, new_sr_urgency
, new_sr_status;

Thanks,
Jim
Re: Excluding data [message #362706 is a reply to message #362675] Wed, 03 December 2008 16:17 Go to previous messageGo to next message
darlene.mack
Messages: 2
Registered: December 2008
Location: San Marcos, TX
Junior Member
Thanks. That was a great help.



d
Re: Excluding data [message #362719 is a reply to message #362683] Thu, 04 December 2008 00:08 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Both,

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals) with your question (real one not hypothetical one).

Regards
Michel
Previous Topic: top SQL
Next Topic: Logic to apportion given value among 2 dates
Goto Forum:
  


Current Time: Sat Dec 10 01:01:24 CST 2016

Total time taken to generate the page: 0.20072 seconds