SQL Query to return records with overlapping fields

[fperri]

I have a table that I need to pull all the records that have overlapping values in two fields (Min_LTV and Max_LTV) such as;

Lender Product Min_LTV Max_LTV
Lender1 Product1 70 80
Lender2 Product1 75 80


This is what I have so far but it is returning records that arn't overlapping.
[Code=sql]
SELECT a.ID, a.LENDER, a.ADJUSTMENT, a.MIN_FICO, a.MAX_FICO, a.MIN_LOAN, a.MAX_LOAN, a.MIN_LTV, a.MAX_LTV, a.PRICE_ADD, a.RATE_ADD, a.MARGIN_ADD, a.LOAN_TYPE, a.PRODUCT_TYPE, a.REGION, a.Occupancy_typ e, a.Unit_type, a.Documentation _type, a.Property_type , a.Transaction_t ype, a.LOCK_PERIOD, a.MIN_DTI, a.MAX_DTI, a.SPECIAL
FROM BASE_ADJUSTMENT S AS a, BASE_ADJUSTMENT S AS b
WHERE (((a.LENDER)=[b].[LENDER]) AND ((a.ADJUSTMENT) =[b].[ADJUSTMENT]) AND ((a.MIN_LTV) Between [b].[MIN_LTV] And [b].[MAX_LTV]) AND ((a.LOAN_TYPE)=[b].[LOAN_TYPE]) AND ((a.PRODUCT_TYP E)=[b].[PRODUCT_TYPE])) AND (a.MIN_LTV <> b.MIN_LTV);
[/Code]
Any ideas what I'm doing wrong?

[Rabbit]

Other than the inefficiency, I don't see anything wrong with the SQL. Can you give an example of the data input and the incorrect output?

[fperri]

I was actally able to get it to work, but your right it is not very efficient. It takes too long to run. I also have various fields throughout the table that have null values in them, so in order to get it to catch all overlapping records I'm going to have to do a query on each possible combination of fields. This really sucks, I'm begining to think that it is not worth it.

[Rabbit]

You should join the table on those fields that need to be equal.

[fperri]

So I joined the fields, and I have gotten 3 out of the 4 field sets that I need to look for overlaps in working but the last one is returning results that are not really overlapping.

Here is my query;

SELECT a.ID, b.ID, a.LENDER, a.ADJUSTMENT, a.MINFICO, a.MAXFICO, a.MINLOAN, a.MAXLOAN, a.MINLTV, a.MAXLTV, a.MINCLTV, a.MAXCLTV, a.PRICE_ADD, a.RATE_ADD, a.MARGIN_ADD, a.LOAN_TYPE, a.PRODUCT_TYPE, a.REGION, a.UNIT_TYPE, a.OCCUPANCY, a.DOCUMENT_TYPE, a.PROPERTY_TYPE, a.TRANSACTION_TYPE, a.LOCK_PERIOD, a.CLTV_TYPE, a.MIN_DTI, a.MAX_DTI, a.SPECIAL, ([a].[MINLOAN])+([a].[MAXLOAN]) AS COMBINED
FROM BASE_ADJUSTMENTS_NO_NULLS AS a INNER JOIN BASE_ADJUSTMENTS_NO_NULLS AS b ON (a.SPECIAL = b.SPECIAL) AND (a.MAX_DTI = b.MAX_DTI) AND (a.MIN_DTI = b.MIN_DTI) AND (a.CLTV_TYPE = b.CLTV_TYPE) AND (a.LOCK_PERIOD = b.LOCK_PERIOD) AND (a.TRANSACTION_TYPE = b.TRANSACTION_TYPE) AND (a.PROPERTY_TYPE = b.PROPERTY_TYPE) AND (a.DOCUMENT_TYPE = b.DOCUMENT_TYPE) AND (a.OCCUPANCY = b.OCCUPANCY) AND (a.UNIT_TYPE = b.UNIT_TYPE) AND (a.REGION = b.REGION) AND (a.PRODUCT_TYPE = b.PRODUCT_TYPE) AND (a.LOAN_TYPE = b.LOAN_TYPE) AND (a.MAXCLTV = b.MAXCLTV) AND (a.MINCLTV = b.MINCLTV) AND (a.MAXLTV = b.MAXLTV) AND (a.MINLTV = b.MINLTV) AND (a.MAXFICO = b.MAXFICO) AND (a.MINFICO = b.MINFICO) AND (a.ADJUSTMENT = b.ADJUSTMENT) AND (a.LENDER = b.LENDER)
WHERE (((a.MINLOAN)>[b].[MINLOAN] And (a.MINLOAN)<[b].[MAXLOAN])) OR (((a.MAXLOAN)>[b].[MINLOAN] And (a.MAXLOAN)<[b].[MAXLOAN])) OR (((a.MINLOAN)=[b].[MAXLOAN])) OR (((a.MAXLOAN)=[b].[MINLOAN]))
GROUP BY a.ID, b.ID, a.LENDER, a.ADJUSTMENT, a.MINFICO, a.MAXFICO, a.MINLOAN, a.MAXLOAN, a.MINLTV, a.MAXLTV, a.MINCLTV, a.MAXCLTV, a.PRICE_ADD, a.RATE_ADD, a.MARGIN_ADD, a.LOAN_TYPE, a.PRODUCT_TYPE, a.REGION, a.UNIT_TYPE, a.OCCUPANCY, a.DOCUMENT_TYPE, a.PROPERTY_TYPE, a.TRANSACTION_TYPE, a.LOCK_PERIOD, a.CLTV_TYPE, a.MIN_DTI, a.MAX_DTI, a.SPECIAL
HAVING (((([a].[MINLOAN])+([a].[MAXLOAN]))<>0))
ORDER BY a.ID;

Here are the records it is pulling that are not really overlapping on the MINLOAN and MAXLOAN fields;

1st Record
MINLOAN = 417001
MAXLOAN = 1500000

2nd Record
MINLOAN = 3000001
MAXLOAN = 5000000

Neither the MINLOAN or the MAXLOAN of either record falls within the range of the other record so I don't understand why it is pulling these. These fields in the table are set as Single -> Auto (decimal points)

I know this is a really long query, but do you have any ideas why it is not working? I'm pretty much using the exact same query for other fields in the table (MIN & MAX FICO, MIN & MAX LTV, etc.) and it is working, but it is not working on this one.

I thought maybe the values were being stored differently than they were being displayed so I did a query on the table to show the absolute values and they were the same.......I'm so lost.

[Rabbit]

Hmm, that's weird, it shouldn't return those records. Have you tried using BETWEEN instead?

[fperri]

Yeah, I tried the between and got the same results.

I ended up just creating two calculated fields to see if it was overlapping instead of the way I was going about it;

Expr1: a.MINLOAN - b.MINLOAN
Criteria: >=0

Expr2: a.MINLOAN - b.MAXLOAN
Criteria: <=0

This ended up working great. I doublechecked all the results and they were correct.

[Rabbit]

Glad you got it working, but still weird that it was a problem. The only ways I could see it happening is if the data was corrupted or if it was treating the fields as text.

[fperri]

Yeah, it is strange. I don't know if I'll be able to figure out why it was doing that. Especiall since it was working on other fields.

Thank you for letting me bounce it all off of you though. :)

[Rabbit]

Not a problem, good luck.