Check Sequence in Max Min Values

Question

I have a database table that Stores Maximum and Minimum Price Breaks for a Product.

Does anyone know of the SQL which say if I have a break from one Max to the Min of the next item. E.g. 1-10 12-20 I would like it to return me either the numbers that are missing or at the very least a count or bool if it can detect a break from the Absolute Min and the Absolute Max by going through each range.

SQL Server (MSSQL) 2008

Answer 1

For a database that supports window functions, like Oracle:

SELECT t.*
     , CASE LAG(maxq+1, 1, minq) OVER (PARTITION BY prod ORDER BY minq)
          WHEN minq
          THEN 0
          ELSE 1
       END AS is_gap
  FROM tbl t
;

This will produce is_gap = 1 for a row that forms a gap with the previous row (ordered by minq). If your quantity ranges can overlap, the required logic would need to be provided.

http://sqlfiddle.com/#!4/f609e/4

Answer 2

Something like this, giving max quantities that aren't the overall max for the product and don't have a min quantity following them:

select prev.tbProduct_Id,prev.MaxQuantity
  from yourtable prev
  left join (select tbProduct_ID, max(MaxQuantity) MaxQuantity from yourtable group by tbProduct_id) maxes
    on maxes.tbProduct_ID=prev.tbProduct_Id and maxes.MaxQuantity=prev.MaxQuantity
  left join yourtable next
    on next.tbProduct_Id=prev.tbProduct_Id and next.MinQuantity=prev.MaxQuantity+1
  where maxes.tbProduct_Id is null and next.tbProduct_Id is null;

This would fail on your sample data, though, because it would expect a row with MinQuantity 21, not 20.