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