Selecting the last from any year

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an AS 400 ODBC file with multiple sku's and prices with various dates
stored as YYYYMMDD
I want to select ALL sku's but only the "Last" price within any given sku
PER YEAR
Thanks in advance for any and all help
example follows:

sku date price Select
1 20071001 1.50 Yes
1 20070301 1.25 No
1 20061001 1.00 Yes
1 20050601 0.75 Yes
1 20050201 0.70 no
2 20071001 1.50 Yes
2 20070301 1.25 no
2 20061001 1.00 Yes
2 20060301 0.75 no
2 20050201 0.70 Yes
2 20050101 0.65 no
2 20040201 0.60 Yes
 
maybe:

SELECT p.sku,
p.price_date,
p.price,
COUNT(* ) AS rank
FROM ProductPrices AS p
INNER JOIN ProductPrices AS p1
ON p.sku = p1.sku
AND YEAR(p.price_date) = YEAR(p1.price_date)
AND p.price_date <= p1.price_date
GROUP BY p.sku,p.price_date,p.price
HAVING COUNT(* ) = 1;
 
Not quite... I see where you are going but do not understand the COUNT(* ) ?
Thanks cww
 
The issue is that a string such as 20071001 is not recognized as a date by
Access, so perhaps a conversion query is needed first.

Query: Convert Price Dates

SELECT ProductPrices.sku,
MID([price_date],1,4) + "-" + MID([price_date],5,2) + "-" +
MID([price_date],7,2) AS PriceDate,
ProductPrices.price
FROM ProductPrices;

SELECT p.sku,
p.pricedate,
p.price
FROM [Convert Price Dates] AS p
INNER JOIN [Convert Price Dates] AS p1
ON (p.pricedate <= p1.pricedate)
AND (YEAR(p.pricedate) = YEAR(p1.pricedate))
AND (p.sku = p1.sku)
GROUP BY p.sku,p.pricedate,p.price
HAVING COUNT(* ) = 1;

or:

SELECT a.sku,
a.pricedate,
a.price
FROM [Convert Price Dates] AS a
WHERE a.pricedate IN (SELECT TOP 1 b.pricedate
FROM [Convert Price Dates] AS b
WHERE b.sku = a.sku
AND YEAR(b.pricedate) = YEAR(a.pricedate)
ORDER BY b.pricedate DESC);
 
Back
Top