tags:

views:

149

answers:

4

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
+2  A: 
select * from ProductPriceHistory p1
where EffectDate = 
(select max(EffectDate) from ProductPriceHistory p2
where p1.ProductCode = p2.ProductCode and p2.EffectDate<=getdate())
tekBlues
Did you forget 'IsActive=true'?
You also forgot GROUP BY...!!!
@jmsa - doesn't need group by if all referenced columns (effectivedate) are passed into aggregate function (which it is). This is the typical solution for this kind of problem, but note that if you do not have a unique constraint of EffectDate you will get duplicate rows outputted.
+1  A: 

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
Alex Martelli
A: 

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.

Steve Broberg
+1  A: 

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
KM
Looks good but you need to put an h.IsActive='true' in there somewhere. +1
wcm
@wcm, the derived table contains IsActive='true', and that will then be joined to the proper row, so no need doing anywhere else...
KM
@KM, If you assume that ProductCode and EffectiveDate are unique then you are right. That's probably what JMSA meant so I concede the point.
wcm
@wcm, there isn't much to go in in the question, I just had to make the assumption. If that isn't the case, the OP can easily modify this to suit their needs.
KM