Wild Cards 3

[mttorpy]

Hi
I have 2 values I need to distinguish from

'F-One Time $30,000 Set-Up Fee' and

'F-One Time $3000 Dealer Socket Set-Up Fee'

My problem is that if I SUM on
c.nm_display_name like '%One%Time%$%Set-Up Fee%'

I am capturing both 'dealer socket fees' and 'non described set up fees.

Any ideas on how I can capture them both separately?

Thanks
Matt

 

[Lothario]

Like '%Dealer%' should give you qith dealer socket fees

-L

 

[SQLBill]

Do the $ amounts stay the same? Are you really trying to SUM or just find out how many Dealer Socket Set-up Fees there are and how many regular Set-Up fees there are?

If the latter, you could get a COUNT of everything and a COUNT where it's Dealer Socket, then subtract the two counts.

-SQLBill

 

[mttorpy]

True,
but I still need to capture the 'F-One Time Set-Up Fee'
by itself. As I have it now.....I am incorrcly capturing all set up fees with '%One%Time%Set-Up Fee%'

Any other idea's?

 

[dbomrrsm]

If you want a sum and want to see both of them dont use a where - do a group by on nm_display_name - then both of them will appear seperately.


[blue]DBomrrsm[/blue]

 

[SQLSister]

Personally with only two values I would not use the like keyword. Like, especially withthe wildcard at the beginning means that indexes cannot be used which will slow the query considerably. Just put both in the where clause.
WHERE c.nm_display_name = 'F-One Time $30,000 Set-Up Fee' or c.nm_display_name. = 'F-One Time $3000 Dealer Socket Set-Up Fee'

Questions about posting. See faq183-874

 

[SQLBill]

If possible you should 'fix' your database. Create two columns, one called DealerSocketSetUpFee and the other RegularSetUpFee. Then just enter the dollar amounts into the columns. That way you don't have to do any fancy conversions or costly searches (using %dealer% means SQL Server can't use the index).

-SQLBill

 

[mttorpy]

$ amounts do change thats one of the major problems as they are adding odd amounts all the time, and no need for a count.

 

[SQLBill]

You might also try pulling the values and putting them into a temp table.....using SUBSTRING with a starting point of 13 will get you the value beginning after the $. Then all you need to do is figure out where you want the substring to end. Or if the values are always $30000 and $3000 you can do something like:

SELECT
CASE WHEN PATINDEX('Dealer', col_name) = 18 THEN
SUM(SUBSTRING(col_name, 13, 5))
ELSE
SUM(SUBSTRING(col_name, 13, 5))

I haven't tested that, but give it a try.

-SQLBill

 

[mttorpy]

Thank's I am sure I can get this running w/ PATINDEX.

Never using it before..

THANKS