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?




LVL 36
SidFishesAsked:
Who is Participating?
 
folderolCommented:
select sum(OD.quantity), P.ProddescEnglish from tblorderdetail OD
inner join tblorders o on o.orderid = od.orderid and o.orderdate between @startdate and @enddate
inner join tblproducts P on P.productcode = od.productcode
group by P.ProddescEnglish

Tom
0
 
SidFishesAuthor Commented:
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



0
 
folderolCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.