CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Matt
Matt

Reputation: 4190

Check if matching record exists within 28 days of current record

I have a table with a unique id field, a userid field, and a date field.

What I want to do is have a column which flags if there is a record in the next 28 days with the same userid.

It is only the future i'm looking at, not past records..

id | uid | date       | future
1  | 342 | 23/10/2007 | 1
2  | 342 | 3/11/2007  | 0
3  | 342 | 20/2/2009  | 0
4  | 147 | 22/2/2009  | 0

This is a basic example of the result i'm after.

I'm using mssql 2008

Upvotes: 1

Views: 2847

Answers (2)

bobs
bobs

Reputation: 22184

Here's another option

SELECT id, uid, DATE,
    CASE WHEN MIN(daterange) BETWEEN 0 AND 28 THEN 1 ELSE 0 END
FROM
    (
    SELECT m1.id, m1.uid, m1.date, DATEDIFF(DD, m1.date, m2.date) AS daterange
    FROM MyTable m1
    LEFT OUTER JOIN MyTable m2 ON m1.uid = m2.uid
        AND m1.id <> m2.id
        AND m1.date <= m2.date
) x
GROUP BY id, uid, DATE
ORDER BY id

Upvotes: 1

Ryan Gross
Ryan Gross

Reputation: 6515

The following query would return the rows matching your requirement. If you want to, you could create a view from this query so that you could run other queries on just the future rows.

SELECT id, uid, date FROM orders current
WHERE EXISTS 
(
    SELECT * from orders future 
    WHERE future.date < DateAdd(DAYS, 28, current.date)
    AND future.date > getdate()
    AND future.uid = current.uid
)

Upvotes: 3

Related Questions