Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

oracle query - count where percent is greater than 100

Posted on 2013-06-19
7
Medium Priority
?
712 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
  • 4
  • 2
7 Comments
 
LVL 38

Accepted Solution

by:
Geert Gruwez earned 336 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 664 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 664 total points
ID: 39260164
If pert can never be >= 200%, then
select ...,
floor(nvl(a.actual_draw,0)/a.projection) as count100%+
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

926 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