1

I have an invoice detail table, that has a Invoice_ID and a Detail_id associated with each invoice. Example:

Detail_ID      date_Created       Invoice_id       Description
   1           1/1/18 12:02:03       1            Service 1
   2           1/1/18 12:02:04       1            Service 2 
   3           1/1/18 12:02:05       1            Service 3
   4           1/1/18 12:06:03       2            Service 1
   5           1/1/18 12:06:04       2            Service 2 
   6           1/1/18 12:06:05       2            Service 3
   7           1/1/18 12:08:03       3            Service 1
   8           1/1/18 12:08:04       3            Service 2 
   9           1/1/18 12:08:05       3            Service 3
  10           1/1/18 12:12:03       4            Service 1
  12           1/1/18 12:12:05       4            Service 3

What i'd like to do, is query the sample data, and return the "Invoice_id" that has a "Detail_ID" that is not in sequential order. For example - Notice above, Invoice_id 4, goes from detail_id 10 to detail_id 12. How can I do this?

Shmewnix
  • 1,553
  • 10
  • 32
  • 66

1 Answers1

3

(This answer assumes that ticket_id is really invoice_id.)

In SQL Server 2012+, you can use lag(). If I assume that there are no duplicate detail_ids, then you can do:

select id.invoice_id
from invoice_detail id
group by id.invoice_id
having count(*) <> max(id.detail_id) - min(id.detail_id) + 1;

Basically, this checks that the number of records matches the range given by the minimum and maximum values.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786