Solved

oracle query - count where percent is greater than 100

Posted on 2013-06-19
7
679 Views
Last Modified: 2013-06-19
select a.area_rep_no,
          a.drive_date,
          a.drive_id,          
          a.site_code,
          a.projection,
          nvl(a.actual_draw,0) actual_draw,      
          to_char(round((nvl(a.actual_draw,0) / a.projection) * 100 ),'fm9999.00') as pert,
          CASE
            WHEN coach_drive='C' THEN 'Coach'
            WHEN coach_drive='M' THEN 'Mini'
            ELSE 'Inside'
         END coach_drive
  from blood_drives a,
       sites b
where a.site_code = b.site_code
  and a.projection != 0
  and a.drive_cancelled is null
  and a.drive_date between '01-may-2013' and '31-may-2013'
  and a.area_rep_no = 12
order by 1,2 ,4

------------------------

AREA_REP_NO|DRIVE_DATE|DRIVE_ID|SITE_CODE|PROJECTION|ACTUAL_DRAW|PERT|COACH_DRIVE
12|5/1/2013|2019509|AL05|21|19|90.00|Coach
12|5/1/2013|2019058|EP01|94|82|87.00|Inside
12|5/1/2013|2020841|HL11|7|3|43.00|Mini
12|5/5/2013|2019536|KK04|26|21|81.00|Coach
12|5/5/2013|2021500|PH26|31|19|61.00|Inside
12|5/6/2013|2019537|BV06|21|15|71.00|Mini
12|5/7/2013|2019544|HD04|21|30|143.00|Inside
12|5/8/2013|2021889|BV10|24|23|96.00|Inside
12|5/8/2013|2019549|BY03|10|11|110.00|Coach
12|5/9/2013|2021793|BV12|6|2|33.00|Mini
12|5/14/2013|2019470|AL36|21|19|90.00|Coach
12|5/15/2013|2019562|BY07|26|17|65.00|Coach
12|5/15/2013|2019511|HS01|31|41|132.00|Inside
12|5/15/2013|2019592|KK08|19|16|84.00|Coach
12|5/15/2013|2019555|SU12|8|5|63.00|Mini
12|5/16/2013|2019517|HS01|31|32|103.00|Inside
12|5/17/2013|2019391|CI04|22|17|77.00|Inside
12|5/20/2013|2019451|CU09|15|16|107.00|Mini
12|5/22/2013|2019650|EP11|63|84|133.00|Inside
12|5/23/2013|2018861|KK11|30|26|87.00|Inside
12|5/25/2013|2021808|KK16|20|19|95.00|Inside

My requirement is - I need a count where the percent(pert) is > 100 %
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 84 total points
ID: 39260126
wrap the select in a select

select count(*) from (
select a.area_rep_no,
          a.drive_date,
          a.drive_id,          
          a.site_code,
          a.projection,
          nvl(a.actual_draw,0) actual_draw,      
          to_char(round((nvl(a.actual_draw,0) / a.projection) * 100 ),'fm9999.00') as pert,
          CASE
            WHEN coach_drive='C' THEN 'Coach'
            WHEN coach_drive='M' THEN 'Mini'
            ELSE 'Inside'
         END coach_drive
  from blood_drives a,
       sites b
where a.site_code = b.site_code
  and a.projection != 0
  and a.drive_cancelled is null
  and a.drive_date between '01-may-2013' and '31-may-2013'
  and a.area_rep_no = 12
order by 1,2 ,4)
where pert > 100

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 166 total points
ID: 39260144
select ...,
sum(case when nvl(a.actual_draw,0)/a.projection > 1 then 1 else 0 end) as count100%+
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 166 total points
ID: 39260164
If pert can never be >= 200%, then
select ...,
floor(nvl(a.actual_draw,0)/a.projection) as count100%+
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 32

Expert Comment

by:awking00
ID: 39260180
BTW, the problem with pert > 100 is that it's a character field when applying the to_char function so, for example, '90' will be greater than '100'
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 39260457
I slightly changes the query

select count(*) from (
select a.area_rep_no,
          a.drive_date,
          a.drive_id,          
          a.site_code,
          a.projection,
          nvl(a.actual_draw,0) actual_draw,      
          to_char(round((nvl(a.actual_draw,0) / a.projection) * 100 ),'fm9999.00') as pert,
              (nvl(a.actual_draw,0) / a.projection) * 100  as pert1,
          CASE
            WHEN coach_drive='C' THEN 'Coach'
            WHEN coach_drive='M' THEN 'Mini'
            ELSE 'Inside'
         END coach_drive
  from blood_drives a,
       sites b
where a.site_code = b.site_code
  and a.projection != 0
  and a.drive_cancelled is null
  and a.drive_date between '01-may-2013' and '31-may-2013'
  and a.area_rep_no = 12
order by 1,2 ,4)
where pert1 > 100
0
 
LVL 32

Expert Comment

by:awking00
ID: 39260528
There is no need to replicate the whole query if you only wanted the count and not the other attribute values, why not just -
select sum(case when pert > 1 then 1 else 0 end) as cnt from
(select nvl(a.actual_draw,0)/a.projection pert
 from blood_drives a, sites b where a.site_code = b.site_code
 and a.projection != 0 and a.drive_cancelled is null
 and a.drive_date between '01-may-2013' and '31-may-2013' and a.area_rep_no = 12)

Also, be careful with
"a.drive_date between '01-may-2013' and '31-may-2013'"
If drive_date contains a time portion, you will not get any
records for 31-may-2013 except for precisely midnight and,
when doing date math, it is always better explicitly convert
to date datatypes -
where a.drive_date >= to_date('01-may-2013',dd-mon-yyyy')
  and a.drive_date < to_date('01-jun-2013',dd-mon-yyyy')
0
 
LVL 6

Author Comment

by:anumoses
ID: 39260545
We are not hardcoding dates. It was as an example I gave. We are taking dates from the drives table.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question