;WITH cte (prefix,suffix,leftpart,rightpart,descrip) AS (

SELECT CASE WHEN LEFT(range2,1) LIKE '[a-zA-Z]' THEN LEFT(range2,1) ELSE '' END,

CASE WHEN RIGHT(range2,1) LIKE '[a-zA-Z]' THEN RIGHT(range2,1) ELSE '' END,

LEFT(range2,CHARINDEX('-',range2)-1),

SUBSTRING(range2,CHARINDEX('-',range2)+1,255),

descrip

FROM #temp),

cte2 (prefix,suffix,startno,endno,descrip) AS (

SELECT prefix,suffix,

CAST(REPLACE(REPLACE(leftpart,prefix,''),suffix,'') as...

Are you able to provide am example of the output as it is now and what you want it to look like?

Obfuscate the data if it is sensitive.

It was not intended as a criticism :blush:

See links in Lynn's signature for information...

Yep Luis always gives good answers 😉

Nice one Luis, especially from sparse details 😀

FOTFL :w00t:

Nice 😉

caippers (4/29/2014)

---

That is what you asked for.

What is the expect result...

I noticed that but declined to comment on the...

Meh! use octal 😛

SELECT TOP 1 ID

FROM

ORDER BY Rank ASC

What about ties (ie two PK with the same Rank)

SSRS 😛

Sorry my bad it is incompatible data types, try converting to double or decimal

=SUM(IIF(Fields!col3.Value = "DEL",CDbl(0),CDbl(Fields!col2.Value)))

or

=SUM(IIF(Fields!col3.Value = "DEL",CDec(0),CDec(Fields!col2.Value)))

Expression looks OK

Check all the values in col2, looks like you have non integer value or NULL.

What is generating the report?

Jeff Moden (4/12/2014)

---

I know this is an old post and that the subject is controversial, but a blank means you know it is blank. NULL actually isn't a value....

Either change the sql query

SELECT col1, CASE WHEN col3 = 'DEL' THEN 0 ELSE col2 END AS [col2], col3

FROM

Or use an expression for column 2

=IIF(Fileds!col3.Value = "DEL",0,Fileds!col2.Value)