Min and Max value of missing sequenced data
Hello
Table( Typ, Id )
Typ Id
-----------------------
T1 1
T1 2
T1 400
T1 999
T2 1
T2 4
T2 10
T2 50
T3 50
T3 51
T3 150
From This table data required query should retrieve min and max value of the window of missing numbers where windowsize is let say greater or equal 5
Typ MinWinNumber MaxWinNumber
----------------------------------------------------------------------------------
T1 3 399
T1 401 998
T2 5 9
T2 11 49
T2 51 999 /* Attention 999 is highest number allowed by datatype */
T3 1 49
T3 52 149
T3 151 999
Something that woud sound like:
Select Typ, MinWinNumber( Id ), MaxWinNumber( id )
Tab
Where id <= 999
Group by Typ
having MaxWinNumber(id ) - MinWinNumber( Id ) >= 5
Is there any way to solve this in decalration way like anaylitical function or subquery/corelated subquery mechanism?
Table( Typ, Id )
Typ Id
-----------------------
T1 1
T1 2
T1 400
T1 999
T2 1
T2 4
T2 10
T2 50
T3 50
T3 51
T3 150
From This table data required query should retrieve min and max value of the window of missing numbers where windowsize is let say greater or equal 5
Typ MinWinNumber MaxWinNumber
----------------------------------------------------------------------------------
T1 3 399
T1 401 998
T2 5 9
T2 11 49
T2 51 999 /* Attention 999 is highest number allowed by datatype */
T3 1 49
T3 52 149
T3 151 999
Something that woud sound like:
Select Typ, MinWinNumber( Id ), MaxWinNumber( id )
Tab
Where id <= 999
Group by Typ
having MaxWinNumber(id ) - MinWinNumber( Id ) >= 5
Is there any way to solve this in decalration way like anaylitical function or subquery/corelated subquery mechanism?
0