Solved

oracle query - count where percent is greater than 100

Posted on 2013-06-19
7
661 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

778 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