I have the following table and I need to select the rows for a specific
SiteCode, ProductCode. But I only need the rows with the current price. ie
MAX(EffectiveDate) for dates <= NOW()

SELECT * FROM Product2PriceGroup
WHERE SiteCode = '24'
AND ProductCode = '8'
AND EffectiveDate = What can I put here to select only rows with the current
price?

CREATE TABLE "DBA"."Product2PriceGroup" (
"SiteCode" CHAR(10) NOT NULL,
"ProductCode" CHAR(10) NOT NULL,
"PriceGroupCode" CHAR(10) NOT NULL,
"PriceType" CHAR(1) NOT NULL DEFAULT 'T'
CHECK(@COLUMN IN ('T','M','P','L','Q','Y','C')),
"EffectiveDate" DATE NOT NULL,
"Price" DECIMAL(11,4) NOT NULL,
"MinimumCharge" DECIMAL(11,4) NOT NULL,
PRIMARY KEY CLUSTERED ("SiteCode", "ProductCode", "PriceGroupCode",
"PriceType", "EffectiveDate")
);


TIA

Eddie Sizemore

In article <3fdde361$1@forums-1-dub>, esizemorenospam (AT) qx (DOT) net says...
How about:

.... AND EffectiveDate = (select max(effectivedate) from
Product2PriceGroup where SiteCode= '24' and ProductCode = '8')

Thanks, the following ended up working. I just had to extend it to include
all the columns of the pk then limit the future prices.


SELECT * FROM Product2PriceGroup as p1
WHERE SiteCode = '24'
AND ProductCode = '8'
AND EffectiveDate = (select max(effectivedate) from Product2PriceGroup as
p2
where p1.SiteCode = p2.SiteCode
and p1.ProductCode = p2.ProductCode
and p1.PriceGroupCode = p2.PriceGroupCode
and p1.pricetype = p2.pricetype
and p2.effectivedate <= date(now()))


"David Kerber" <ns_dkerber (AT) ns_wraenviro (DOT) com> wrote