Problem: Selecting the most appropriate row based upon the row's effective date (a user defined field of the row) verses the current date and time.
Platform: Microsoft SQL Server 2000
In a table of products, each record is a product, or a version of a product. These products or versions of products are "Effective Dated", meaning, a row is activated by the date it becomes effective by (do not be confused by an active row, and an active product - active row is the currently effective row, an active product is a flag set by the user as to define whether the product is active when it's effective date is reached). Example:
Product 1 (version 1)
Effective Date: 6/1/2003 12:00:00 AM
Product 1 (version 2)
Effective Date: 7/11/2003 12:00:00 AM
Product 1 (version 3)
Effective Date: 12/25/2003 12:00:00 AM
In the above case, this SINGLE product has four records in the product table representing different versions. Product 1 version 1 has expired since Product 1 version 2 is effective today (7/11). Product 1 version 3 and version 4, however, have not come about yet due to their effective dating in the future. Product versions 1 through 3 are all active, and the product is no longer available after 1/1/2004, when version 4 becomes effective and the status changes to "Inactive".
The problem I'm experiencing with my SQL is that I cannot differentiate between the version effective currently and the future versions.
My current SQL looks as such:
WHERE NOT EXISTS
SELECT P.cProdCode, P.dtEffDate
FROM tblProducts AS P
tblProducts.cProdCode = P.cProdCode AND
tblProducts.dtEffDate < P.dtEffDate
AND bActive = 1
AND cProdCode = 'somecode'
ORDER BY sProdName
In the above SQL, bActive is a flag that declares this product as an active product (verses inactive) and cProdCode is a code that all versions of the Product share. Ex:
The above SQL is effective in ignoring the expired versions of the product, but returns the current and future versions. I've attempted to massage the SQL to ignore the expired and future versions (leaving me with the single, currently active, version).
Ideally, I'd like to do this without having to create flags for the records that deam them as past, present, future, or having to export versions out into an archive table. This should be able to be determined by strictly SQL, but haven't been able to get the data to return as I'd like it..
In the end, the final result would be a single record of the version of the product which is currently effective, and alertnately, the ability to retrieve a set of records listing all of the currently effective active products.
Now, I should mention that I have a version of this SQL that works, but I suppose my ultimate question is "Is this the most efficient and proper way to do this or is there a better way?"
Here is the SQL that does work, but it nags at me with the use of TOP and ORDER BY. It doesn't seem as though this is the proper elegant solution:
SELECT TOP 1 *
FROM tblProducts AS P
FROM tblProducts P2
P2.dtEffDate < GETDATE()
AND P.bActive = 1
AND P.cProdCode = 'somecode'
ORDER BY P.dtEffDAte DESC
If I do not use TOP, it returns every version of a product except the future versions. If I do not use ORDER BY it returns them in an order inconsistant with their effective date. So combined together, the records are sorted most current->least current, then chopped at the top for the most current effective dated product.
Now, this comes into play heavily when we start talking about retrieving the most recent version of all of the products. Sure the above works when getting the most current version of a single product, but (due to the "TOP") does not work properly when attempting to retrieve the most current version of all active products. The above SQL's "TOP" directive is in there to drop off the expired version of the product, and in this case, TOP drops off more then just expired versions of products.. it drops everything except the lucky record at the top of the set.
I apologize for the complexity of the project, but I think you nailed the solution. I've done some cursory testing, and just looking over the SQL, it looks right on. Tonight I'll take some time to do some more testing, but it looks good. I hadn't even thought of using the MAX function. Thanks for your assistance, I appreciate you taking the time to go through all my details (even if they were a bit long and confusing) and coming up with a solid solution.