Greater Than/Less Than joins

[briangriffin]

I have a table of surgical procedures:

Account [tab] ProcedureCode
999 [tab][tab][tab]X1235

And a table used to cross reference procedures to billing codes:
RangeStart [tab][tab][tab] RangeEnd
X1234 [tab][tab][tab] [tab][tab][tab] X1345
X2345 [tab][tab][tab] [tab][tab][tab] X2467

I need to return all the accounts with ProcedureCodes within the ranges in the table. I thought this would work:

Select
a.Account,
a.ProcedureCode

from SurgicalProcedures a inner join CodeLookup b
on a.ProcedureCode >= b.RangeStart
and a.ProcedureCode <= b.RangeEnd


That doesn't give me a true cartesian product, but the record count is grossly inflated with records where the code is nowhere near the ranges. How can I get the desired result?

TIA

 

[briangriffin]

Also, this is a relatively small dataset so performance is not an issue.

 

[Andrzejek]

It would be nice to have a small sample of your real data, otherwise what I see is something similar to:
[pre]Where a.briangriffin >= b.BRIANGRIFFIN
and a.BrIaNgRiFfIn <= b.bRiAnGrIfFiN[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[briangriffin]

declare @v_procedures table
(AccountNumber varchar(32),
ProcedureCode varchar(12))

insert into @v_procedures
select 'V0-B20160610160142361','02H60KZ'
union
select 'V0-B20160805113907510','02HK4KZ'
union
select 'V0-B20171129172645075','0JH63XZ'
union
select 'V0-B20180311152256514','1234'
union
select 'V0-B20171129172645075','3456'


declare @v_coderanges table
(CodeRangeStart varchar(12),
CodeRangeEnd varchar(12))

insert into @v_coderanges
select '02H60KZ','02H61KZ'
union
select '02HK4KZ','02HK5KZ'
union
select '0JH63XZ','0JH63XZ'


select
a.AccountNumber,
a.ProcedureCode

from @v_procedures a inner join @v_coderanges b
	on a.ProcedureCode >= b.CodeRangeStart
	and a.ProcedureCode <= b.CodeRangeEnd

However, this works exactly as I expected. I'll continue to expand this until I can get it to break.

 

[briangriffin]

If you can import the attached sample data then this query should return records that don't belong:

select
a.AccountNumber,
a.ProcedureCode,
b.CodeRangeStart,
b.CodeRangeEnd

from @v_procedures a inner join @v_coderanges b
	on a.ProcedureCode >= b.CodeRangeStart
	and a.ProcedureCode <= b.CodeRangeEnd

 

[fredericofonseca]

from your query and data what records do you think do not belong to your criteria?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[briangriffin]

Here is a sample of the results I'm getting with production data:

The first four records belong, the last three do not. Also, the first four appear to be duplicates but they are not - that patient had the procedure four different times. Thanks.

 

[fredericofonseca]

well the spreadsheet you supplied didn't have any of that.

In any case your production query is correctly outputing those last 3 records.

3 is higher than 0(zero) and lower than O(letter O) so it is correctly included.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[briangriffin]

I didn't notice that the unwanted values started with 'O' instead of 0. This is incorrect, I was supplied bad data.

At least I know there was nothing wrong with my query syntax... thanks for the help.