Please help me write a query

Hi Guys,

Pl. help me write a query.

I have a foll. qry.

select fcp.concurrent_program_name||' - '||
fcpt.user_concurrent_program_name "Program"
,count(*) "SLA miss count"
from
sla_request_data gsrd,
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt
where
gsrd.request_id = fcr.request_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcpt.concurrent_program_id = fcp.concurrent_program_id
and fcpt.language = 'US'
and fcr.program_application_id = fcp.application_id
and fcpt.application_id = fcp.application_id
and gsrd.start_date between '01-SEP-2005' and '30-SEP-2005'
group by fcp.concurrent_program_name
,fcpt.user_concurrent_program_name

This gives output...

Program SLA miss count
FAPPT - Process Pending Transactions 1
POPDI - GEMS PDI PO Report Optio 1
RVCTP - Receiving Transaction Processor 9
APPBCF - Confirm Payment Batch 10

I want to have foll. output (for example)

Program SLA miss total %miss
FAPPT - Process Pending Transactions 1 100 1
POPDI - GEMS PDI PO Report Optio 1 90 .9
RVCTP - Receiving Transaction Processor 9 1000 .01
APPBCF - Confirm Payment Batch 10 1000 .01

total colum is a count of total requests in fnd_concurrent_requests table for particular program in the month of Sep-05. I want this column to show %miss for a parti. program.

How can I do this?

Pl. help.

thanks in adv.

It's difficult to answer this without understanding your table structures. You seem to want to compare "misses" with total records. Your first query must be returning the "misses". How would you return total records on your database ? Once you've got that, you can join the two queries together and get the information you want.

Hi Scorby,

Thanks for your response.

Here is the structure.

SLA_REQUEST_DATA
--------------------
start_date Date
request_id number

FND_CONCURRENT_REQUESTS
-----------------------------
request_id Number
concurrent_program_id Number

FND_CONCURRENT_PROGRAMS
------------------------------
concurrent_program_id Number
Concurrent_program_name VC

I am joining SLA_REQUEST_DATA with FND_CONCURRENT_REQUESTS table to bring the concurrent_program_id and then joining with FND_CONCURRENT_PROGRAMS to bring program name.

Total count/records for a particular concurrent_program_id is in fnd_concurrent_requests table. My problem is how to get this count in the same query where I get misses.

Thanks in Adv.

This doesn't make any sense. Your original query retrieves "misses" by selecting from FIND_CONCURRENT_REQUESTS. But you say also want to get total records from the same table. If they are both coming from the same table, then ther must be something like a type which distinguishes between a missed record and a successful record. Or is it the fact that there is a match in the sla_request_data which determines that something "missed" ?

Hi Scorby,

Thanks again!

There is no such column in FND_CONCURRENT_REQUESTS table which tells that the request was a success. It has all the rows for a particular concurrent_program_id out which some rows are getting populated in SLA_REQUEST_DATA which had SLA miss. SLA_REQUEST_DATA has only those request_ids where had SLA miss.

I think what you need is something like this:

Code:

---

select fcp.concurrent_program_name||' - '||
fcpt.user_concurrent_program_name "Program"
,count(gsrd.request_id) "SLA miss count",
count(*) total,
((count(gsrd.request_id)/count(*)) * 100 prop
from
(select * from sla_request_data
where start_date between '01-SEP-2005' and '30-SEP-2005') gsrd,
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt
where
gsrd.request_id (+) = fcr.request_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcpt.concurrent_program_id = fcp.concurrent_program_id
and fcpt.language = 'US'
and fcr.program_application_id = fcp.application_id
and fcpt.application_id = fcp.application_id
group by fcp.concurrent_program_name
,fcpt.user_concurrent_program_name

---

The drawback is that this would return rows in dates other than September as well. Is there a start_date on the find_concurrent_requests table as well ?