Solved

oracle query - count where percent is greater than 100

Posted on 2013-06-19
7
673 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 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
Technology Partners: 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

Technology Partners: 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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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
This video shows how to recover a database from a user managed backup

680 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