Sql Count Of 90 Day Gaps Between Records

Say I have a Payment table. I need to know the number of times the gap between payments is greater than 90 days grouped by personID. Payment frequency varies. There is no expect

Solution 1:

If you have SQL Server 2014, you can use the LAG or LEAD function to peek at other rows, making this easy:

SelectPersonId, Sum(InfrequentPayment) InfrequentPaymentsfrom
(
    select PersonId
    , case 
        when dateadd(day,@period,paymentdate) < coalesce(lead(PaymentDate) over (partition by personid order by PaymentDate),getutcdate()) 
        then 1 
        else 0 
      end InfrequentPayment
    from @Payment
) xGroupbyPersonId

Demo: http://sqlfiddle.com/#!6/9eecb7d/491

Explanation:

The outer SQL is fairly trivial; we take the results of the inner SQL, group by PersonId, and count/sum the number of times they've paid payment judged as Infrequent.

The inner SQL is also simple; we're selecting every record, making a note of the person and whether that payment (or rather the delay after that payment) was judged infrequent.

The case statement determines what constitutes an infrequent payment. Here we say that if the record's paymentdate plus 90 days is still earlier than the next payment (or current date if it's the last payment, so there's no next payment) then it's infrequent (1); otherwise it's not (0).

The coalesce is simply there to handle the last record for a person; i.e. so that if there is no next payment the current date is used (thus capturing anyone who's last payment was over 90 days before today).

Now for the "clever" bit: lead(PaymentDate) over (partition by personid order by PaymentDate). LEAD is a new SQL function which lets you look at the record after the current one (LAG is to see the previous record). If you're familiar with row_number() or rank() you may already understand what's going on here. To determine the record after the current one we don't look at the current query though; rather we specify an order by clause just for this function; that's what's in the brackets after the over keyword. We also want to only compare each person's payment dates with other payments made by them; not by any customer. To achieve that we use the partition by clause.

I hope that makes sense / meets your requirement. Please say if anything's unclear and I'll try to improve my explanation.

---

EDIT

For older versions of SQL, the same effect can be achieved by use or ROW_NUMBER and a LEFT OUTER JOIN; i.e.

;with cte (PersonId, PaymentDate, SequenceNo) as
(
    select PersonId
    , PaymentDate
    , ROW_NUMBER() over (partitionby PersonId orderby PaymentDate)
    from@Payment
)
select a.PersonId
, sum(casewhen dateadd(day,@period,a.paymentdate) <coalesce(b.paymentdate,getutcdate()) then1else0end) InfrequentPayments
from cte a
leftouterjoin cte b 
on b.PersonId = a.PersonId 
and b.SequenceNo = a.SequenceNo +1Groupby a.PersonId

Another method which should work on most databases (though less efficient)

selectPersonId
, sum(InfrequentPayment) InfrequentPaymentsfrom 
(
    select PersonId
    , case when dateadd(day,@period,paymentdate) < coalesce((
        select min(PaymentDate) 
        from @Payment b 
        where b.personid = a.personid
        and b.paymentdate > a.paymentdate
    ),getutcdate()) then 1 else 0 end InfrequentPayment
    from @Payment a
) xGroupbyPersonId

Solution 2:

Generic query for this problem given a timestamp field would be something like this:

SELECT p1.personID, COUNT(*)
FROM payments p1
JOIN payments p2 ON
  p1.timestamp < p2.timestamp
  AND p1.personID = p2.personID
  ANDNOTEXISTS (-- exclude combinations of p1 and p2 where p exists between themSELECT*FROM payments p 
   WHERE p.personID = p1.personID
   AND p.timestamp > p1.timestamp 
   AND p.timestamp < p2.timestamp)
WHERE
  DATEDIFF(p2.timestamp, p1.timestamp) >=90GROUPBY p1.personID