Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-10 : 07:43:38
|
| Bill writes "I'd like some input on potentially more efficient ways of doing this. It seems overly complicated and I'm hoping there's a better way.In this code, the following represents:---------------------------------------Product = Table with products for saleProductPrice = Table with Prices for the products* Note that the same product purchased at different times may have different prices** Note that the product may have multiple price entries for the same EffDate - newest UpdateDate should be chosen*** Since products could have been priced when the first entry for an EffDate was in effect, those records must be retained[other code here]from Product pInner Join ProductPrice pp On p.a = pp.a and p.b = pp.b and p.c = pp.c and p.PurchaseDate >= pp.EffDate -- Price must have been in effect at this purchase date and pp.EffDate = (Select Max(EffDate) -- Newest price that was in effect for this product From ProductPrice pp2 Where p.a = pp2.a and p.b = pp2.b and p.c = pp2.c and pp2.EffDate <= p.PurchaseDate) and pp.UpdateDate = ( Select Max(UpdateDate) -- Newest price with that effective date From ProductPrice pp2 Where p.a = pp2.a and p.b = pp2.b and p.c = pp2.c and pp2.EffDate = pp.EffDate)" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Newest price in effect for a given purchase date 