asked on

Trying to avoid using a cursor to create report results

This gives me the numbers I am looking for

select sum(OD.quantity), P.ProddescEnglish from tblorderdetail OD
inner join tblproducts P on P.productcode = od.productcode
group by P.ProddescEnglish

4532 FooBars
234 GooBars

but I'm looking for dates within a range (from tblOrders) so If I try this

select sum(OD.quantity), P.ProddescEnglish from tblorderdetail OD
inner join tblorders o on o.orderid = od.orderid
inner join tblproducts P on P.productcode = od.productcode
group by P.ProddescEnglish, o.orderdate
having o.orderdate between @startdate and @enddate

I get items grouped on order date

1 FooBar
2 GooBars
1 FooBar

I do have it set up with a cursor and it works but it's ugly... is there a cleaner way?

ASKER CERTIFIED SOLUTION

folderol

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SidFishes

ASKER

thanks ...just a small correction by adding the CONVERT

for the paq db

I also added an If/Else to handle a single day since the statement between '10/10/2007' and '10/10/2007' yeilds nothing

if @startdate = @enddate
BEGIN
      select sum(OD.quantity), P.ProddescEnglish from tblorderdetail OD
      inner join tblorders o on o.orderid = od.orderid
      and CONVERT(varchar(10), OrderDate, 101) = @startdate
      inner join tblproducts P on P.productcode = od.productcode
      group by P.ProddescEnglish

END
ELSE
BEGIN
      select sum(OD.quantity), P.ProddescEnglish from tblorderdetail OD
      inner join tblorders o on o.orderid = od.orderid
      and CONVERT(varchar(10), OrderDate, 101) between @startdate and @enddate
      inner join tblproducts P on P.productcode = od.productcode
      group by P.ProddescEnglish

END

folderol

If Orderdate is datetime with a time portion, then

cast(datediff(day,0,Orderdate) as datetime) between @startdate and @enddate

will work in all instances, as
where '10/1/2007' between '10/1/2007' and '10/1/2007'
is true.