QUERY- I have an oracle database that holds maintenance records.

[tav1035]

I have an oracle database that holds maintenance records. I'm looking to find where my "REACTIVE" calls have increased this year over last year per "ASSET".


Field names:
WONUM= work order number.
WORKTYPE= work order maintenance types (REACTIVE).
ASSETNUM= unique asset id tag.
REPORTDATE= date the work order was created.

Is it possible to write a query like the example below?
ie... WHERE (count(worktype ='REACTIVE') and reportpdate > trunc(sysdate -365)) > (count(worktype ='REACTIVE') and (reportpdate > trunc(sysdate -720 and reportpdate <trunc(sysdate -365))))

Thanks,
tav

 

[SantaMufasa]

Is it possible to write a query like the example below?

Close...There are many ways to achieve what you want. Here is one of those ways:

SQL> select * from work_orders;

     WONUM WORKTYPE     ASSETNUM REPORTDAT
---------- ---------- ---------- ---------
         1 REACTIVE          100 12-MAY-09
        10 REACTIVE          100 07-MAY-08
        11 REACTIVE          100 07-MAY-08
        12 REACTIVE          100 07-MAY-08
        13 REACTIVE          200 12-MAY-09
        14 REACTIVE          200 07-MAY-08
        15 INSTALL           200 06-MAY-08
        16 REACTIVE          300 12-MAY-09
        17 REACTIVE          300 11-MAY-09
        18 REACTIVE          300 07-MAY-08

col x heading "<,=,>" format a6
col new_cnt heading "This|Year" format 999
col old_cnt heading "Last|Year" format 999
select new.assetnum
      ,old_cnt
      ,decode(sign(new_cnt-old_cnt)
             ,-1,'>'
             ,0 ,'='
             ,1 ,'<') x
      ,new_cnt
  from (select assetnum,count(*)new_cnt from work_orders
         where worktype = 'REACTIVE'
           and trunc(reportdate) > trunc(sysdate - 365)
         group by assetnum) new
      ,(select assetnum,nvl(count(*),0)old_cnt from work_orders
         where worktype = 'REACTIVE'
           and trunc(reportdate) between trunc(sysdate - 720) and trunc(sysdate - 365)
         group by assetnum) old
 where new.assetnum = old.assetnum(+);

           Last        This
  ASSETNUM Year <,=,>  Year
---------- ---- ------ ----
       100    3 >         1
       200    1 =         1
       300    1 <         2

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[tav1035]

Mufasa,
3 things-
FIRST- I'm not familiar with running this type of query, I did get it to run in "Oracle, SQL Plus", but stopped it when it kept running. When I run this it runs through all of the fields in my table and scrolls them across the page. Is this going to bog down or hurt our database?
I just need the counts this year and the counts last year of only the ones where this year is greater than last year.

SECOND- Also, we already have a java based front end client that has a query window that excepts advanced queries (sql or PLSQL), or we can type the queries into the fields for searching. It will then return the records back in the same software as a spreadsheet type view of single records. I was thinking that I could ask for where the count for this year is greater than last year grouped by assetnum.
THIRD- I would also like to try to do this in excel using msquery and build a dashboard of RED, YELLOW, GREEN statuses if the REACTIVE increased by 10%, 50% or 100% or something to that nature.
I would have to work out the syntax. Any ideas?

tav