Home » SQL & PL/SQL » SQL & PL/SQL » Query (12.0.2)
Query [message #680156] Sat, 25 April 2020 07:57 Go to next message
bha_96
Messages: 11
Registered: April 2020
Junior Member
HI Experts,

We have below query running on porudction env:

SELECT TO_CHAR(EVENT_MINUTE, 'MM-YYYY') AS EVENT_MINUTE,
PROCESS_NAME,
CONTRACT,
APPLICATION AS APPLICATION,
SUM(TOTAL_COUNT) AS TOTAL_COUNT,
SUM(TOTAL_COUNT)-SUM(COUNT_FAILED) AS SUCCESS_TRANS,
ROUND(AVG(AVG_DURATION),2) AS AVG_DURATION,
ROUND((SUM(TOTAL_COUNT)-SUM(COUNT_FAILED))/SUM(TOTAL_COUNT)*100) AS SUCCESS_PERCENTAGE
FROM STATS_TRACK_SUMMARY_BY_MIN
WHERE EVENT_MINUTE >= ADD_MONTHS(TRUNC (SYSDATE,'MM'), - 3)
AND CONTRACT <> 'NULL'
AND APPLICATION IN ('cap','CAP')
GROUP BY TO_CHAR(EVENT_MINUTE, 'MM-YYYY'),
CONTRACT,
APPLICATION,
PROCESS_NAME
ORDER BY TO_DATE(EVENT_MINUTE,'MM-YYYY')





Output is below :
=================
Event_minute Process_name Contract Application Total_count Success_trans Avg_duration Success Percentage
02-2020 workorderinquire getWorkOrderList cap 1952 1952 1.03 100
02-2020 ws_router forceGetDetails CAP 2 2 0.11 100
02-2020 workorderinquire getWorkOrderDetails cap 45 26 1.11 58
02-2020 workorderservices updateWorkOrder cap 4520 4476 1.38 99
02-2020 ws_router forceGetWorkList CAP 6 6 0.05 100
03-2020 workorderservices updateWorkOrder cap 350317 345780 1.39 99
04-2020 workorderservices updateWorkOrder cap 255221 252429 1.27 99
04-2020 ws_router forceGetWorkList CAP 1 1 0.06 100
04-2020 workorderinquire getWorkOrderList cap 6 6 1.55 100


How can we modify the query to get the output like for process_name ws_router we can group the contracts and CAP in single row:

and sum of corresponding values respectively.



Event_minute Process_name Contract Application Total_count Success_trans Avg_duration Success Percentage
02-2020 workorderinquire getWorkOrderList cap 1952 1952 1.03 100
02-2020 ws_router forceGetDetails,forceGetWorkList CAP 9 9 0.22 100
02-2020 workorderinquire getWorkOrderDetails cap 45 26 1.11 58
02-2020 workorderservices updateWorkOrder cap 4520 4476 1.38 99
03-2020 workorderservices updateWorkOrder cap 350317 345780 1.39 99
04-2020 workorderservices updateWorkOrder cap 255221 252429 1.27 99
04-2020 workorderinquire getWorkOrderList cap 6 6 1.55 100


Kindly provide suggestions to get expected result
list [message #680157 is a reply to message #680156] Sat, 25 April 2020 08:05 Go to previous messageGo to next message
bha_96
Messages: 11
Registered: April 2020
Junior Member
Hi experts,




Can you Please provide your view on this, how can we get the desired results;



Below is the query we are using:



SELECT TO_CHAR(EVENT_MINUTE, 'MM-YYYY') AS EVENT_MINUTE,

PROCESS_NAME,

CONTRACT,

APPLICATION AS APPLICATION,

SUM(TOTAL_COUNT) AS TOTAL_COUNT,

SUM(TOTAL_COUNT)-SUM(COUNT_FAILED) AS SUCCESS_TRANS,

ROUND(AVG(AVG_DURATION),2) AS AVG_DURATION,

ROUND((SUM(TOTAL_COUNT)-SUM(COUNT_FAILED))/SUM(TOTAL_COUNT)*100) AS SUCCESS_PERCENTAGE

FROM STATS_TRACK_SUMMARY_BY_MIN

WHERE EVENT_MINUTE >= ADD_MONTHS(TRUNC (SYSDATE,'MM'), - 3)

AND CONTRACT <> 'NULL'

AND APPLICATION IN ('cap','CAP')

GROUP BY TO_CHAR(EVENT_MINUTE, 'MM-YYYY'),

CONTRACT,

APPLICATION,

PROCESS_NAME

ORDER BY TO_DATE(EVENT_MINUTE,'MM-YYYY')







Output from above query is:

========================

02-2020 workorderinquire getWorkOrderList cap 1952 1952 1.03 100

02-2020 ws_router forceGetDetails CAP 2 2 0.11 100

02-2020 workorderinquire getWorkOrderDetails cap 45 26 1.11 58

02-2020 workorderservices updateWorkOrder cap 4520 4476 1.38 99

02-2020 ws_router forceGetWorkList CAP 6 6 0.05 100

03-2020 workorderservices updateWorkOrder cap 350317 345780 1.39 99

04-2020 workorderservices updateWorkOrder cap 237374 234755 1.27 99

04-2020 ws_router forceGetWorkList CAP 1 1 0.06 100

04-2020 workorderinquire getWorkOrderList cap 6 6 1.55 100





output expected from the query is:



02-2020 ws_router forceGetDetails,forceGetWorkList CAP 9, 9, 0.22 100

02-2020 workorderinquire getWorkOrderList cap 1952 1952 1.03 100

02-2020 workorderinquire getWorkOrderDetails cap 45 26 1.11 58

02-2020 workorderservices updateWorkOrder cap 4520 4476 1.38 99

03-2020 workorderservices updateWorkOrder cap 350317 345780 1.39 99

04-2020 workorderservices updateWorkOrder cap 237374 234755 1.27 99

04-2020 workorderinquire getWorkOrderList cap 6 6 1.55 100
Re: list [message #680158 is a reply to message #680157] Sat, 25 April 2020 08:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: Query [message #680159 is a reply to message #680156] Sat, 25 April 2020 09:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Query [message #680160 is a reply to message #680159] Sat, 25 April 2020 10:33 Go to previous messageGo to next message
bha_96
Messages: 11
Registered: April 2020
Junior Member
Hi Experts ,

Oracle version is 12.0.2



Re: Query [message #680161 is a reply to message #680160] Sat, 25 April 2020 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle version 12.0.2 does not exist.
Post what is requested as it is requested.

Re: Query [message #680162 is a reply to message #680161] Sat, 25 April 2020 12:43 Go to previous messageGo to next message
bha_96
Messages: 11
Registered: April 2020
Junior Member
HI Experts:


Oracle version is 12.1.0.2


Can you Please provide your view on this, how can we get the desired results;



Below is the query we are using:



SELECT TO_CHAR(EVENT_MINUTE, 'MM-YYYY') AS EVENT_MINUTE,

PROCESS_NAME,

CONTRACT,

APPLICATION AS APPLICATION,

SUM(TOTAL_COUNT) AS TOTAL_COUNT,

SUM(TOTAL_COUNT)-SUM(COUNT_FAILED) AS SUCCESS_TRANS,

ROUND(AVG(AVG_DURATION),2) AS AVG_DURATION,

ROUND((SUM(TOTAL_COUNT)-SUM(COUNT_FAILED))/SUM(TOTAL_COUNT)*100) AS SUCCESS_PERCENTAGE

FROM STATS_TRACK_SUMMARY_BY_MIN

WHERE EVENT_MINUTE >= ADD_MONTHS(TRUNC (SYSDATE,'MM'), - 3)

AND CONTRACT <> 'NULL'

AND APPLICATION IN ('cap','CAP')

GROUP BY TO_CHAR(EVENT_MINUTE, 'MM-YYYY'),

CONTRACT,

APPLICATION,

PROCESS_NAME

ORDER BY TO_DATE(EVENT_MINUTE,'MM-YYYY')







Output from above query is:

========================

02-2020 workorderinquire getWorkOrderList cap 1952 1952 1.03 100

02-2020 ws_router forceGetDetails CAP 2 2 0.11 100

02-2020 workorderinquire getWorkOrderDetails cap 45 26 1.11 58

02-2020 workorderservices updateWorkOrder cap 4520 4476 1.38 99

02-2020 ws_router forceGetWorkList CAP 6 6 0.05 100

03-2020 workorderservices updateWorkOrder cap 350317 345780 1.39 99

04-2020 workorderservices updateWorkOrder cap 237374 234755 1.27 99

04-2020 ws_router forceGetWorkList CAP 1 1 0.06 100

04-2020 workorderinquire getWorkOrderList cap 6 6 1.55 100





output expected from the query is:



02-2020 ws_router forceGetDetails,forceGetWorkList CAP 9, 9, 0.22 100

02-2020 workorderinquire getWorkOrderList cap 1952 1952 1.03 100

02-2020 workorderinquire getWorkOrderDetails cap 45 26 1.11 58

02-2020 workorderservices updateWorkOrder cap 4520 4476 1.38 99

03-2020 workorderservices updateWorkOrder cap 350317 345780 1.39 99

04-2020 workorderservices updateWorkOrder cap 237374 234755 1.27 99

04-2020 workorderinquire getWorkOrderList cap 6 6 1.55 100
Re: Query [message #680163 is a reply to message #680162] Sat, 25 April 2020 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you provide what is asked in the way it is asked.

Re: Query [message #680164 is a reply to message #680163] Sat, 25 April 2020 15:01 Go to previous messageGo to next message
bha_96
Messages: 11
Registered: April 2020
Junior Member
HI Experts:


Oracle version is 12.1.0.2


Can you Please provide your view on this, how can we get the desired results;



Below is the query we are using:



SELECT TO_CHAR(EVENT_MINUTE, 'MM-YYYY') AS EVENT_MINUTE,

PROCESS_NAME,

CONTRACT,

APPLICATION AS APPLICATION,

SUM(TOTAL_COUNT) AS TOTAL_COUNT,

SUM(TOTAL_COUNT)-SUM(COUNT_FAILED) AS SUCCESS_TRANS,

ROUND(AVG(AVG_DURATION),2) AS AVG_DURATION,

ROUND((SUM(TOTAL_COUNT)-SUM(COUNT_FAILED))/SUM(TOTAL_COUNT)*100) AS SUCCESS_PERCENTAGE

FROM STATS_TRACK_SUMMARY_BY_MIN

WHERE EVENT_MINUTE >= ADD_MONTHS(TRUNC (SYSDATE,'MM'), - 3)

AND CONTRACT <> 'NULL'

AND APPLICATION IN ('cap','CAP')

GROUP BY TO_CHAR(EVENT_MINUTE, 'MM-YYYY'),

CONTRACT,

APPLICATION,

PROCESS_NAME

ORDER BY TO_DATE(EVENT_MINUTE,'MM-YYYY')







Output from above query is:

========================

02-2020 workorderinquire getWorkOrderList cap 1952 1952 1.03 100

02-2020 ws_router forceGetDetails CAP 2 2 0.11 100

02-2020 workorderinquire getWorkOrderDetails cap 45 26 1.11 58

02-2020 workorderservices updateWorkOrder cap 4520 4476 1.38 99

02-2020 ws_router forceGetWorkList CAP 6 6 0.05 100

03-2020 workorderservices updateWorkOrder cap 350317 345780 1.39 99

04-2020 workorderservices updateWorkOrder cap 237374 234755 1.27 99

04-2020 ws_router forceGetWorkList CAP 1 1 0.06 100

04-2020 workorderinquire getWorkOrderList cap 6 6 1.55 100





output expected from the query is:



02-2020 ws_router forceGetDetails,forceGetWorkList CAP 9, 9, 0.22 100

02-2020 workorderinquire getWorkOrderList cap 1952 1952 1.03 100

02-2020 workorderinquire getWorkOrderDetails cap 45 26 1.11 58

02-2020 workorderservices updateWorkOrder cap 4520 4476 1.38 99

03-2020 workorderservices updateWorkOrder cap 350317 345780 1.39 99

04-2020 workorderservices updateWorkOrder cap 237374 234755 1.27 99

04-2020 workorderinquire getWorkOrderList cap 6 6 1.55 100
Re: Query [message #680165 is a reply to message #680164] Sun, 26 April 2020 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Sat, 25 April 2020 20:25

Can you provide what is asked in the way it is asked.
Re: Query [message #680168 is a reply to message #680165] Sun, 26 April 2020 01:44 Go to previous messageGo to next message
bha_96
Messages: 11
Registered: April 2020
Junior Member
create table table_CAP (process_name varchar2(20), contract varchar2(20), application varchar2(20));
insert into table_cap(process_name, contract, application) values ('workorderinquire', 'getWorkOrderList', 'cap');
insert into table_cap(process_name, contract, application) values ('ws_router', 'forceGetDetails', 'CAP');
insert into table_cap(process_name, contract, application) values ('workorderinquire', 'getWorkOrderDetails', 'cap');
insert into table_cap(process_name, contract, application) values ('workorderservices', 'updateWorkOrder', 'cap');
insert into table_cap(process_name, contract, application) values ('ws_router', 'forceGetWorkList', 'CAP');
insert into table_cap(process_name, contract, application) values ('workorderservices', 'updateWorkOrder', 'cap');
insert into table_cap(process_name, contract, application) values ('ws_router', 'forceGetWorkList', 'CAP');

insert into table_cap(process_name, contract, application) values ('workorderinquire', 'getWorkOrderList' ,'cap');



below is the output we need:


Process_name Contract Application
workorderinquire getWorkOrderList cap
ws_router forceGetDetails,forceGetWorkList CAP
workorderinquire getWorkOrderDetails cap
workorderservices updateWorkOrder cap
workorderservices updateWorkOrder cap
workorderinquire getWorkOrderList cap
Re: Query [message #680169 is a reply to message #680168] Sun, 26 April 2020 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Close but:*
1/ Not formatted.
2/ What is the difference between the input (the INSERT statements) and the output (maybe there is one but as it is not formatted it is hard to see)?
3/ Give the the rules to get the result, the specification of what you want.

Re: Query [message #680170 is a reply to message #680169] Sun, 26 April 2020 03:19 Go to previous messageGo to next message
bha_96
Messages: 11
Registered: April 2020
Junior Member
Below is the output we need:


Process_name Contract Application
workorderinquire getWorkOrderList cap
ws_router forceGetDetails,forceGetWorkList CAP
workorderinquire getWorkOrderDetails cap
workorderservices updateWorkOrder cap
workorderservices updateWorkOrder cap
workorderinquire getWorkOrderList cap
Re: Query [message #680171 is a reply to message #680170] Sun, 26 April 2020 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Below is the requested output:
SQL> select 'Process_name Contract Application
  2  workorderinquire getWorkOrderList cap
  3  ws_router forceGetDetails,forceGetWorkList CAP
  4  workorderinquire getWorkOrderDetails cap
  5  workorderservices updateWorkOrder cap
  6  workorderservices updateWorkOrder cap
  7  workorderinquire getWorkOrderList cap' result from dual;
RESULT
-----------------------------------------------------------------------
Process_name Contract Application
workorderinquire getWorkOrderList cap
ws_router forceGetDetails,forceGetWorkList CAP
workorderinquire getWorkOrderDetails cap
workorderservices updateWorkOrder cap
workorderservices updateWorkOrder cap
workorderinquire getWorkOrderList cap
Re: Query [message #680172 is a reply to message #680171] Sun, 26 April 2020 04:17 Go to previous messageGo to next message
bha_96
Messages: 11
Registered: April 2020
Junior Member
Hi experts,

Can you help to achieve from below query same output

SELECT TO_CHAR(EVENT_MINUTE, 'MM-YYYY') AS EVENT_MINUTE,

PROCESS_NAME,

CONTRACT,

APPLICATION AS APPLICATION,

SUM(TOTAL_COUNT) AS TOTAL_COUNT,

SUM(TOTAL_COUNT)-SUM(COUNT_FAILED) AS SUCCESS_TRANS,

ROUND(AVG(AVG_DURATION),2) AS AVG_DURATION,

ROUND((SUM(TOTAL_COUNT)-SUM(COUNT_FAILED))/SUM(TOTAL_COUNT)*100) AS SUCCESS_PERCENTAGE

FROM STATS_TRACK_SUMMARY_BY_MIN

WHERE EVENT_MINUTE >= ADD_MONTHS(TRUNC (SYSDATE,'MM'), - 3)

AND CONTRACT <> 'NULL'

AND APPLICATION IN ('cap','CAP')

GROUP BY TO_CHAR(EVENT_MINUTE, 'MM-YYYY'),

CONTRACT,

APPLICATION,

PROCESS_NAME

ORDER BY TO_DATE(EVENT_MINUTE,'MM-YYYY')




02-2020 ws_router forceGetDetails,forceGetWorkList CAP 9, 9, 0.22 100

02-2020 workorderinquire getWorkOrderList cap 1952 1952 1.03 100

02-2020 workorderinquire getWorkOrderDetails cap 45 26 1.11 58

02-2020 workorderservices updateWorkOrder cap 4520 4476 1.38 99

03-2020 workorderservices updateWorkOrder cap 350317 345780 1.39 99

04-2020 workorderservices updateWorkOrder cap 237374 234755 1.27 99

04-2020 workorderinquire getWorkOrderList cap 6 6 1.55 100

[Updated on: Sun, 26 April 2020 04:22]

Report message to a moderator

Re: Query [message #680173 is a reply to message #680172] Sun, 26 April 2020 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 26 April 2020 07:45

Michel Cadot wrote on Sat, 25 April 2020 20:25

Can you provide what is asked in the way it is asked.
Re: Query [message #680177 is a reply to message #680172] Sun, 26 April 2020 08:38 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
If you persist in ignoring Forum Guidelines, you are unlikely to get any assistance. Please format your code (there is a code formatting tool here, http://www.dpriver.com/pp/sqlformat.htm ) and enclose the code you post in [code] tags, as described here, http://www.orafaq.com/forum/m/673006/#msg_673006
Re: Query [message #680178 is a reply to message #680168] Sun, 26 April 2020 09:29 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
bha_96 wrote on Sun, 26 April 2020 02:44
create table table_CAP (process_name varchar2(20), contract varchar2(20), application varchar2(20));
insert into table_cap(process_name, contract, application) values ('workorderinquire', 'getWorkOrderList', 'cap');
insert into table_cap(process_name, contract, application) values ('ws_router', 'forceGetDetails', 'CAP');
insert into table_cap(process_name, contract, application) values ('workorderinquire', 'getWorkOrderDetails', 'cap');
insert into table_cap(process_name, contract, application) values ('workorderservices', 'updateWorkOrder', 'cap');
insert into table_cap(process_name, contract, application) values ('ws_router', 'forceGetWorkList', 'CAP');
insert into table_cap(process_name, contract, application) values ('workorderservices', 'updateWorkOrder', 'cap');
insert into table_cap(process_name, contract, application) values ('ws_router', 'forceGetWorkList', 'CAP');

insert into table_cap(process_name, contract, application) values ('workorderinquire', 'getWorkOrderList' ,'cap');



below is the output we need:


Process_name Contract Application
workorderinquire getWorkOrderList cap
ws_router forceGetDetails,forceGetWorkList CAP
workorderinquire getWorkOrderDetails cap
workorderservices updateWorkOrder cap
workorderservices updateWorkOrder cap
workorderinquire getWorkOrderList cap
SQL> with t as (
  2             select  table_cap.*,
  3                     case
  4                       when process_name = 'ws_router' and application = 'CAP'
  5                         then 1 - row_number() over(partition by process_name,contract order by 1)
  6                       else rownum
  7                     end flag
  8               from  table_cap
  9            )
 10  select  process_name,
 11          listagg(contract,',') within group(order by contract) contract,
 12          application
 13    from  t
 14    where flag >= 0
 15    group by flag,
 16             process_name,
 17             application
 18  /

PROCESS_NAME         CONTRACT                            APPLICATION
-------------------- ----------------------------------- --------------------
ws_router            forceGetDetails,forceGetWorkList    CAP
workorderinquire     getWorkOrderList                    cap
workorderinquire     getWorkOrderDetails                 cap
workorderinquire     getWorkOrderList                    cap
workorderservices    updateWorkOrder                     cap
workorderservices    updateWorkOrder                     cap

6 rows selected.

SQL>
SY.
Re: Query [message #680179 is a reply to message #680178] Sun, 26 April 2020 23:09 Go to previous messageGo to next message
bha_96
Messages: 11
Registered: April 2020
Junior Member
HI Experts,

I tried to run from the logic in main query:


with t_process as (
select distinct process_name,application,contract from STATS_TRACK_SUMMARY_BY_MIN
--where process_name = 'ws_router'
),
t_contract as
(select process_name, application, listagg(contract,',') within group ( order by null) contract_list from t_process
where process_name = 'ws_router'
group by process_name, application
union all
select process_name, application, contract contract_list from STATS_TRACK_SUMMARY_BY_MIN where process_name != 'ws_router')
select a.process_name,a.application, contract_list,TO_CHAR(to_date(EVENT_MINUTE,'DD-Mon-YY'), 'MM-YYYY') AS EVENT_MINUTE
, SUM(total_count) AS total_count,
SUM(total_count)-SUM(count_failed) AS SUCCESS_TRANS, ROUND(AVG(AVG_DURATION),2) AS AVG_DURATION,
ROUND((SUM(total_count)-SUM(count_failed))/SUM(total_count)*100) AS SUCCESS_PERCENTAGE
from
t_process a
, t_contract b
, STATS_TRACK_SUMMARY_BY_MIN c
where a.process_name = b.process_name
and c.process_name = a.process_name
and EVENT_MINUTE >= add_months(TRUNC (SYSDATE,'mm'), - 3) AND CONTRACT_list<> 'NULL'
AND a.APPLICATION IN ('cap','CAP')
group by a.process_name, a.application, contract_list,TO_CHAR(to_date(EVENT_MINUTE,'DD-Mon-YY'), 'MM-YYYY')



Can you please provide where changes is required.

Re: Query [message #680180 is a reply to message #680179] Mon, 27 April 2020 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 26 April 2020 12:06
Michel Cadot wrote on Sun, 26 April 2020 07:45

Michel Cadot wrote on Sat, 25 April 2020 20:25

Can you provide what is asked in the way it is asked.
Re: Query [message #680183 is a reply to message #680180] Mon, 27 April 2020 03:02 Go to previous messageGo to next message
bha_96
Messages: 11
Registered: April 2020
Junior Member
HI Experts,



I have tried to make the query, and result is successfully coming as per requirement:





with t_contract as (

select distinct process_name,application,contract from STATS_TRACK_SUMMARY_BY_MIN

),





t_application as (

select process_name, application, listagg(contract,',') within group ( order by null) contract_list from t_contract

where process_name = 'ws_router'

group by process_name, application

union all

select distinct process_name, application, contract contract_list from STATS_TRACK_SUMMARY_BY_MIN where process_name != 'ws_router'

)





select TO_CHAR(EVENT_MINUTE, 'MM-YYYY') AS EVENT_MINUTE,

b.PROCESS_NAME,

contract_list,

b.APPLICATION,

SUM(TOTAL_COUNT) AS TOTAL_COUNT,

SUM(TOTAL_COUNT)-SUM(COUNT_FAILED) AS SUCCESS_TRANS,

ROUND(AVG(AVG_DURATION),2) AS AVG_DURATION,

ROUND((SUM(TOTAL_COUNT)-SUM(COUNT_FAILED))/SUM(TOTAL_COUNT)*100) AS SUCCESS_PERCENTAGE

from STATS_TRACK_SUMMARY_BY_MIN c, t_contract a, t_application b

where

a.process_name=b.process_name

and c.process_name=a.process_name and

EVENT_MINUTE >= ADD_MONTHS(TRUNC (SYSDATE,'MM'), - 3)

AND CONTRACT_LIST <> 'NULL'

AND b.APPLICATION IN ('CAP', 'cap')

GROUP BY TO_CHAR(EVENT_MINUTE, 'MM-YYYY'),

contract_list,

b.APPLICATION,

b.PROCESS_NAME

ORDER BY TO_DATE(EVENT_MINUTE,'MM-YYYY')







from the above query output is coming as expected but values for other columns are showing very high:

EVENT_MINUTE PROCESS_NAME CONTRACT_LIST APPLICATION TOTAL_COUNT SUCCESS_TRANS AVG_DURATION SUCCESS_PERCENTAGE

02-2020 workorderinquire getWorkOrderDetails cap 65874 63576 0.9 97

02-2020 workorderservices updateWorkOrder cap 81177 80292 0.92 99

02-2020 workorderinquire getWorkOrderList cap 65874 63576 0.9 97

02-2020 ws_router forceDispatch,forceGetDetails,forceGetWorkList,getAndDispatch CAP 205940 205940 0.21 100

03-2020 workorderservices updateWorkOrder cap 5595552 5509080 0.92 98

04-2020 workorderinquire getWorkOrderList cap 113004 109110 0.89 97

04-2020 workorderinquire getWorkOrderDetails cap 113004 109110 0.89 97

04-2020 workorderservices updateWorkOrder cap 4941096 4885869 0.74 99

04-2020 ws_router forceDispatch,forceGetDetails,forceGetWorkList,getAndDispatch CAP 3273900 3273900 0.1 100





But when we run this query we are getting the below results:



SELECT TO_CHAR(EVENT_MINUTE, 'MM-YYYY') AS EVENT_MINUTE,

PROCESS_NAME,

CONTRACT,

APPLICATION AS APPLICATION,

SUM(TOTAL_COUNT) AS TOTAL_COUNT,

SUM(TOTAL_COUNT)-SUM(COUNT_FAILED) AS SUCCESS_TRANS,

ROUND(AVG(AVG_DURATION),2) AS AVG_DURATION,

ROUND((SUM(TOTAL_COUNT)-SUM(COUNT_FAILED))/SUM(TOTAL_COUNT)*100) AS SUCCESS_PERCENTAGE

FROM STATS_TRACK_SUMMARY_BY_MIN

WHERE EVENT_MINUTE >= ADD_MONTHS(TRUNC (SYSDATE,'MM'), - 3)

AND CONTRACT <> 'NULL'

AND APPLICATION IN ('cap','CAP')

GROUP BY TO_CHAR(EVENT_MINUTE, 'MM-YYYY'),

CONTRACT,

APPLICATION,

PROCESS_NAME

ORDER BY TO_DATE(EVENT_MINUTE,'MM-YYYY')











EVENT_MINUTE PROCESS_NAME CONTRACT_LIST APPLICATION TOTAL_COUNT SUCCESS_TRANS AVG_DURATION SUCCESS_PERCENTAGE

02-2020 workorderinquire getWorkOrderList cap 1952 1952 1.03 100

02-2020 ws_router forceGetDetails CAP 2 2 0.11 100

02-2020 workorderinquire getWorkOrderDetails cap 45 26 1.11 58

02-2020 workorderservices updateWorkOrder cap 4520 4476 1.38 99

02-2020 ws_router forceGetWorkList CAP 6 6 0.05 100

03-2020 workorderservices updateWorkOrder cap 350317 345780 1.39 99

04-2020 workorderservices updateWorkOrder cap 255221 252429 1.27 99

04-2020 ws_router forceGetWorkList CAP 1 1 0.06 100

04-2020 workorderinquire getWorkOrderList cap 6 6 1.55 100







If we compare the values from both the queries values for TOTAL_COUNT, SUCCESS_TRANS, AVG_DURATION, SUCCESS_PERCENTAGE is very high in above query can you please suggest here.

Re: Query [message #680184 is a reply to message #680183] Mon, 27 April 2020 03:14 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
BHA, is your obnoxious behaviour is deliberate? You are repeatedly making posts that are not properly formatted. This looks very like trolling: being rude in an attempt to make people angry. Please stop flooding the forum with rubbish.

I shall lock this topic.



Re: Query [message #680185 is a reply to message #680183] Mon, 27 April 2020 03:16 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In the end, it seems you are just a troll trying to make people angry.
This topic is locked.
Come back with a proper request in another topic.

Previous Topic: How too convert rows into columns based on order hiredate month wise
Next Topic: "ORA-00942: table or view does not exist" when creating view via role's privilege
Goto Forum:
  


Current Time: Thu Mar 28 15:45:55 CDT 2024