faulknerwilliam2 (12/1/2015)
Thanks for getting back.I have tried:
WHERE ...
AND ((job.orderdate BETWEEN (@odate1) AND (odate2))
OR (job.completeddate BETWEEN (@odate1) AND (odate2)))
Which nearly works in that it brings through the correct completed total. However the order total is now skewed by any orders that began before the parameter dates, yet are now included because the job completed within the parameter date!
If you're looking for order placed between or orders completed between, I would suggest having another parameter, for example @kpi which can be either values "Placed" or "Completed".
YOu can then have your SQL as
WHERE ...
AND ((job.orderdate BETWEEN (@odate1) AND (odate2) and @kpi = 'Placed')
OR (job.completeddate BETWEEN (@odate1) AND (odate2) and @kpi = 'Completed'))
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk