Hello Users, I am working on criteria set as below > Find ID's which who have at least 3 rows - date in second row within 12 months of first row and date in third row within 6 months of second row. Here is a sample data #ID Startdate Enddate Date_diff Months 143 3/6/2007 3/6/2007 * 143 9/17/2007 9/17/2007 195 6.414474 143 10/16/2008 10/16/2008 395 12.99342 143 12/19/2009 12/19/2009 429 14.11184 155 11/8/2011 11/19/2011 * 155 7/19/2012 7/21/2012 243 7.993421 155 5/31/2013 6/2/2013 314 10.32895 155 7/7/2013 7/8/2012 35 1.151316 155 4/10/2014 4/11/2014 641 21.08553 The Date_diff come from substracting lag(enddate) with Startdate. Based on the criteria mentioned above. Only ID 155 should be in the output as it has 3 consecutive rows with a row date_diff < 12 months and a next consecutive row with date_diff < 6 months. This should be BY ID. Any help is appreciated. Thank you.
... View more