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).
精彩评论