Last 6 months duplicate records in SQL Server using t-sql?

Question

I got a table:

declare @t1 table(Pat_Ref int,regdt datetime)

insert into @t1 
values   (1,'2015-06-09 21:31:09.253')
        ,(1,'2015-04-09 21:31:09.253')
        ,(2,'2015-06-08 21:31:09.253')
        ,(2,'2015-03-09 21:31:09.253')
        ,(3,'2014-11-09 21:31:09.253')
        ,(3,'2015-02-15 21:31:09.253')
        ,(4,'2015-01-15 21:31:09.253')

I want the most recent regdt date of only duplicate records of last 6 months.

Note: first duplicate record can be more than 6 months.

For example

(3, '2014-11-09 21:31:09.253') and 
(3,'2015-02-15 21:31:09.253')

This should be treated as duplicate record

The result should be like this:

Pat_Ref regdt
1       2015-06-09 21:31:09.253   
2       2015-06-08 21:31:09.253   
3       2015-02-15 21:31:09.253

I tried with this query:

;with cte 
as (
    select * from (
        select *, ROW_NUMBER() over (partition by Pat_Ref order by regdt asc) Rn
        from @t1 where convert(date, regdt)>=DATEADD(MM, -6, convert(date,GETDATE()))
    ) t2 
        where t2.Rn>1)
        ,cte1 as ( select *,ROW_NUMBER() over (partition by Pat_Ref order by regdt desc) Rn1  from cte)
    select * from cte1
    where cte1.Rn1=1

I am getting like this:

Pat_Ref  regdt         
1        2015-06-09 21:31:09.253    
2        2015-06-08 21:31:09.253    

Any help. Thanks

Answer 1

It seems like an exist clause in a group by would work:

SELECT Pat_Ref, MAX(regdt)
FROM @t1 t1
WHERE EXISTS (
    SELECT 1 
    FROM @t1 t 
    WHERE t.Pat_Ref = t1.Pat_Ref 
      and t.regdt <> t1.regdt 
      and t.regdt between DATEADD(MM, -6, t1.regdt) and DATEADD(MM, 6, t1.regdt)
)
GROUP BY Pat_Ref

Answer 2

Using a having clause where the count > 1 and max regdt > today - 6months should work

SELECT  Pat_Ref, MAX(regdt)
FROM    @t1 t1
GROUP BY Pat_Ref
HAVING  COUNT(*) > 1 
        AND  MAX(regdt) >= DATEADD(MONTH, -6, GETDATE())