Add a range of scores to a parameter

Question

Post reply

Add a range of scores to a parameter

fergusoj

Mr or Mrs. 500

Points: 573
More actions
August 17, 2011 at 2:09 pm

#255797

I have a list of scores, anything from 10.00 to 151.80 for example. I need to create a range of 10-19, 20-29, 30-44 and >=45 for the drop-down list. Then that range must pickup any records within the chosen range. I have a working query for the ranges, but the parameter selections won't work.
Can anyone help with this Visual Studio 2008 question? Thanks.

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

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

ken.trock SSCertifiable Points: 5147 More actions · Answer 1

The SQL here would be pretty easy. This sounds more like VS question.

Ken

GSquared SSC Guru Points: 260824 More actions · Answer 2

In SQL 2008, I'm used to doing that by building a dataset in the .NET application or report, and then passing that into the query using a table variable parameter. How are you doing it?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

fergusoj Mr or Mrs. 500 Points: 573 More actions · Answer 3

I used a case statement in a dataset: CASE WHEN PGM_HRS_ERN BETWEEN '10.00' AND '19.00' THEN '10.00-19.00' ....etc. to give me the ranges:>=45.00, 30.00-44.00, 20.00-29.00 and 10.00-19.00.

When I try to run the report and choose a range from the parameter drop-down, I get a message "error converting datatype nvarchar to numeric."

I think I have to convert the nvarchar values in the dataset above to numeric, but don't know how to do this.

Thank you.

GSquared SSC Guru Points: 260824 More actions · Answer 4

What's your query look like? Can you post it?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

fergusoj Mr or Mrs. 500 Points: 573 More actions · Answer 5

This is the dataset query for the hours ranges:

SELECT DISTINCT

CASE WHEN PGM_HRS_ERN BETWEEN '10.00' AND '19.00' THEN '10.00-19.00' WHEN PGM_HRS_ERN BETWEEN '20.00' AND

'29.00' THEN '20.00-29.00' WHEN PGM_HRS_ERN BETWEEN '30.00' AND '44.00' THEN '30.00-44.00' WHEN PGM_HRS_ERN >= '45.00' THEN '>= 45.00' END AS HOURS,

PGM_HRS_ERN

FROM AWARD

WHERE (CASE WHEN PGM_HRS_ERN BETWEEN '10.00' AND '19.00' THEN '10.00-19.00' WHEN PGM_HRS_ERN BETWEEN '20.00' AND

'29.00' THEN '20.00-29.00' WHEN PGM_HRS_ERN BETWEEN '30.00' AND '44.00' THEN '30.00-44.00' WHEN PGM_HRS_ERN >= '45.00' THEN '>= 45.00' END IS NOT NULL)

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 6

fergusoj (8/18/2011)
I used a case statement in a dataset: CASE WHEN PGM_HRS_ERN BETWEEN '10.00' AND '19.00' THEN '10.00-19.00' ....etc. to give me the ranges:>=45.00, 30.00-44.00, 20.00-29.00 and 10.00-19.00.
When I try to run the report and choose a range from the parameter drop-down, I get a message "error converting datatype nvarchar to numeric."
I think I have to convert the nvarchar values in the dataset above to numeric, but don't know how to do this.
Thank you.

Wouldn't just removing the single quotes from the numbers in the BETWEEN fix that for you?

--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)

fergusoj Mr or Mrs. 500 Points: 573 More actions · Answer 7

I will try this solution. And I think I've found another option to try. Thank you for your help.