T-SQL Help

Question

T-SQL Help

VA123

SSCarpal Tunnel

Points: 4272
More actions
April 6, 2017 at 11:25 am

#336789

I'm developing a report in SSRS and using a stored procedure as the dataset.
I have data in a table like this:

...and a drop-down of item types that are passed to the stored procedure as parameters.

How can I write a 'Select' statement 'Where' clause so that only records having selected item types (Steel and Wood) whose value is > 0 are returned?
That is, I want only RecordId A2 returned since only Steel and Wood are selected in the drop-down AND BOTH Steel and Wood have values > 0. So, I want this result:

Thank you.

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

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

Select from table where Steel > 0 and Wood > 0
But I'm guessing your question is more complex than that?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sgmunson SSC Guru Points: 110640 More actions · Answer 2

Without knowing what your stored procedure does, that's an almost impossible to answer question. There might well be a dozen different ways to get to the same result, but the nature of your source data is quite likely to suggest the best approach. Please post sample data for the tables involved in your procedure, plus the procedure itself, and we'll take a closer look and see what might work. Be sure to post "CONSUMABLE" data, meaning table creation scripts as well as INSERT scripts for sample data, as opposed to pictures or attached spreadsheets.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 3

Yes I think more complex. The item types are passed to the stored procedures as a parameter string like this: 'Plastic,Steel,Tree,Wood' I insert the comma delimited string into a temp table like this:
ItemType
Plastic
Steel
Tree
Wood

In my example in my first post only Steel and Wood are selected and passed so, 'Steel,Wood'.

So I have two tables: a data table (RecordId and item type values) and an item type table (above)

Phil Parkin SSC Guru Points: 246965 More actions · Answer 4

VA123 - Thursday, April 6, 2017 11:42 AM
Yes I think more complex. The item types are passed to the stored procedures as a parameter string like this: 'Plastic,Steel,Tree,Wood' I insert the comma delimited string into a temp table like this:
ItemType
Plastic
Steel
Tree
Wood
In my example in my first post only Steel and Wood are selected and passed so, 'Steel,Wood'.
So I have two tables: a data table (RecordId and item type values) and an item type table (above)

I can see a couple of options:
1) Create a query to normalise your data (RecordId, Material, Value) and you can fashion a join between that and your temp table which should work, or
2) Write some dynamic SQL.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sgmunson SSC Guru Points: 110640 More actions · Answer 5

When you comply with the requested information, I can help you. You were asked for "CONSUMABLE" data. Please provide.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 6

Phil Parkin - Thursday, April 6, 2017 11:59 AM
VA123 - Thursday, April 6, 2017 11:42 AM
Yes I think more complex. The item types are passed to the stored procedures as a parameter string like this: 'Plastic,Steel,Tree,Wood' I insert the comma delimited string into a temp table like this:
ItemType
Plastic
Steel
Tree
Wood
In my example in my first post only Steel and Wood are selected and passed so, 'Steel,Wood'.
So I have two tables: a data table (RecordId and item type values) and an item type table (above)
I can see a couple of options:
1) Create a query to normalise your data (RecordId, Material, Value) and you can fashion a join between that and your temp table which should work, or
2) Write some dynamic SQL.

Thank you Phil. Yes I need to rethink this.