Did I miscopy John's query? It gives me the future price of $25.04
for Product One, but is easy to fix by adding

AND P1.EffectiveDate <= CURRENT_TIMESTAMP

to the where clause of the derived table LatestPrices

John's initial idea of NOT EXISTS still works, too, in part. (Note
that I blew off the ProductName parsing by taking a hatchet to that
column with an update...


-- Be lazy
UPDATE ProductList SET
ProductName =
SUBSTRING(ProductName, 1,CHARINDEX('(', ProductName) - 2)
GO

-- For each product, select the lowest non-future
-- price that has not been superseded.
SELECT ProductName, MIN(Price) as Price -- get the lowest price
FROM (
SELECT ProductName, Price -- from all non-future prices
FROM ProductList P1
WHERE P1.EffectiveDate <= CURRENT_TIMESTAMP
AND NOT EXISTS ( -- that have not been superseded
SELECT * FROM ProductList P2
WHERE P2.Distributor = P1.Distributor
AND P2.ProductName = P1.ProductName
AND P2.EffectiveDate <= CURRENT_TIMESTAMP
AND P2.EffectiveDate > P1.EffectiveDate
)
) T
GROUP BY ProductName -- for each product
go

By the way, the delimited data was no trouble. I pasted it into
Excel, did Text To Columns, named the data region, and inserted it with:

insert into [ProductList]
select Manufacturer, ProductName, Price, EffectiveDate
from OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=e:\excel\ProductList.xls',
'select * from ProductList')

Be sure to close the Excel file before inserting.

-- Steve Kass
-- Drew University
-- Ref: 2498EA13-1756-4C81-9307-25AFD5BB451D



netpurpose wrote:
> Hi Jag,
>
> Thanks a lot!! It works like a charm! Your query saves me a lot of headache.
>
> I have struggled with this for quite a while now and I am glad I find help here.
>
> Again, thank you very much! I really appreciate your help in such a short time!
>
> Regards,
> Netpurpose
>
>
> "John Gilson" <jag@acm.org> wrote in message news:<amO3b.52163$ev.12155054@twister.nyc.rr.com>. ..
>
>>"netpurpose" <netpurpose@hotmail.com> wrote in message
>>news:31c57b38.0308291024.6bd2a29c@posting.google .com...
>>
>>>Hi Jag,
>>>
>>>Thank you for your response. I tried the query, but it gives me a
>>>different pricing result:
>>>
>>>ProductName Price
>>>---------------------------------------- ---------------------
>>>Product One (MXT234) 28.1500
>>>Product Two (DGT6789) 44.3500
>>>Product Three (QAT556) 40.1500
>>>
>>>The correct result should be:
>>>
>>>ProductName Price
>>>---------------------------------------- ---------------------
>>>Product One 33.5000
>>>Product Two 44.7500
>>>Product Three 40.4500
>>>
>>>The reason is because for Product One, Manufacturer "BBB" has a price
>>>of 28.15 on 2003-07-30, this price is no longer valid because "BBB"
>>>has more recent price of 33.5 starting on 2003-08-27. Same case with
>>>Product Two and Three (manufacturer "JJJ" overrides the 40.15 on
>>>2003-08-01 with 40.45 on 2003-08-04).
>>>
>>>I have to take the most recent prices from each manufacturer for a
>>>particular product (price at the latest date as of today), and then
>>>take the minimum price from the result.
>>>
>>>Again, thank you for your help. Sorry I might not explain it well in
>>>the beginning. Any further help is appreciated. Thank you very much
>>>in advance!
>>>
>>>Regards,
>>>Netpurpose
>>
>>I was a little too quick on the draw and misread your requirements.
>>
>>SELECT ProductName, MIN(Price)
>>FROM (SELECT P1.Distributor,
>> SUBSTRING(P1.ProductName, 1,
>> CHARINDEX('(', P1.ProductName) - 2) AS
>> ProductName,
>> P1.EffectiveDate AS LatestDate,
>> P1.price
>> FROM ProductList AS P1
>> LEFT OUTER JOIN
>> ProductList AS P2
>> ON P1.Distributor = P2.Distributor AND
>> P1.ProductName = P2.ProductName AND
>> P1.EffectiveDate <= CURRENT_TIMESTAMP AND
>> P2.EffectiveDate <= CURRENT_TIMESTAMP AND
>> P2.EffectiveDate > P1.EffectiveDate
>> WHERE P2.EffectiveDate IS NULL) AS LatestPrices
>>GROUP BY ProductName
>>
>>Regards,
>>jag
>>
>>
>>>"John Gilson" <jag@acm.org> wrote in message news:<_uI3b.51441$ev.11935402@twister.nyc.rr.com>. ..
>>>
>>>>"netpurpose" <netpurpose@hotmail.com> wrote in message
>>>>news:31c57b38.0308290040.6a560bd6@posting.goog le.com...
>>>>
>>>>>I need to extract data from this table to find the lowest prices of
>>>>>each product as of today. The product will be listed/grouped by the
>>>>>name only, discarding the product code - I use SUBSTRING(ProductName,
>>>>>1, CHARINDEX('(', ProductName)-2).
>>>>>
>>>>>I can get this result, but I had to use several views (totally
>>>>>inefficient). I think this can be done in one efficient/fast query,
>>>>>but I can't think of one.
>>>>>
>>>>>In the case that one query is not possible, is there other efficient
>>>>>way to get the results? There are about 300K records in the table.
>>>>>
>>>>>Any help is appreciated! Thanks a lot in advance!
>>>>>
>>>>>Thanks,
>>>>>Netpurpose
>>>>
>>>>SELECT P1.ProductName, P1.Price
>>>>FROM ProductList AS P1
>>>>WHERE EffectiveDate <= CURRENT_TIMESTAMP AND
>>>> NOT EXISTS
>>>> (SELECT *
>>>> FROM ProductList AS P2
>>>> WHERE SUBSTRING(P1.ProductName, 1,
>>>> CHARINDEX('(', P1.ProductName) - 2) =
>>>> SUBSTRING(P2.ProductName, 1,
>>>> CHARINDEX('(', P2.ProductName) - 2) AND
>>>> P2.EffectiveDate <= CURRENT_TIMESTAMP AND
>>>> P2.Price < P1.Price)
>>>>
>>>>Regards,
>>>>jag
>>>>
>>>>
>>>>>-----------------------START TABLE INFO-------------------------
>>>>>
>>>>>Here is the desired result (on 8/29/03):
>>>>>
>>>>>ProductName Price
>>>>>---------------------------------------- ---------------------
>>>>>Product One 33.5000
>>>>>Product Three 40.4500
>>>>>Product Two 44.7500
>>>>>
>>>>>
>>>>>Here is the table info:
>>>>>
>>>>>CREATE TABLE [ProductList] (
>>>>>[Distributor] [nvarchar] (5) NULL ,
>>>>>[ProductName] [nvarchar] (40) NULL ,
>>>>>[Price] [money] NULL ,
>>>>>[EffectiveDate] [smalldatetime] NULL
>>>>>) ON [PRIMARY]
>>>>>
>>>>>
>>>>>Table Data (comma delimited):
>>>>>*Sorry I can't list in insert commands as there are too many.
>>>>>
>>>>>"Manufacturer","ProductName","Price","Effecti veDate"
>>>>>"AAA","Product One (MXT234)",1000,2003-08-30 00:00:00
>>>>>"BBB","Product One (MXT234)",1000,2003-07-29 00:00:00
>>>>>"BBB","Product One (MXT234)",28.15,2003-07-30 00:00:00
>>>>>"BBB","Product One (MXT234)",35.22,2003-08-04 00:00:00
>>>>>"BBB","Product One (MXT234)",35.22,2003-08-04 00:00:00
>>>>>"BBB","Product One (MXT234)",1000,2003-08-16 00:00:00
>>>>>"BBB","Product One (MXT234)",33.8,2003-08-27 00:00:00
>>>>>"CCC","Product One (MXT234)",33.25,2003-08-31 00:00:00
>>>>>"DDD","Product One (MXT234)",46.25,2003-01-02 00:00:00
>>>>>"EEE","Product One (MXT234)",1000,2003-08-30 00:00:00
>>>>>"FFF","Product One (MXT234)",1000,2003-08-30 00:00:00
>>>>>"GGG","Product One (MXT234)",1000,2003-09-01 00:00:00
>>>>>"HHH","Product One (MXT234)",33.8,2003-08-04 00:00:00
>>>>>"III","Product One (MXT234)",1000,2003-01-13 00:00:00
>>>>>"JJJ","Product One (MXT234)",34.35,2003-07-30 00:00:00
>>>>>"JJJ","Product One (MXT234)",34.35,2003-09-01 00:00:00
>>>>>"KKK","Product One (MXT234)",1000,2003-08-30 00:00:00
>>>>>"ZZZ","Product One (MXT205)",54,2003-01-13 00:00:00
>>>>>"AAA","Product One (MXT205)",1000,2003-08-30 00:00:00
>>>>>"BBB","Product One (MXT205)",33.95,2003-07-27 00:00:00
>>>>>"BBB","Product One (MXT205)",33.95,2003-07-29 00:00:00
>>>>>"BBB","Product One (MXT205)",35.22,2003-08-04 00:00:00
>>>>>"BBB","Product One (MXT205)",33.8,2003-08-16 00:00:00
>>>>>"BBB","Product One (MXT205)",33.5,2003-08-27 00:00:00
>>>>>"CCC","Product One (MXT205)",1000,2003-08-30 00:00:00
>>>>>"DDD","Product One (MXT205)",46.25,2003-01-02 00:00:00
>>>>>"EEE","Product One (MXT205)",1000,2003-08-30 00:00:00
>>>>>"FFF","Product One (MXT205)",1000,2003-08-30 00:00:00
>>>>>"GGG","Product One (MXT205)",1000,2003-09-01 00:00:00
>>>>>"HHH","Product One (MXT205)",33.95,2003-08-04 00:00:00
>>>>>"III","Product One (MXT205)",1000,2003-01-13 00:00:00
>>>>>"JJJ","Product One (MXT205)",34.35,2003-07-30 00:00:00
>>>>>"JJJ","Product One (MXT205)",34.35,2003-09-01 00:00:00
>>>>>"KKK","Product One (MXT205)",1000,2003-08-30 00:00:00
>>>>>"ZZZ","Product One (MXT548)",54,2003-01-13 00:00:00
>>>>>"AAA","Product One (MXT548)",25.04,2003-08-31 00:00:00
>>>>>"BBB","Product One (MXT548)",33.95,2003-07-22 00:00:00
>>>>>"BBB","Product One (MXT548)",33.95,2003-07-27 00:00:00
>>>>>"BBB","Product One (MXT548)",35.22,2003-08-04 00:00:00
>>>>>"BBB","Product One (MXT548)",33.8,2003-08-16 00:00:00
>>>>>"BBB","Product One (MXT548)",33.8,2003-08-27 00:00:00
>>>>>"CCC","Product One (MXT548)",1000,2003-08-30 00:00:00
>>>>>"DDD","Product One (MXT548)",46.25,2003-01-02 00:00:00
>>>>>"EEE","Product One (MXT548)",1000,2003-08-30 00:00:00
>>>>>"FFF","Product One (MXT548)",1000,2003-08-30 00:00:00
>>>>>"GGG","Product One (MXT548)",1000,2003-09-01 00:00:00
>>>>>"HHH","Product One (MXT548)",33.8,2003-08-04 00:00:00
>>>>>"III","Product One (MXT548)",1000,2003-01-13 00:00:00
>>>>>"JJJ","Product One (MXT548)",34.35,2003-07-30 00:00:00
>>>>>"JJJ","Product One (MXT548)",34.35,2003-09-01 00:00:00
>>>>>"KKK","Product One (MXT548)",1000,2003-08-30 00:00:00
>>>>>"ZZZ","Product Two (DGT6789)",54,2003-01-13 00:00:00
>>>>>"AAA","Product Two (DGT6789)",1000,2003-08-30 00:00:00
>>>>>"BBB","Product Two (DGT6789)",1000,2003-07-22 00:00:00
>>>>>"BBB","Product Two (DGT6789)",44.75,2003-07-27 00:00:00
>>>>>"BBB","Product Two (DGT6789)",44.75,2003-07-29 00:00:00
>>>>>"BBB","Product Two (DGT6789)",44.35,2003-07-30 00:00:00
>>>>>"BBB","Product Two (DGT6789)",44.75,2003-08-04 00:00:00
>>>>>"BBB","Product Two (DGT6789)",44.75,2003-08-04 00:00:00
>>>>>"BBB","Product Two (DGT6789)",1000,2003-08-23 00:00:00
>>>>>"BBB","Product Two (DGT6789)",44.75,2003-08-27 00:00:00
>>>>>"CCC","Product Two (DGT6789)",1000,2003-08-30 00:00:00
>>>>>"DDD","Product Two (DGT6789)",46.25,2003-05-08 00:00:00
>>>>>"EEE","Product Two (DGT6789)",1000,2003-08-30 00:00:00
>>>>>"FFF","Product Two (DGT6789)",1000,2003-08-30 00:00:00
>>>>>"GGG","Product Two (DGT6789)",1000,2003-09-01 00:00:00
>>>>>"HHH","Product Two (DGT6789)",46.1,2003-08-04 00:00:00
>>>>>"III","Product Two (DGT6789)",1000,2003-01-13 00:00:00
>>>>>"JJJ","Product Two (DGT6789)",47.45,2003-08-04 00:00:00
>>>>>"JJJ","Product Two (DGT6789)",47.45,2003-09-01 00:00:00
>>>>>"KKK","Product Two (DGT6789)",44.75,2003-07-14 00:00:00
>>>>>"ZZZ","Product Two (DGT6704)",54,2003-01-13 00:00:00
>>>>>"AAA","Product Two (DGT6704)",1000,2003-08-30 00:00:00
>>>>>"BBB","Product Two (DGT6704)",45.5,2003-07-22 00:00:00
>>>>>"BBB","Product Two (DGT6704)",46.1,2003-08-04 00:00:00
>>>>>"BBB","Product Two (DGT6704)",46.1,2003-08-16 00:00:00
>>>>>"BBB","Product Two (DGT6704)",44.75,2003-08-27 00:00:00
>>>>>"CCC","Product Two (DGT6704)",1000,2003-08-30 00:00:00
>>>>>"DDD","Product Two (DGT6704)",46.25,2003-05-08 00:00:00
>>>>>"EEE","Product Two (DGT6704)",1000,2003-08-30 00:00:00
>>>>>"FFF","Product Two (DGT6704)",1000,2003-08-30 00:00:00
>>>>>"GGG","Product Two (DGT6704)",1000,2003-09-01 00:00:00
>>>>>"HHH","Product Two (DGT6704)",46.1,2003-08-04 00:00:00
>>>>>"III","Product Two (DGT6704)",1000,2003-01-13 00:00:00
>>>>>"JJJ","Product Two (DGT6704)",47.45,2003-08-04 00:00:00
>>>>>"JJJ","Product Two (DGT6704)",47.45,2003-09-01 00:00:00
>>>>>"KKK","Product Two (DGT6704)",44.75,2003-07-14 00:00:00
>>>>>"ZZZ","Product Three (QAT6785)",52,2003-01-13 00:00:00
>>>>>"AAA","Product Three (QAT6785)",1000,2003-08-30 00:00:00
>>>>>"BBB","Product Three (QAT6785)",45.5,2003-07-22 00:00:00
>>>>>"BBB","Product Three (QAT6785)",532.25,2003-07-29 00:00:00
>>>>>"BBB","Product Three (QAT6785)",1000,2003-07-30 00:00:00
>>>>>"BBB","Product Three (QAT6785)",1000,2003-08-04 00:00:00
>>>>>"BBB","Product Three (QAT6785)",46.1,2003-08-16 00:00:00
>>>>>"BBB","Product Three (QAT6785)",44.75,2003-08-27 00:00:00
>>>>>"CCC","Product Three (QAT6785)",42.65,2003-08-31 00:00:00
>>>>>"DDD","Product Three (QAT6785)",46.25,2003-05-08 00:00:00
>>>>>"EEE","Product Three (QAT6785)",1000,2003-08-30 00:00:00
>>>>>"FFF","Product Three (QAT6785)",1000,2003-08-30 00:00:00
>>>>>"GGG","Product Three (QAT6785)",1000,2003-09-01 00:00:00
>>>>>"HHH","Product Three (QAT6785)",46.1,2003-08-04 00:00:00
>>>>>"III","Product Three (QAT6785)",1000,2003-01-13 00:00:00
>>>>>"JJJ","Product Three (QAT6785)",40.45,2003-08-04 00:00:00
>>>>>"JJJ","Product Three (QAT6785)",40.45,2003-09-01 00:00:00
>>>>>"KKK","Product Three (QAT6785)",44.75,2003-07-14 00:00:00
>>>>>"ZZZ","Product Three (QAT556)",50.23,2003-01-13 00:00:00
>>>>>"AAA","Product Three (QAT556)",1000,2003-08-30 00:00:00
>>>>>"BBB","Product Three (QAT556)",44.75,2003-07-22 00:00:00
>>>>>"BBB","Product Three (QAT556)",532.25,2003-07-29 00:00:00
>>>>>"BBB","Product Three (QAT556)",1000,2003-07-30 00:00:00
>>>>>"BBB","Product Three (QAT556)",1000,2003-08-03 00:00:00
>>>>>"BBB","Product Three (QAT556)",1000,2003-08-04 00:00:00
>>>>>"BBB","Product Three (QAT556)",44.75,2003-08-16 00:00:00
>>>>>"BBB","Product Three (QAT556)",44.75,2003-08-27 00:00:00
>>>>>"CCC","Product Three (QAT556)",1000,2003-08-30 00:00:00
>>>>>"DDD","Product Three (QAT556)",46.25,2003-05-08 00:00:00
>>>>>"EEE","Product Three (QAT556)",1000,2003-08-30 00:00:00
>>>>>"FFF","Product Three (QAT556)",1000,2003-08-30 00:00:00
>>>>>"GGG","Product Three (QAT556)",1000,2003-09-01 00:00:00
>>>>>"HHH","Product Three (QAT556)",46.1,2003-08-04 00:00:00
>>>>>"III","Product Three (QAT556)",1000,2003-01-13 00:00:00
>>>>>"JJJ","Product Three (QAT556)",40.45,2003-08-04 00:00:00
>>>>>"JJJ","Product Three (QAT556)",40.45,2003-09-01 00:00:00
>>>>>"KKK","Product Three (QAT556)",44.75,2003-07-14 00:00:00
>>>>>"JJJ","Product Three (QAT556)",40.15,2003-08-01 00:00:00