Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Greater Than/Less Than joins

Status
Not open for further replies.

briangriffin

Programmer
Nov 6, 2008
878
US
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

 
Also, this is a relatively small dataset so performance is not an issue.
 
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.
 
Code:
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.
 
If you can import the attached sample data then this query should return records that don't belong:

Code:
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
 
 https://files.engineering.com/getfile.aspx?folder=5faeed0f-634c-4709-9708-191f08639d90&file=Sample_Data.xlsx
Here is a sample of the results I'm getting with production data:

results_wzicje.jpg


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.
 
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

FAQ219-2884
FAQ181-2886
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top