sql - Optimizing Query With Subselect -

I am trying to prepare a sales report that lists each product + total sales in a given month . This is a bit difficult because the prices of products can change throughout the month. For example:

• Between Jan-01 and Jan-15, my company sells 50 widgets for $ 10 each
• Jan-15 and Jan-31

This set up in the database as follows:

• Jan = (50 * 10) + (50 * 15) = Total sales of widgets for $ 1250

Featured:

 Sales table Sales_ID Productivity Sales_ Date 1 1 2009-01-01 2 1 2009-01-01 3 1 2009-01-02 ... 50 1 2009- 01-15 51 1 200 9 -01-16 52 1 2009-01-17 ... 100 1 2009-01-31 Price Table Product_id Sales_ Date Price 1 2009-01-01 10.00 1 2009-01-16 15.00 

When price is defined in price prices, then the given salesdate applies to all the products sold with the given product provided by the advance.

Actually, I'm looking for a query that returns the data as follows:

desired product Sale_ID ProductID sales_ Date value 1 1 2009-01-01 10.00 2 1 2009-01 -01 10.00 3 1 2009-01-02 10.00 ... 50 1 2009-01-15 10.00 51 1 2009-01-16 15.00 52 1 2009-01-17 15.00 ... 100 1 2009-01-31 15.00 < / Pre>

I have the following questions:

SELECT Sale_ID, Product_ID, Sales_ Date, (Please select the top 1 value from the price where the prices are. Product_id = sales.product_id and prices.Selling_Date & Lt; Sales from SaleData Order ordered . Cell detect Diissi)

It works, but what is nested sub-select more efficient questions?

And before you say that the "price" will be easy to include in the sales table, I should mention that the schema has been placed by another vendor and I can change it I'm unable. And if it matters, then I am using SQL Server 2000.

If you create a start and end date, or a view that includes start and end dates (you You can make your query very simple) (if you are certain that the range is not overlapped)

  SELECT SEAL_ID, Product_ID, Sale_Date, sales From Sales Price to Sales_date & gt; StartDate and Sale_Date & lt; = EndDate - Not careful to use between them Both ends include  

Note:

A technique with these lines will allow you to do this with a view . Note, if you need to index the view, then it will wake around quite a bit ..

  get table vs. t (d datetime) T values ​​(getdate ()) Insert T values ​​(Getdate) + 1) Insert T values ​​(getdate (+2) Select the view select = isnull (maximum (T2D), '1975-1-1') Look at the end, end = t1 dt t1 join the left t1 t1.d & gt; Select t1.d by T2.d group * myview starts with end ----------------------- ------------- ---------------------------------------------- 2009-01- 28 11: 12: 57.383 2009-01-29 11: 12: 57.383