Link to home
Start Free TrialLog in
Avatar of Swadhin Ray
Swadhin RayFlag for United States of America

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:

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
    );

Open in new window

Avatar of Geert G
Geert G
Flag of Belgium image

you could use a count(case ...), count(case ...)  for the separate calculation on each table
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')=to_char(sysdate,'MON')

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')
SOLUTION
Avatar of Geert G
Geert G
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Swadhin Ray

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?
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 

Open in new window

the intension is to get the counts for each status from same table
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial