Question: I have a `Payment` table that looks a little like this:

```
Id (int identity)
CustomerId (int)
PaymentDate (SmallDateTime)

```

Now I want to write a query that will find those customers that have made three payments within a period of three months. Given the following data:

```
Id   CustomerId   PaymentDate (YYYY-MM-DD)
------------------------------------------
1    1            2010-01-01
2    1            2010-02-01
3    1            2010-03-01
4    1            2010-06-01
5    2            2010-04-01
6    2            2010-05-01
7    2            2010-06-01
8    2            2010-07-01

```

I would like to produce the following result:

```
CustomerId    LastPaymentDateInPeriod
-------------------------------------
1             2010-03-01
2             2010-07-01

```

Where `LastPaymentDateInPeriod` is the `PaymentDate` with the highest value within a three-month period. If there is more than one three-month period for a given customer it would have to return the highest value from the most recent period (this is what I tried to illustrate for customer 2 in the above example). **Note** that three payments on three consecutive days would also meet the criteria. The payments just have to fall within a three-month period.

I know how to do this with a cursor and a lot of smaller queries but this is slow (and, I've come to understand, should only be a last resort). So do any of you SqlServer geniuses know how to do this with a query?

Thanks in advance.

Answer: 
Bit of a rushed job as I'm off out.

```
declare @T TABLE
(
Id int,
CustomerId int,
PaymentDate SmallDateTime
)
insert into @T
SELECT 1, 1,'2010-01-01' UNION ALL
SELECT 2, 1,'2010-02-01' UNION ALL
SELECT 3, 1,'2010-03-01' UNION ALL
SELECT 4, 1,'2010-06-01' UNION ALL
SELECT 5, 2,'2010-04-01' UNION ALL
SELECT 6, 2,'2010-05-01' UNION ALL
SELECT 7, 2,'2010-06-01' UNION ALL
SELECT 8, 2,'2010-07-01' 

;with CTE1 AS
(
SELECT Id, CustomerId, PaymentDate, ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY PaymentDate) RN
FROM @T
),  CTE2 AS
(
SELECT  C1.Id, C1.CustomerId, MAX(C2.PaymentDate) AS LastPaymentDateInPeriod
FROM CTE1 C1 LEFT JOIN CTE1 C2 ON C1.CustomerId = C2.CustomerId AND C2.RN BETWEEN C1.RN AND C1.RN + 2  and C2.PaymentDate <=DATEADD(MONTH,3,C1.PaymentDate)
GROUP BY C1.Id, C1.CustomerId
HAVING COUNT(*)=3
)
SELECT CustomerId, MAX(LastPaymentDateInPeriod) LastPaymentDateInPeriod
FROM CTE2
GROUP BY CustomerId

```
I thought of:

`select customerId,max(PaymentDate) from payment where customerId in  

(select case when count(*)<3 then null else customerId end as customerId from payment 
where paymentdate>dateadd(month,-3,getdate()) group by customerId)  

group by customerId;`