Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

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?




0
SidFishes
Asked:
SidFishes
  • 2
1 Solution
 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now