Re: THE SQL CHALLENGE!

In article <3478u6$lit_at_charm.magnus.acs.ohio-state.edu> phart_at_magnus.acs.ohio-state.edu (Patrick S Hart) writes:
>I have been trying, to no avail, to solve the below problem with only one
>pass at the data. I was curious to know if anyone can solve this:
 

>Table Definition:
 

> StockNumber Product LastInvDate LastInvQty
>-------------------------------------------------------------
> NSN4567 Shoes 08/12/94 1,000
> NSN4567 Shoes 09/01/94 1,500
> NSN2345 Pants 08/23/94 1,200
> NSN2345 Pants 08/05/94 1,500
 

>I want to select the *last* recorded Inventory for a given date. So if the
>date
>given is 09/02/94, the result should look like this:
 

> NSN4567 Shoes 09/01/94 1,500
> NSN2345 Pants 08/23/94 1,200

Hi Patrick,

I'm not sure what you mean by one pass, but you can do this with one SQL query:

  select I1.StockNumber, I1.Product, I1.LastInvDate, I1.LastInvQty   from INVENTORY I1
  where

      (I1.StockNumber, I1.LastInvDate) in 
            ( select I2.StockNumber, MAX(I2.LastInvDate)
              from INVENTORY I2
              where I2.LastInvDate <= SYSDATE
              group by I2.StockNumber )

This is assuming that StockNumber is a primary key and the table name is INVENTORY. What you will get is your current inventory as of the current date. If you wnat to know you inventory at a specific time, just replay SYSDATE in the sub-query with the appropriate date.

Hope this helps....
Tom

--
--
  |\                    +---------------------------------+
  | ) ----------------- | Tommy Hui                       | The views expressed
 / /  Energy            | Programmer/Analyst              | here are solely  my
( (    Management       |_________________________________| own  and  in no way
 \ \    Solutions Corp. |                                 | reflect  the  views
  | ) ----------------- | ems2_at_delphi.calgary.chevron.com | of my employer.
  |/                    +---------------------------------+