 Re: Need help with query
"netpurpose" <netpurpose@hotmail.com> wrote in message
news:31c57b38.0308291024.6bd2a29c@posting.google.c om...
> 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.google.c om...
> > > 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","EffectiveDat e"
> > > "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
 |