开发者

Difference between dates when grouping in SQL

I have a table of purchases containing a user_id and a date_of_purchase.

I need to be able to select all the users who have made 2 purchases within 12 months of each other. The dates can be any point in time as long as they are less than 12 months apart.开发者_开发百科

e.g.

user_id      date_of_purchase
123          01/Jan/2010
124          01/Aug/2010
123          01/Feb/2010
124          05/Aug/2008

In this example i want user_id 123


select distinct user_id 
from MyTable t1
inner join MyTable t2 on t1.user_id = t2.user_id 
where t1.date_of_purchase - t2.date_of_purchase <= 365

Note: this does not handle leap years.


In Oracle, there's a DATE_DIFF function - I think the MS equivalent has no underscore (DATEDIFF).

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜