Parameter advice please

Question

Parameter advice please

faulknerwilliam2

SSCrazy

Points: 2299
More actions
December 1, 2015 at 4:05 am

#309841

Hello
I have a simple script that brings through, for a certain product, the date it was ordered and the date the order was completed.
On a graph, I am tring to show the number ordered between a certain date and the number completed between the same date.
The problem I face is that in the script I have used:
AND job.orderdate BETWEEN (@odate1) AND (odate2)
This is absolutely fine for displaying new orders. However, when I use the same parameter for completed orders, it obviously only shows the jobs that were completed between the above parameter.
For example, if I put in November's date parameters, it will show me:
50 orders
25 jobs completed
What it will not show me is if a job completed in November where the order did not begin in November. So I may have an instance where 15 other jobs were completed in November, but because their start date was say October, they will not come through.
Can anyone suggest a way of bringing this data through? All I am trying to do is present a graph that shows: new orders and completed orders.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 22598 More actions · Answer 1

faulknerwilliam2 (12/1/2015)
Hello
I have a simple script that brings through, for a certain product, the date it was ordered and the date the order was completed.
On a graph, I am tring to show the number ordered between a certain date and the number completed between the same date.
The problem I face is that in the script I have used:
AND job.orderdate BETWEEN (@odate1) AND (odate2)
This is absolutely fine for displaying new orders. However, when I use the same parameter for completed orders, it obviously only shows the jobs that were completed between the above parameter.
For example, if I put in November's date parameters, it will show me:
50 orders
25 jobs completed
What it will not show me is if a job completed in November where the order did not begin in November. So I may have an instance where 15 other jobs were completed in November, but because their start date was say October, they will not come through.
Can anyone suggest a way of bringing this data through? All I am trying to do is present a graph that shows: new orders and completed orders.
Thanks

Do you have a completeddate column anywhere? You'll need to change your script to include that somehow to bring through jobs that were completed in the range.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

faulknerwilliam2 SSCrazy Points: 2299 More actions · Answer 2

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!

Neil Burton SSC-Insane Points: 22598 More actions · Answer 3

Can you post your full query and some sample data please? It will make it much easier to come up with a solution.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Thom A SSC Guru Points: 99009 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

You don't need OR. Please see the following article.

http://qa.sqlservercentral.com/articles/T-SQL/105968/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Thom A SSC Guru Points: 99009 More actions · Answer 6

Jeff Moden (12/1/2015)
You don't need OR. Please see the following article.
http://qa.sqlservercentral.com/articles/T-SQL/105968/

Mmm, you're right, I should really be using a case statement instead to be honest, only one trip to the table then:

WHERE ...

AND (case @kpi when 'Placed' then job.orderdate when 'Completed' then job.completeddate BETWEEN (@odate1) AND (odate2) end)

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk