SQL cross join restricted to dates within 7 days of each other

Question

I would like to do a cross join but only retain pairs of records that are within 7 days of each other. My code below retains pairs with exactly the same date. How can it be modified to allow dates within 7 days? I am using SQL Server 2008.

SELECT 
t1.[id] AS [A_id],
t2.[id] AS [B_id],
t1.[date] AS [A_date],
t2.[date] AS [B_date],
t1.[item] AS [A_item],
t2.[item] AS [B_item],
INTO [records_crossed]
FROM [records] t1
CROSS JOIN [records] t2
WHERE 
t1.[date]=t2.[date]
ORDER BY t1.[id],t2.[id]

Check out OVERLAPS. (BTW, switch to `INNER JOIN ... ON` instead of cross join... — jarlh, Feb 01 '16 at 13:31
Tag dbms used. (Too many products are far from ANSI SQL compliant when it comes to date/time.) — jarlh, Feb 01 '16 at 13:31
jarlh is right. Your cross join isn't really a cross join. It's an obfuscated inner join — , Feb 01 '16 at 13:32
Possible duplicate of [Difference of two date time in sql server](http://stackoverflow.com/questions/2116540/difference-of-two-date-time-in-sql-server) — Tab Alleman, Feb 01 '16 at 13:56

score 1 · Accepted Answer · answered Feb 01 '16 at 13:39

How you express such a join isn't that important. The query is essentially:

SELECT . . .
INTO [records_crossed]
FROM [records] t1 JOIN
     [records] t2
     ON ABS(DATEDIFF(DAY, t1.[date], t2.[date])) <= 7
ORDER BY t1.[id], t2.[id];

This will generate duplicate inversions (the same two records in a different order) and the same record. If you don't want such records:

SELECT . . .
INTO [records_crossed]
FROM [records] t1 JOIN
     [records] t2
     ON DATEDIFF(DAY, t1.[date], t2.[date]) BETWEEN 0 AND 7 AND
        t1.id <> t2.id
ORDER BY t1.[id], t2.[id];

score 0 · Answer 2 · answered Feb 01 '16 at 16:04

This will give what you need and allow the use of an INDEX on [records].[date] if present.

SELECT 
    t1.[id] AS [A_id],
    t2.[id] AS [B_id],
    t1.[date] AS [A_date],
    t2.[date] AS [B_date],
    t1.[item] AS [A_item],
    t2.[item] AS [B_item],
INTO 
    [records_crossed]
FROM 
    [records] t1
    INNER JOIN [records] t2 ON
        t2.[date] BETWEEN DATEADD(DAY,-7,t1.[date]) AND DATEADD(DAY,+7,t1.[date])
ORDER BY 
    t1.[id],
    t2.[id];

SQL cross join restricted to dates within 7 days of each other

2 Answers2