Selecting Records based on values in other records

[RonVolkmar]

I have aircraft parts inventory program that uses a SQL7.0 DB. One table is Transactions which contains part numbers, date, type, serial numbers, and shelflife date among others. Typical types are RECEIVE, REMOVE, and ISSUE. The shelflife date is important when the part is on the shelf, but once issued or shipped out for repair, the part does not have a shelflife. However, it can return to the shelf and then it gets a shelflife again. Typical data is below.

part date type serial# shelflife
9930 2002-12-17 REMOVE 1635-2 2003-12-31
9930 2002-12-17 SHIP 1635-2 NULL
9930 2002-12-24 RECEIVE 1635-2 2003-12-31
9930 2003-01-06 ISSUE 1635-2 NULL
9930 2003-08-11 REMOVE 1635-2 2004-08-31
9930 2003-08-11 SHIP 1635-2 NULL
9930 2003-08-19 RECEIVE 1635-2 2004-08-31
9930 2003-08-28 ISSUE 1635-2 NULL

I am having trouble generating a shelflife report. A "SELECT....Max(Shelflife)", works but returns a shelflife even when the part has been issued (TYPE = ISSUE).

Thanks in advance for any suggestions on how to return a Shelflife date when the TYPE is other than ISSUE, but still return the latest shelflife date for a given part and serial number?

Ron

 

[fredericofonseca]

can you post the full select you have tried so far. (This does look a lot like homework!!)




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[RonVolkmar]

Hi Frederico,

Here is what I thought would work, but it returns a shelflife when the part has been removed from the shelf (TYPE = ISSUE OR SHIPPED).

select part, date, type, serialnumber, max(shelflife)
from transactions
group by part, date, type, serialnumber

Your comment that this may be difficult was helpful. I was thinking there was an easy solution that I was missing. As a work-around, I can manage something in the application. I was thinking of having the program make the shelflife (date) something like "2099-12-31" when the part was issued and then change that "2099-12-31" back to the current value when the part is returned to the shelf.

Therefore, unless you want the challenge, you don't "do a lot of homework."

Ron

 

[swampBoogie]

What do you mean with shelflife report?

What result do you expect from the given sample data?

 

[fredericofonseca]

I still don'w know exactly what you require (some information is missing), but you will probably wish to add a where clause to it

after the from ...

where TYPE not in('ISSUE', 'SHIPPED')

Not sure if thie is what you need..


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[RonVolkmar]

Sorry, I guess the desired results were not obvious. I need a report that returns the shelflife (actually the date when the shelflife expires) of the various parts in inventory (Note: Not all parts have a shelflife). For the sample data, if the report was run on 2002-12-25 with only the first 3 records, it should return 2003-12-31. If run on 2003-01-07, after the 4th record was added, indicating the part was issued (taken off the shelf and put on an airplane), it should return nothing as that part does not have a shelflife any more. If run again of 2003-08-31, it should return 2004-08-31, which is the new shelflife after being shipped out for repair.

In plain English, I need a select statement that returns shelflife if it is not NULL for latest (or last) record for unique part and serialnumber.

I hope this clarifies it. Thanks again for your efforts.

Ron

Ron

 

[swampBoogie]

select shelflife from <yourTable> 
where date = (select max(date) from <yourTable>)
  and shelflife is not null

 

[fredericofonseca]

Ron, I may be wrong but I think you have something wrong on your last post.

1- You mention that
"If run on 2003-01-07, after the 4th record was added, indicating the part was issued (taken off the shelf and put on an airplane), it should return nothing as that part does not have a shelflife any more"

This gives us the most recent record with an "ISSUE" type, and therefore the whole part should be ignored.

2- then you mention
"If run again of 2003-08-31, it should return 2004-08-31, which is the new shelflife after being shipped out for repair."
Now the lastest record also has a "ISSUE" type. Should it not be ignored?

If I am correct you need a compound select, where you first select the max of date to be used on the second part of the select as a "key" to the correct record.

similar to

select a.part, a.date, a.type, a.serialnumber, max(a.shelflife) from
(select part, max(date), serialnumber
from transactions
group by part, date, serialnumber
) b, transactions a
where a.part = b.part and a.date = b.date and a.serialnumber = b.serialnumber
and b.shelflife is not null
group by part, date, type, serialnumber

(code may be wrong but you got the idea)


Not quite sure of how to do this one now, but I see a possible problem with it, this being the possibility of a part having two (or more records) of different parts on the same day. If this is possible then only the last record should be considered, and this may be tricky to do if you don't have another control field such as a sequence number.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[RonVolkmar]

Thanks, SwampBoogie, but that only returns a few values for the entire table as "select MAX(date) from transactions" only returns one value. Here is what I tried:


select part, serialnumber, shelflife from transactions
where date = (select max(date) from transactions)
and shelflife is not null

 

[swampBoogie]

I forgot the correlation

select shelflife from <yourTable> as t
where date = (select max(date) 
from <yourTable>
 where part = t.part)
  and shelflife is not null

 

[RonVolkmar]

Frederico,

I thought you had it. I understand exactly what you did and made perfect sense. I didn't realize a FROM could contain a SELECT...thanks.

But it returns an error

"No column was specified for column 2 of 'b'"


I guess I could try putting "b" into a temporary table.
**********************************

Yes, you were right, I misspoke. "If run again of 2003-08-31, it should return 2004-08-31" should be if you run again on 2003-08-20...."

 

[fredericofonseca]

select a.part, a.date, a.type, a.serialnumber, max(a.shelflife) from
(select part, max(date) as date, serialnumber
from transactions
group by part, date, serialnumber
) b, transactions a
where a.part = b.part and a.date = b.date and a.serialnumber = b.serialnumber
and b.shelflife is not null
group by part, date, type, serialnumber

But note that the above will not work on the situation I mentioned before.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[RonVolkmar]

SwampBoogie

It appears you nailed it.

Frederico,

Yours worked, but the exception you mentioned was an issue. It did have almost two records for each part.

My thanks to both of you. I learned a lot and the solution will be implemented today and be very helpful.

Ron

 

[dnoeth]

If you want to get rid of the second row:

select part,
nullif(max(coalesce(shelflife, '99991231')), '99991231') from transactions t
where date = (select max(date)
from TRAnsactions t2
where t2.part = t.part)
group by part

Dieter