Re: Help with SQL

SELECT e.equipid, e.moldno, e.startrun, e.endrun,

       sum(nvl(f.fails, 0)) fails
FROM equip e, fail f
WHERE f.equipid (+) = e.equipid
AND f.moldno (+) = e.moldno
GROUP BY e.equipid, e.moldno, e.startrun, e.endrun /
"John Kramer" <john_at_ekramer.net> wrote in message news:fbcef4df.0201071557.302c2ad0_at_posting.google.com...
> I have two tables that are joined by five fields, though I've
> simplified the joins down to two for the example below. The first
> table holds equipment data (EQUIP), and the second (FAIL) holds data
> that reflects failures that occured in the production on the equipment
> stored in EQUIP. The EQUIP table stores the times that the equipment
> was run, and the FAIL table show aggregated data for time periods when
> there were failures on the equipment. Here's the problem I'm having:
>
> EQUIP Table FAIL Table
> EquipID MoldNo StartRun EndRun EquipID MoldNo Start End
> Fails
> 1 2 13:00 15:00 1 2 13:00
> 13:05 2 2 2 14:00 15:00 1 2
> 13:05 13:10 3
> 1 2 13:10
> 13:15 2
> 1 2 13:15
> 13:20 3
> 1 2 13:20
> 13:25 2
> 1 2 13:25
> 13:30 3
> ***The Start and End columns in the EQUIP table represent the time
> period that the equipment was run.
> ***The Start and End columns in the FAIL table represent 5 minute
> increments for recording failures
> *A record can only exist in the FAIL table if there is a record in the
> EQUIP table for the same time period, but no record necessarily exists
> in the FAIL table if one exists in the EQUIP table. In other words, a
> record is written into the FAIL table only when a failure occurs. If
> no failure occurs, no record is written.
>
> I'd like to get a result set that displays all equipment used during a
> time period (lets say between 13:00 and 15:00 for this day) and the
> sum quantity of fails that occurred on that equipment during the
> sepcific time period that it was run. If there were no fails on that
> equipment for that time period that it was run, I'd like it to show
> that there were no errors. Here's the resultset I'd hope to return for
> the previous data sets:
>
> EquipID MoldNo Start End Fails
> 1 2 13:00 15:00 15
> 2 2 14:00 15:00 0
>
> Everything I've tried will only records from the EQUIP table that have
> matching records in the FAIL table. I just can't see how to get this
> to work the way I'd like to. Do I need to use PL/SQL to accomplish
> this?
>
> Your help and expertise is greatly appreciated.
>
Received on Tue Jan 08 2002 - 02:56:24 CET