I wasn't sure where exactly to create this question since the ultimate product is going into an SSRS report, but I'm having trouble writing the SQL query. I'm hoping someone out there will have some suggestions on how to approach this. Here's the situation:
I have an SSRS report with a multi-selection parameter which is querying on a field that has concatenated 1 - 2-character values separated by a comma. The sample data looks something like this:
, mobility VARCHAR(15)
INSERT INTO @table
DECLARE @MOBILITYAID VARCHAR(20) = 'A,WA'
WHERE MOBILITY LIKE '%' + @MOBILITYAID + '%'
I'd like for the query to pull up all rides who have either A or WA codes, which would be 121, 223, 445,667 and 778.
This is going to be used in an SSRS report which allows for multiple values to be selected. Because of this, the user can select from a list of these two-character codes in which to search for all the clients who have any combination of those selected codes. The problem arises when the user selects "A" as one of the codes. There are many codes which contain the letter A, but there is also a code in and of itself the letter 'A' (and BTW always happens to be located at the beginning of the string if it's listed in mobility field). The problem with using a wildcard here is that when A is the parameter, all clients who have an A in their mobility string come up in the results. I want to be able to both search for records which have the A code, as well as whatever other codes they've selected from the list. Almost like search for "where mobility in ('A','A,') and mobility in ('%' + @mobility + '%') ---which I know is wrong, but I'm trying to articulate what I'm thinking here.
The original author of the SSRS report added this code below in the SSRS dataset to account for these codes, but it doesn't properly pick up the "A" code by itself so it's not very functional for the users who are specifically looking for those clients who have the "A" code (as well as any other single codes):
="SELECT * FROM Clients WHERE InActive = @Inactive " &
" AND (mobaids like '%" & Join(Parameters!mobaid.Value,"%' OR mobaids LIKE '%") & "%')" &
" order by lastname"
Is there any way that I can actually allow for this? The users need to be able to select each individual code they want from the list and find any client who has any combination of those selected codes, accounting for those codes which are single letters which also may show up as part of other codes.
(Hopefully I've explained this well enough).
Looking for any suggestions.
Thanks so much