Results 1 to 5 of 5

Thread: sql query

  1. #1
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33

    Unanswered: sql query

    Hi,


    Table: Eqip_Location
    Loca_ID Numeric 10
    Loca_Name varchar 30


    Table: Equipment
    Eq_id N 10
    eq_loca_id N 10
    eq_name var 30

    Table: Inst_equipment_RuntimeErr
    Inst_EQRun_id N 10
    Eq_id N 10
    StartTime date
    endtime Date


    Data:
    Eqip_Location
    Loca_ID Loca_Name
    1 Floor 1
    2 MFloor 1
    3 MFloor 2
    4 MFloor 3

    Equipment
    Eq_id eq_loca_id eq_name
    1 1 CNC1
    2 1 CNC2
    3 3 CNC3
    4 1 CNC4


    Inst_equipment_RuntimeErr
    Inst_EQRun_id Eq_id StartTime endtime
    1 1 01/nov/2004 9:00 03/nov/2004 9:00
    2 3 05/nov/2004 9:00 09/nov/2004 9:00
    3 4 02/nov/2004 9:00 09/nov/2004 9:00
    4 2 06/nov/2004 2:00 12/nov/2004 19:00
    5 3 25/nov/2004 10:00 29/nov/2004 3:00


    Select Equipment.eq_name MaxDur_Equiment,
    Equipment.eq_name MinDur_Equiment,
    Equipment.eq_name MostTime_Equiment,
    Eqip_Location.Loca_Name
    from Inst_equipment_RuntimeErr,Equipment,Eqip_Location
    group by Eqip_Location.loca_ID

    Above is the Sql statement. but I have to
    incorporate the below logic to findout the
    values for MaxDur_Equiment,MinDur_Equiment,MostTime_Equiment


    MaxDur_Equiment(eq_name):
    Equipment name(eq_name) should be displayed
    based on the maximum time difference between the fields
    Inst_equipment_RuntimeErr.StartTime and Inst_equipment_RuntimeErr.endtime
    for an Equipment location (Eqip_Location.loca_ID).

    MinDur_Equiment(eq_name):
    Equipment name(eq_name) should be displayed
    based on the minimum time difference between the fields
    Inst_equipment_RuntimeErr.StartTime and Inst_equipment_RuntimeErr.endtime
    for a Equipment location (Eqip_Location.loca_ID).

    MostTime_Equiment(eq_name):
    Is the maximum number of errors reported (Inst_equipment_RuntimeErr)
    from a location (Eqip_Location).

    Thanx.
    sam

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    here's a start. If you get errors, just post the errors or something:
    PHP Code:
    select distinct
      
    CASE when maxdur_equiment maxdur then eq_name end maxdur_equiment
      CASE 
    when mindur_equiment mindur then eq_name end mindur_equiment
      
    mosttime_equiment
      
    loca_name
    from 
    (
            
    select 
                e
    .eq_name,
                
    max(ier.endtime-ier.starttimeover (partition by el.loca_namemaxdur_equiment,
                
    max(ier.endtime-ier.starttimeover (partition by el.loca_namee.eq_namemaxdur,
                
    min(ier.endtime-ier.starttimeover (partition by el.loca_namemindur_equiment,
                
    min(ier.endtime-ier.starttimeover (partition by el.loca_namee.eq_namemindur,
                
    count(ier.inst_eqrun_idover (partition by el.loca_namemosttime_equiment,
                
    el.loca_name
            from 
                inst_equipment_runtimeerr eir
    ,
                
    equipment e,
                
    eqip_location el
            where
                
    /* link all your tables together */
                
    e.eq_loca_id el.loca_id and
                
    e.eq_id ier.eq_id
          
    )
    where 
        maxdur_equiment 
    maxdur OR
        
    mindur_equiment mindur
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33
    Hi,

    Thank you for your solution. It worked fine. Actually
    i need only one line for a location.

    The current output is like this:
    MAXDUR_EQUIMENT MINDUR_EQUIMENT MOSTTIME_EQUIMENT LOCA_NAME
    ------------------------------ ------------------------------ ----------------- --------
    CNC1 3 Floor 1
    CNC4 3 Floor 1
    CNC3 CNC3 2 MFloor 2



    I wanted it this way:
    MAXDUR_EQUIMENT MINDUR_EQUIMENT MOSTTIME_EQUIMENT LOCA_NAME
    ------------------------------ ------------------------------ ----------------- --------
    CNC4 CNC1 3 Floor 1
    CNC3 CNC3 2 MFloor 2


    MOSTTIME_EQUIMENT: should be the equipment name (e.eq_name) and not the count.
    That is the equipment name, which reported maximum number
    of errors.

    Thank you again for your time.

    sam.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I think this will do..
    Code:
    SQL@8i> select * from equipment;
    
         EQ_ID EQ_LOCA_ID EQ_NAME
    ---------- ---------- ------------------------------
             1          1 CNC1
             2          1 CNC2
             3          3 CNC3
             4          1 CNC4
    
    SQL@8i> select * from Eqip_location;
    
       LOCA_ID LOCA_NAME
    ---------- ------------------------------
             1 Floor 1
             2  MFloor 1
             3  MFloor 2
             4  MFloor 3
    
    SQL@8i> select * from Inst_equipment_RuntimeErr;
    
    INST_EQRUN_ID      EQ_ID STARTTIME         ENDTIME
    ------------- ---------- ----------------- -----------------
                1          1 01/nov/2004 09:00 03/nov/2004 09:00
                2          3 05/nov/2004 09:00 09/nov/2004 09:00
                3          4 02/nov/2004 09:00 09/nov/2004 09:00
                4          2 06/nov/2004 02:00 12/nov/2004 19:00
                5          3 25/nov/2004 10:00 29/nov/2004 03:00
    
    SQL@8i> select substr( max( diff || eq_name ),11 ) MaxDur_Equiment,
      2         substr( min( diff || eq_name ),11 ) MinDur_Equiment,
      3         substr( max( to_char( cnt,'fm0000000' ) || eq_name ),8 ) MostTime_Equiment,
      4         substr( max( to_char( cnt,'fm0000000' ) || loca_name ),8 ) Loca_Name
      5    from (
      6  select el.loca_id,
      7         el.loca_name,
      8         e.eq_name,
      9         to_char( i.starttime - i.endtime,'fm00000.000' ) diff,
     10         count( * ) over( partition by el.loca_id, e.eq_name ) cnt
     11    from equipment e, Eqip_location el, Inst_equipment_RuntimeErr i
     12   where e.eq_loca_id = el.loca_id
     13     and i.eq_id = e.eq_id
     14         )
     15   group by loca_id
     16  /
    
    MAXDUR_EQUIMENT                MINDUR_EQUIMENT                MOSTTIME_EQUIMENT               LOCA_NAME
    ------------------------------ ------------------------------ ------------------------------- ------------------------
    CNC4                           CNC1                           CNC4                            Floor 1
    CNC3                           CNC3                           CNC3                             MFloor 2
    
    SQL@8i>

  5. #5
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33
    Hi The_Duck & JMartinez,

    JMartinez, your query worked as desired. thank you.

    The_Duck, Thanx for giving me the start with the query, I was also able to modify your query to my requirement.

    Bye.

    sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •