Get sequential values ​​in the group defined by the minimum and maximum value

Question

I am trying to get groups of sequential values ​​from a table. I tried to find an example about this, but I could not use it in my case.

This is a small part of the table that I have.

CardID CardSerial CardStateID AssignUserID Denomination ----------- --------------------------- ----------- ------------ ------------- 2685680 214026002 3 81 10 2685681 214026003 3 81 10 2685682 214026004 3 81 10 2685683 214026005 3 81 10 2685684 214026006 3 81 10 2685778 214026100 3 81 10 2685779 214026101 3 81 10 2685780 214026102 3 81 10 2685781 214026103 3 81 10 2685782 214026104 3 81 10 2685878 214026200 3 81 10 2685879 214026201 3 81 10 2685880 214026202 3 81 10 2685881 214026203 3 81 10 2685882 214026204 3 81 10 2685883 214026205 3 81 10 2685884 214026206 3 81 10 

Expected results:

  min value max value cant Den --------------- --------------- -------- ---- 214026002 214026006 5 10 214026100 214026104 5 10 214026200 214026206 7 10 

The tough part for me is getting a group value for a query like

 select min(cardSerial), max(cardSerial), count(*), Den from Cards group by Den, GROUPING_VALUE_GENERATED 

Answer 1

Assuming cardSerial is an INT or other numeric data type ...

 WITH sequenced AS ( SELECT ROW_NUMBER() OVER (PARTITION BY Denomination ORDER BY cardSerial) AS sequence_id, * FROM Cards ) SELECT min(cardSerial), max(cardSerial), count(*), Denomination FROM Sequenced GROUP BY Denomination, cardSerial - sequence_id ORDER BY Denomination, cardSerial - sequence_id 

If it is not a numeric data type, convert it into a query into a suitable large numeric data type to allow cardSerial - sequence_id to work.

As the calculation of cardSerial - sequence_id gives something meaningful to the group ...

 214026002 - 01 = 214026001 214026003 - 02 = 214026001 214026004 - 03 = 214026001 214026005 - 04 = 214026001 214026006 - 05 = 214026001 214026100 - 06 = 214026094 214026101 - 07 = 214026094 214026102 - 08 = 214026094 214026103 - 09 = 214026094 214026104 - 10 = 214026094 214026200 - 11 = 214026189 214026201 - 12 = 214026189 214026202 - 13 = 214026189 214026203 - 14 = 214026189 214026204 - 15 = 214026189 214026205 - 16 = 214026189 214026206 - 17 = 214026189