Hi,

I have a table like the following columns:

Warehouse                 INTEGER NOT NULL,
sku                            INTEGER  NOT NULL,
qtyOnHand                DECIMAL(9,4),
DateLastPurchase      DATE,
SupplierLastPurchase   INTEGER

Primary key is  Warehouse,Sku

It actually has more columns, but these serve for the purpose of an example.

I want to select the LAST purchase date for each SKU.   In other words, one 
row for each SKU, selecting the MAX(DateLastPurchase)

So if the table has

Warehouse  Sku      DateLastPuchase    SupplierLastPurchase
1                   1          '2010-01-15'           5
2                    1         '2010-01-12'            4
3                    1         '2010-01-10'           5
1                    2         '2010-01-10'            4
2                    2         '2010-01-10'            4
3                    2         '2010-01-12'            3


Would return
Warehouse  Sku      DateLastPuchase    SupplierLastPurchase
1                   1          '2010-01-15'           5
3                    2         '2010-01-12'            3


In other words, what was the last purchase for each item irrespective of the
warehouse.

How would I do this on MSSQL 2000?


Thanks,
Edgard

"Edgard L. Riba" <elriba at rimith dot com> wrote in message 
news:#RPKff7ELHA.5668@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> I have a table like the following columns:
>
> Warehouse                 INTEGER NOT NULL,
> sku                            INTEGER  NOT NULL,
> qtyOnHand                DECIMAL(9,4),
> DateLastPurchase      DATE,
> SupplierLastPurchase   INTEGER
>
> Primary key is  Warehouse,Sku
>
> It actually has more columns, but these serve for the purpose of an 
> example.
>
> I want to select the LAST purchase date for each SKU.   In other words, 
> one row for each SKU, selecting the MAX(DateLastPurchase)
>
> So if the table has
>
> Warehouse  Sku      DateLastPuchase    SupplierLastPurchase
> 1                   1          '2010-01-15'           5
> 2                    1         '2010-01-12'            4
> 3                    1         '2010-01-10'           5
> 1                    2         '2010-01-10'            4
> 2                    2         '2010-01-10'            4
> 3                    2         '2010-01-12'            3
>
>
> Would return
> Warehouse  Sku      DateLastPuchase    SupplierLastPurchase
> 1                   1          '2010-01-15'           5
> 3                    2         '2010-01-12'            3
>
>
> In other words, what was the last purchase for each item irrespective of 
> the
> warehouse.
>
> How would I do this on MSSQL 2000?
>
>
> Thanks,
> Edgard
>
>
>

And what happens if you have 2 different SupplierLastPurchase values on the 
same latest date? Which one do you want to see? Highest? Lowest? Average? 
Sum? Something else?

-- 
Dan 

CREATE TABLE Inventory_Report
(warehouse_nbr INTEGER NOT NULL,
 sku INTEGER NOT NULL,
 PRIMARY KEY (warehouse_nbr, sku),
 onhand_qty DECIMAL(9,4) NOT NULL,
 last_purchase_date DATE DEFAULR CURRENT_DATE NOT NULL,
 last_purchase_supplier_id INTEGER NOT NULL);

Since SKU is a "Stock Keeping Unit", how can you have decimal places
for the quantity? Can I assume that you do not want NULLs?

>> I want to select the LAST purchase date for each SKU. In other words, one row for each SKU, selecting the MAX(DateLastPurchase) <<

Here is one way:

WITH Max_SKU_Dates
AS
(SELECT sku, MAX(last_purchase_date)
  FROM Inventory_Report
 GROUP BY sku)

SELECT R.warehouse_nbr, R.sku, R.onhand_qty, R.last_purchase_date,
R.last_purchase_supplier_id
  FROM Inventory_Report AS R, Max_SKU_Dates AS L
WHERE R.sku = L.sku
  AND R.last_purchase_date = L.last_purchase_date;

This will preserve ties.

Hi Dan,

Thanks for looking into this.

In this particular case it doesn't matter.   I just need to select at least 
one supplier on the last date.

Edgard

>
> "Edgard L. Riba" <elriba at rimith dot com> wrote in message 
> news:#RPKff7ELHA.5668@TK2MSFTNGP04.phx.gbl...
>> Hi,
>>
>> I have a table like the following columns:
>>
>> Warehouse                 INTEGER NOT NULL,
>> sku                            INTEGER  NOT NULL,
>> qtyOnHand                DECIMAL(9,4),
>> DateLastPurchase      DATE,
>> SupplierLastPurchase   INTEGER
>>
>> Primary key is  Warehouse,Sku
>>
>> It actually has more columns, but these serve for the purpose of an 
>> example.
>>
>> I want to select the LAST purchase date for each SKU.   In other words, 
>> one row for each SKU, selecting the MAX(DateLastPurchase)
>>
>> So if the table has
>>
>> Warehouse  Sku      DateLastPuchase    SupplierLastPurchase
>> 1                   1          '2010-01-15'           5
>> 2                    1         '2010-01-12'            4
>> 3                    1         '2010-01-10'           5
>> 1                    2         '2010-01-10'            4
>> 2                    2         '2010-01-10'            4
>> 3                    2         '2010-01-12'            3
>>
>>
>> Would return
>> Warehouse  Sku      DateLastPuchase    SupplierLastPurchase
>> 1                   1          '2010-01-15'           5
>> 3                    2         '2010-01-12'            3
>>
>>
>> In other words, what was the last purchase for each item irrespective of 
>> the
>> warehouse.
>>
>> How would I do this on MSSQL 2000?
>>
>>
>> Thanks,
>> Edgard
>>
>>
>>
>
> And what happens if you have 2 different SupplierLastPurchase values on 
> the same latest date? Which one do you want to see? Highest? Lowest? 
> Average? Sum? Something else?
>
> -- 
> Dan 

Edgard L. Riba (elriba at rimith dot com) writes:
> Thanks for looking into this.
> 
> In this particular case it doesn't matter.   I just need to select at
> least one supplier on the last date. 

But why then bother at all? I always get an uneasy feeling when someone
says "one, but it does not matter which".

Anyway, this could do it.

   SELECT Warehouse, Sku, LastDate, 
          (SELECT TOP 1 SupplierLastPurchase
           FROM   tbl b
           WHERE  b.Warehouse = a.Warehouse
             AND  b.Sku       = b.Sku
             AND  b.Lastdate  = b.Lastdate
           ORDER  BY newid()) AS SupplierLastPurchase
   FROM   (SELECT Warehouse, Sku, LastDate = MAX(DateLastPurcahse)
           FROM   tbl
           GROUP  BY Warehouse, Sku) AS d
 
On SQL 2005/2008 you can use the row_number function which makes this a lot
easier, but you are not there unfortunately.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx