SQL - Problem with SQL Query ; group by and join

Question

Suppose I have a table named [ProductPriceHistory] like the following:

HistoryID..ProductCode..EffectDate.... Price.... IsActive...ProductName
1----------11-----------1 Jan 09-------100-------true-------AAA
2----------11-----------1 Feb 09-------150-------true-------AAA
3----------11-----------1 Mar 09-------200-------false------AAA
4----------22-----------1 Jan 09-------150-------true-------BBB
5----------22-----------1 Feb 09-------200-------true-------BBB
6----------22-----------1 Mr 09--------250-------true-------AAA

How can I find the final status of all active products on latest date?

That is, my query will find the row:

6----------22-----------1 Mr 09--------250-------true-------AAA

Answer 1

select * from ProductPriceHistory p1
where EffectDate = 
(select max(EffectDate) from ProductPriceHistory p2
where p1.ProductCode = p2.ProductCode and p2.EffectDate<=getdate())

Answer 2

you're not quite specifying fully -- maybe @tekBlues' query is what you want, or maybe:

SELECT * FROM ProductPriceHistory t1
WHERE t1.EffectDate =
  (SELECT MAX(t2.EffectDate)
   FROM ProductPriceHistory t2
   WHERE t2.IsActive=true)
  AND t1.IsActive=true

Answer 3

Assuming that ProductCode and EffectDate uniquely identify a row, you can do:

SELECT *
  FROM productpricehistory
     , (SELECT productcode
             , MAX(effectdate) effectdate
          FROM productpricehistory
         GROUP BY productcode) maxhistory
 WHERE productpricehistory.productcode = maxhistory.productcode
   AND productpricehistory.effectdate = maxhistory.effectdate
   AND IsActive = TRUE;

If ProductCode and EffectDate don't uniquely identify a row, you'd probably want to use HistoryId instead of EffectDate, if we can assume that HistoryId is unique and that an increasing HistoryId means also an increasing EffectDate.

edit: I realize that I was treating active differently than you were - I was assuming that IsActive applied only to the particular EffectDate, but I see that you inactivate an entire product by setting its "IsActive" to false. I updated accordingly, assuming that you could subsequently activate a product by creating a new row with IsActive = true.

Answer 4

to get the given product code's value use:

DECLARE @ProcuctCode  int
SET @ProductCode=11

SELECT
    h.* 
    FROM ProductPriceHistory h
        INNER JOIN (SELECT
                        ProductCode
                            ,MAX(EffectDate) AS MaxEffectDate
                        FROM ProductPriceHistory
                        WHERE ProductCode=@ProductCode
                            AND IsActive='true'
                        GROUP BY ProductCode
                   ) dt ON h.ProductCode=dt.ProductCode AND h.EffectDate=dt.MaxEffectDate
    WHERE h.ProductCode =@ProductCode

to find all products use:

SELECT
    h.* 
    FROM ProductPriceHistory h
        INNER JOIN (SELECT
                        ProductCode
                            ,MAX(EffectDate) AS MaxEffectDate
                        FROM ProductPriceHistory
                        WHERE IsActive='true'
                        GROUP BY ProductCode
                   ) dt ON h.ProductCode=dt.ProductCode AND h.EffectDate=dt.MaxEffectDate
     ORDER BY h.ProductCode