To follow my example, execute the following scripts on an empty database to create 2 tables (you can skip this step and look directly at the results):
Each Sale (master table) is associated with the details table – SalesItems.

create table Sales (
Id int primary key,
Amount money not null,
Date datetime not null,
Comment varchar(128)
)
GO
create table SaleItems (
SaleId int,
ItemId int not null,
Quantity int
)
GO

The Sales table (master table) will have 10’000 random records. The SalesItems table (details table) will have 1 to 10 items for each sale record in the Sales table.

Execute the following scripts to populate both tables and create indexes:

set nocount on
GO
declare @n int, @m int
set @n=10000
while @n>0 begin
insert into Sales select @n,$10.0*(@n%100)+$100.,dateadd(hh,-@n,'20070507'),
'This is sale N '+convert(varchar,@n)
set @m=@n%10
while @m>0 begin
insert into SaleItems select @n,@m,@m+@n
set @m=@m-1
end
set @n=@n-1
end
GO
create index SaleItems_Id on SaleItems (SaleId)
GO
create index Sales_Dates on Sales (Date)
GO

Note that Sales.Date varies from 2006-03-16 08:00 to 2007-05-06 23:00.

Now, we make a first attempt to write a stored procedure that retrieves the maximum quantity of sales for a specified period of time.

create procedure GetMaxQuantity
@p1 datetime, @p2 datetime
as
select max(Quantity) from SaleItems where SaleId in (
select Id from Sales where Date between @p1 and @p2)
GO

Before running the procedure, let’s enable IO Statistics by executing: 

Read more