Auto join in SQL Query

I've got the following table :
CREATE TABLE M6_CHECK
(
ID_QUOTE VARCHAR(20),
ID_ITEM VARCHAR(20),
BEGIN_DATE DATETIME,
END_DATE DATETIME
)
This table contains the following data :
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q1','I1','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q1','I2','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q1','I3','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q2','I1','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q2','I4','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q3','I2','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q3','I5','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q4','I3','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q4','I6','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q5','I4','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q6','I5','01/12/2000','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q7','I6','01/31/2001','12/31/2001')
I am trying to find a SQL query which would return the following recordset :
ID_ITEM ID_QUOTE_1 END_DATE_1 ID_QUOTE_2 BEGIN_DATE_2
----
I5 Q3 12/31/2000 Q6 01/12/2000
I6 Q4 12/31/2000 Q7 01/31/2001
Let me explain :
The SQL Query should return every ID_ITEM for which 2 consecutives quotes
don't verify the following rule :
end date previous quote = begin date next quote - 1 day
It looks like I should make an auto join in the query but I couldn't make it
:(
Thanks in advance for your help,
AlexSELECT t1.ID_ITEM,
t1.ID_QUOTE as ID_QUOTE_1,
t1.END_DATE as END_DATE_1,
t2.ID_QUOTE as ID_QUOTE_2,
t1.BEGIN_DATE as BEGIN_DATE_2
FROM M6_CHECK t1
INNER JOIN M6_CHECK t2 ON t1.ID_ITEM=t2.ID_ITEM
AND t1.BEGIN_DATE<t2.BEGIN_DATE
AND DATEADD(day,1,t1.END_DATE)<>t2.BEGIN_DATE
ORDER BY t1.ID_QUOTE,t1.ID_ITEM,t1.BEGIN_DATE|||typo..
t1.BEGIN_DATE as BEGIN_DATE_2
should be
t2.BEGIN_DATE as BEGIN_DATE_2|||This doesn't show up in the test data, but
more correctly I think it should be this
SELECT t1.ID_ITEM,
t1.ID_QUOTE as ID_QUOTE_1,
t1.END_DATE as END_DATE_1,
t2.ID_QUOTE as ID_QUOTE_2,
t2.BEGIN_DATE as BEGIN_DATE_2
FROM M6_CHECK t1
INNER JOIN M6_CHECK t2 ON t1.ID_ITEM=t2.ID_ITEM
AND t1.BEGIN_DATE<t2.BEGIN_DATE
AND DATEADD(day,1,t1.END_DATE)<>t2.BEGIN_DATE
AND t2.BEGIN_DATE=(SELECT MIN(t3.BEGIN_DATE)
FROM M6_CHECK t3
WHERE t3.ID_ITEM=t1.ID_ITEM
AND
t3.BEGIN_DATE>t1.BEGIN_DATE)
ORDER BY t1.ID_ITEM,t1.ID_QUOTE,t1.BEGIN_DATE|||It's almost the good result. The problem is that if you add the following
record
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q9','I3','01/01/2001','12/31/2001');
This record should appear in the result, and with your query it does not :(
Damn it !!
Thanks a lot for your help !!
Alex|||What result are you expecting? Q1 -> Q9 or Q9 ->Q1 or both?
Try this
SELECT t1.ID_ITEM,
t1.ID_QUOTE as ID_QUOTE_1,
t1.END_DATE as END_DATE_1,
t2.ID_QUOTE as ID_QUOTE_2,
t2.BEGIN_DATE as BEGIN_DATE_2
FROM M6_CHECK t1
INNER JOIN M6_CHECK t2 ON t1.ID_ITEM=t2.ID_ITEM
AND t1.ID_QUOTE<t2.ID_QUOTE
AND t1.BEGIN_DATE<=t2.BEGIN_DATE
AND DATEADD(day,1,t1.END_DATE)<>t2.BEGIN_DATE
ORDER BY t1.ID_ITEM,t1.ID_QUOTE,t1.BEGIN_DATE