select		orderID

from		[Order]

where		[Order].orderDate between @date1 and @date2
			and [order].storeID = @storeID

--this select statement currently populates an asp record set that is then looped through, 
--performing the below 2 select statements on each value in the recordset


--ORDER BREAKOUT
select		description,
			dvalue,
			quantity,
			sum ((ItemType.dvalue*OrderItem.quantity)) as total 

from		[Order]
			join OrderItem
               on OrderItem.orderID = [Order].orderID
            join ItemType
			   on orderItem.ItemTypeID = OrderItemType.ItemTypeID

where		[Order].orderID = @orderID --orderID comes from first select statement 

group by	description,
			dollarValue,
			quantity

--TOTAL
select		sum ((ItemType.dvalue*OrderItem.quantity)) as total 

from		[Order]
			join OrderItem
               on OrderItem.orderID = [Order].orderID
            join OrderItemType
			   on orderItem.ItemTypeID = OrderItemType.ItemTypeID

where		[Order].orderID = @orderID  --orderID comes from first select statement

quote:

---

Originally posted by tkizer

Rather than loop, which is a performance issue, can you bring back two result sets that contain the rows for all of the ones in the first select statement? This would be a set-based approach and therefore more efficient.

---

select	
	[Order].orderID,
	description,
	dvalue,
	quantity,
	sum ((ItemType.dvalue*OrderItem.quantity)) as total 
from [Order]
join OrderItem
	on OrderItem.orderID = [Order].orderID
join ItemType
	on orderItem.ItemTypeID = OrderItemType.ItemTypeID
where	
	[Order].orderDate between @date1 and @date2 and
	[order].storeID = @storeID
group by
	[Order].orderID,
	description,
	dollarValue,
	quantity

select
	[Order].orderID,
	sum ((ItemType.dvalue*OrderItem.quantity)) as total 
from	[Order]
join OrderItem
	on OrderItem.orderID = [Order].orderID
join OrderItemType
	on orderItem.ItemTypeID = OrderItemType.ItemTypeID
where 
	[Order].orderDate between @date1 and @date2 and
	[order].storeID = @storeID
group by [Order].orderID

--ORDER BREAKOUT
SELECT	
			o.OrderID,
			description,
			dvalue,
			quantity,
			SUM((it.dvalue*oi.quantity)) AS total 

FROM
	[Order] o
INNER JOIN OrderItem oi
	ON oi.orderID = o.orderID
INNER JOIN ItemType it
	ON oi.ItemTypeID = it.ItemTypeID
WHERE 	
	o.orderDate BETWEEN @date1 AND @date2
	AND
	o.storeID = @storeID
GROUP BY 
	o.OrderID,
	description,
	dvalue,
	quantity

--TOTAL
SELECT
	o.orderID,
	SUM((it.dvalue*oi.quantity)) AS total
FROM
	[Order] o
INNER JOIN
	OrderItem oi
	ON
		oi.OrderID = o.OrderID
INNER JOIN
	ItemType it
	ON
		oi.itemTypeID = it.ItemTypeID
WHERE
	o.orderDate BETWEEN @date1 AND @date2
	AND
	o.storeID = @storeID
GROUP BY 
	o.orderID

quote:

---

Originally posted by crugerenator

Actually have a stupid Q... Is there anyway to add breaks when OrderID's change?

Example:

orderId
1
1
1
1
1
1
<br />
<br />
2
2
2
2
2
2
2
<br />
<br />

---