-1

I have an invoice table that includes invoice dates (Date_Effective) per Company_ID, and I'm trying to essentially find those customers that, at one point or another, had a gap in invoicing of 2 years or more (for example--Customer ABC started invoicing on 1/1/2019 - 6/30/2020 and then resumed invoicing on 8/2/2023).

In addition, I'd love to run the query via a WHERE clause that would only pull customers that had an invoice date in a particular date range (so, using the example above, if I ran WHERE INVOICE_DATE BETWEEN 8/1/2023 & 8/3/2023 it would return Customer ABC, but if the BETWEEN was 7/30/2023 AND 8/1/2023 that customer would not be returned).

The table itself is extremely basic--four columns (Company_ID, Date_effective, Invoice_Amount, and Invoice_ID). As you can imagine, there are certainly multiple invoices for the same company & invoice date (just date, not datetime).

Charlieface
  • 52,284
  • 6
  • 19
  • 43
burghman217
  • 13
  • 2
  • 4
    This is a typical gaps and islands problem. It has been asked and answered on SO dozens and dozens of times. Often it is the search term that is hard to know. Just search `gaps and islands`. If you are still stuck you need to post table definitions along with sample data and desired output from that sample data so others can help. – Sean Lange Aug 23 '23 at 18:53
  • 3
    just use LAG(date_effective) over(partition by company_id) to get previous date and then compare the difference between them – siggemannen Aug 23 '23 at 18:57

0 Answers0