Q:

SQL query data that appears for consecutive years

I have tried several times to figure this out but no luck.
I have one table that I am trying to query.
InvNo (primary key),
CustID,
InvAmt,
DatePD
I want to pull all of the customers that have paid at least one invoice for 3 consecutive fiscal years. Fiscal year for this example is Aug 1 to Jul 31 of the following year. Thie InvNo is unique but the custID can appear multiple times depending on how many invoices they have paid. Can anyone help me with this?
For output I need one record per custID and how many consecutive years that CustID has paid an invoice.
CustID 333   ConsecutiveYears 7

A:

You can get the year by either subtracting 8 months or adding four months and extracting the year.  Then, lag() can solve the problem:
select distinct custid
from (select t.*, lag(yyyy, 2) over (partition by custid order by yyyy) as prev_yyyy_2
      from (select distinct custid, year(dateadd(month, -8, datepd)) as yyyy
            from t
           ) t
     ) t
where prev_yyyy_2 = yyyy - 2;

The innermost subquery just gets pairs of customers and year.  The lag() looks two rows behind.  Three years are present if "2 rows behind" is exactly 2 years ago.

