Using LIKE syntax in nested query

[benchpolo]

[B]Item A[/B]

Code
----------
250
357.2
362.0
366.41
648.0

[B]Item B[/B]

select cm.Membid
,cm.ADMDIAG
from dbo.RVS_CLAIM_MASTERS cm
where cm.membid in (select distinct memberid from CLINICAL.dbo.DM_CDC_Tracking_Excel)
and cm.ADMDIAG in (select code from #diag a inner join 
dbo.RVS_CLAIM_MASTERS b on a.code like b.admdiag +'%')

Scenario: Item A contains a list of code that I need to pull from the script in Item B.

Problem: I'm not able to figure out the code to pull a range of values based on the Item A codes using Item B script. For example, if i'm searching for 250 the source database has values of 250, 250.00. 250.1 i should be able the range value of 250. Make sense? I tried to search the JOIN with LIKE and i'm getting zero results.

Thanks.

[ck9663]

Can you post some sample dbo.RVS_CLAIM_M ASTERS.ADMDIAG?


~~ CK

[Rabbit]

If we're talking about numeric data and not text data, then use >= 250 and < 251

[benchpolo]

data looks like

AdmDiag
250.80
250.72
250.02
250.00
250.60
250.50
250.12
250.70
250.42

[ck9663]

Follow Rabbit's suggestion.

Happy Coding!!!


~~ CK

[benchpolo]

but it is a range. Table A contains code that are defined in the document, but Table B contains a range of code. For example, Table A contains Diabetes code 250.00, but Diabetes code is a range from 250.00-250.8; therefore, I a code that pulls the LIKE code from Table A to Table B, and the value is a mixture of numeric and text. but in this particular example it is pure numeric.

[Rabbit]

Yes, we know it's a numeric range. Use my suggestion.

[benchpolo]

but that is hard coding i want to do it using field.

select diag
from diagtable
where diag in (select diag from #temp)

[ck9663]

You can actually use "<=" and "=>" in a JOIN clause making it a range JOIN not an exact "=" JOIN.

Happy Coding!!!


~~ CK

[Rabbit]

My example may be hard coded but where ever you see a hardcoded value, you can use a field. Alternatively, you can use the floor function.