@owner int = 0,
@date_start smalldatetime = '1/1/2008',
@date_end smalldatetime = '1/1/2050'

SELECT DISTINCT o.id as 'order_id',
	 o.value as 'order_value',
	 o.date as 'order_date',
	 w.id as 'owner_id',
	 w.name as 'owner_name',
	 (SELECT sum(price) FROM items WHERE (@owner = 0 or (@owner = owner_id))) as 'sum_item_price',
	 (SELECT count(id) FROM items WHERE (@owner = 0 or (@owner = owner_id))) as 'count_items'
	FROM items i
		inner join orders o 
			on o.id = i.order_id
		inner join owner w
			on w.id = i.owner_id
WHERE (@owner = 0 or (@owner = i.owner_id))
AND (o.date >= convert(smalldatetime, @date_start)
     and o.date <= dateadd(day, 1, convert(smalldatetime, @date_end))
    )
ORDER BY o.date DESC

quote:

---

Originally posted by deniseaddy

Here is a basic version of what I'm trying to achieve but it doesn't work.

@owner int = 0,
@date_start smalldatetime = '1/1/2008',
@date_end smalldatetime = '1/1/2050'

SELECT o.id as 'order_id',
	 o.value as 'order_value',
	 o.date as 'order_date',
	 w.id as 'owner_id',
	 w.name as 'owner_name',
	 sum(i.price) as 'sum_item_price',
	 count(i.id)  as 'count_items'
	FROM items i
		inner join orders o 
			on o.id = i.order_id
		inner join owner w
			on w.id = i.owner_id
WHERE (@owner = 0 or (@owner = i.owner_id))
AND (o.date >= @date_start
     and o.date <= dateadd(day, 1,@date_end)
    )
Group by o.id,o.value ,
	 o.date,
	 w.id,
	 w.name
ORDER BY o.date DESC

It also means repeating the same where conditions in the sum and count sub queries and there must be a better way of achieving that with a single conditional query for the whole statement.

Thanks.

---