title: SQL Server Consecutive Invoices excluding after 36 month gap
body: <p>I’m struggling with a problem of using a formula to identify loyal customers and I’m hoping for some insight. 
  The Loyal Customer criteria are as follows:</p>
  
  <ul>
  <li>The Customer must have been invoiced at least 4 times within 36
  months, with invoices falling in the following time ranges: 
  <ul>
  <li>First Invoice (A) – This does not necessarily have to be the first time the
  customer is ever invoiced, just the first invoice that falls into
  this pattern</li>
  <li>Second Invoice (B) – Has to occur from 1-12 months
  after Invoice A</li>
  <li>Third Invoice (C) – Has to occur from 13-24 months
  after Invoice A </li>
  <li>Fourth Invoice (D) – Has to occur from 25-36 months
  after Invoice A</li>
  </ul></li>
  <li>Once these criteria are met, all invoices with a    date greater than
  or equal to A are considered loyal customer    invoices 
  <ul>
  <li>Only invoices with positive sub totals should be counted    as qualifying
  invoices (A, B, C, or D), but all invoices should be    counted once
  the customer meets the qualifying criteria</li>
  </ul></li>
  </ul>
  
  <p>So far, I’m OK with this. Here is the query I’m using (It’s pretty slow, but I’m not running it very frequently so I’m not particularly concerned with speed):</p>
  
  <pre><code>;WITH 
  
  T1 AS 
  ( 
  SELECT
  CUSTOMER_ID,
  INVOICE_DATE,
  DATEPART(YEAR, INVOICE_DATE)*12 + DATEPART(MONTH, INVOICE_DATE) AS YM
  FROM INVOICE
  WHERE Sub_Total &gt; 0
  ),
  
  T2 AS
  (
  SELECT DISTINCT 
  A.CUSTOMER_ID,
  A.INVOICE_DATE A_DATE
  FROM T1 A
  JOIN T1 B ON A.CUSTOMER_ID = B.CUSTOMER_ID AND (B.YM &gt; A.YM AND B.YM &lt;= (A.YM + 12))
  JOIN T1 C ON A.CUSTOMER_ID = C.CUSTOMER_ID AND (C.YM &gt; B.YM AND C.YM BETWEEN (A.YM + 13) AND (A.YM + 24))
  JOIN T1 D ON A.CUSTOMER_ID = D.CUSTOMER_ID AND (D.YM &gt; C.YM AND D.YM BETWEEN (A.YM + 25) AND (A.YM + 36))
  )
  
  SELECT DISTINCT 
  I.CUSTOMER_ID,
  C.CUSTOMER_NAME,
  I.INVOICE_ID,
  cast(i.Invoice_Date as Date) as Invoice_Date,
  DATEPART(YEAR, I.INVOICE_DATE)*12 + DATEPART(MONTH, I.INVOICE_DATE) AS YM,
  DATEPART(MONTH, I.INVOICE_DATE) AS Mo,
  DATEPART(YEAR, I.INVOICE_DATE) AS Yr,
  I.SUB_TOTAL
  FROM INVOICE I
  JOIN T2 ON T2.CUSTOMER_ID = I.CUSTOMER_ID AND I.INVOICE_DATE &gt;= A_DATE
  JOIN CUSTOMER C ON C.CUSTOMER_ID = I.CUSTOMER_ID
  ORDER BY I.CUSTOMER_ID, INVOICE_DATE
  </code></pre>
  
  <p>However, what’s causing me to get stuck is another criteria stating that if a loyal customer goes 36 months without being invoiced they are no longer considered loyal, and any invoices following the 36-month gap should not be counted. If the customer completes the 4 qualifying invoices identified above (A, B, C, and D) again after the 36-month gap it is considered a new set of loyal invoices. </p>
  
  <p>Example:</p>
  
  <pre>
  +--------------------+--------------------+-----------------+
  | Invoice Month/Year | Qualifying Invoice | Count as Loyal? |
  +--------------------+--------------------+-----------------+
  | 01/04              | N/A                | No              |
  | 03/05              | A                  | Yes             |
  | 09/05              | B                  | Yes             |
  | 12/05              | N/A                | Yes             |
  | 06/06              | C                  | Yes             |
  | 04/07              | D                  | Yes             |
  | 06/07              | N/A                | Yes             |
  | 07/10              | N/A                | No              |
  | 08/10              | N/A                | No              |
  | 09/11              | A                  | Yes             |
  | 10/11              | B                  | Yes             |
  | 01/13              | C                  | Yes             |
  | 04/14              | D                  | Yes             |
  | 06/14              | N/A                | Yes             |
  +--------------------+--------------------+-----------------+
  </pre>
  
  <p>I apologize if I’ve made this too complex. Ultimately, I’m hoping to gain some insight into how to exclude all invoices after a 36-month gap except those that meet the loyal customer criteria again. </p>