Swadhin Ray
asked on
Ways to write the SQL
Hello Experts,
I have a SQL which gives me the correct result but is there any better way to modify the SQL ? As I am using the same table and same column multiple times to check the counts on various stages.
Here is my code:
I have a SQL which gives me the correct result but is there any better way to modify the SQL ? As I am using the same table and same column multiple times to check the counts on various stages.
Here is my code:
select distinct mth,
cnt,
new_ra,
renewals,
closed
from
(select to_char(dd_created_on,'MON') mth,
count( *) over ( partition by (to_char(dd_created_on,'MON')) ) cnt ,
(
select count(*)
from ms_raf_risk_acc a
where status ='Approved'
and ( renew_flag ='N'
or renew_flag is null)
and to_char(approval_date,'MON')=to_char(sysdate,'MON')
) as new_ra ,
(
select distinct count(*) over (partition by t.status) --AS draft_cnt
from
(select v.status,
row_number () over (partition by v.process_instance_id order by v.instance_id desc ) cnt
from ms_raf_risk_accept_v v
where v.renew_flag = 'Y'
and (to_char (v.creation_date, 'MON') = to_char(p.dd_created_on,'MON'))
and status = 'Draft'
and process_instance_id not in
(select process_instance_id
from ms_raf_risk_accept_v
where status not in ('Draft')
)
) t
where t.cnt =1
) as renewals ,
(select count(*) closed
from ms_raf_risk_acc z
where z.status ='Closed'
and to_char(z.closure_date,'MON') = to_char(p.dd_created_on,'MON')
) as closed
from ms_raf_risk_acc p
where p.dd_created_on between (add_months(sysdate,-6)) and sysdate
);
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
now say if we don't want to use partition by query then what should be the right approach .
I think this query can be optimized. In this section, your are querying ms_raf_risk_accept_v multiplt times.
first with filter "status = 'Draft'" and then in sub-query with filter "status NOT IN ( 'Draft' )". What is your intention here?
Are you interested to get the processes which are only in 'Draft' status?
first with filter "status = 'Draft'" and then in sub-query with filter "status NOT IN ( 'Draft' )". What is your intention here?
Are you interested to get the processes which are only in 'Draft' status?
FROM ms_raf_risk_accept_v v
WHERE v.renew_flag = 'Y'
AND ( to_char (v.creation_date, 'MON') = to_char(p.dd_created_on, 'MON') )
AND status = 'Draft'
AND process_instance_id NOT IN (SELECT process_instance_id
FROM ms_raf_risk_accept_v
WHERE status NOT IN ( 'Draft' ))) t
ASKER
the intension is to get the counts for each status from same table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
but i think the optimizer will concat the results anyway
and will probably access the data via different indexes also
but your where clauses are totally different ... so i don't really see a point
like these 2:
select count(*)
from ms_raf_risk_acc a
where status ='Approved'
and ( renew_flag ='N'
or renew_flag is null)
and to_char(approval_date,'MON
select count(*) closed
from ms_raf_risk_acc z
where z.status ='Closed'
and to_char(z.closure_date,'MO