Fill in missing gaps on intergers

Question

Fill in missing gaps on intergers

Geoff A

SSChampion

Points: 11511
More actions
August 22, 2017 at 6:50 am

#324987

given a starting value and an ending value when stored as INT. how can i achieve the following.
CREATE TABLE #T (Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, StartSeqNo INT, EndSeqNo INT) INSERT INTO #T ( StartSeqNo , EndSeqNo ) SELECT 26490, 26493 UNION SELECT 91177, 91189 UNION SELECT 42029, 42031
SELECT * FROM #T
I want a result set that looks like this;
Id Continous 1 26490 1 26491 1 26492 1 26493 2 42029 2 42030 2 42031 3 91177 3 91178 3 91179 3 91180 3 91181 3 91182 3 91183 3 91184 3 91185 3 91186 3 91187 3 91188 3 91189
thank you in advance

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Google: "Gaps and Islands", the best stuff will probably be by Itzik Ben-Gan

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 2

Hi Geoff. Long time no see.

First, create this function. It's a "programmable" Tally function.

CREATE FUNCTION [dbo].[fnTallyProg] /********************************************************************************************************************** Purpose: Given a start value, end value, and increment, create a sequencial list of integers.

Programmers Notes:
1. The increment can be negative if the start value is greater than the end value.

Revison History:
Rev 00 - 18 Feb 2017 - Jeff Moden
- Initial creation and unit test.
**********************************************************************************************************************/

        (
         @pStart     BIGINT
        ,@pEnd       BIGINT
        ,@pIncrement BIGINT
        )
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
E01(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N))    --10 rows
,E04(N) AS (SELECT 1 FROM E01 a CROSS JOIN E01 b CROSS JOIN E01 c CROSS JOIN E01 d) --10 Thousand rows
,E16(N) AS (SELECT 1 FROM E04 a CROSS JOIN E04 b CROSS JOIN E04 c CROSS JOIN E04 d) --10 Quadrillion rows, which is crazy
            SELECT TOP (ABS((@pEnd-@pStart+@pIncrement)/@pIncrement))
                   N = (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)*@pIncrement+@pStart
              FROM E16
             WHERE (@pStart<=@pEnd AND @pIncrement > 0)
                OR (@pStart>=@pEnd AND @pIncrement < 0)
;
GO

After that and using your good test data, the problem becomes trivial.

SELECT t.Id ,Continuous = s.N FROM #T t CROSS APPLY dbo.fnTallyProg(t.StartSeqNo,t.EndSeqNo,1) s ;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 3

We have this piece: http://www.sqlservercentral.com/articles/Advanced+Querying/anefficientsetbasedsolutionforislandsandgaps/1619/

Geoff A SSChampion Points: 11511 More actions · Answer 4

thanks Jeff. Much appreciated. Will you be in Seattle this year? we could catch up then....

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 5

Geoff A - Tuesday, August 22, 2017 7:42 AM
thanks Jeff. Much appreciated. Will you be in Seattle this year? we could catch up then....

Heh... no. I'm all "self-funded" and I can't afford the trip even if I to submit a session and have it selected. Besides, half the distance between Novi and Troy is a lot less and I wouldn't have to buy new sox for the occasion. 😉 We should make it a plan sometime soon.

p.s. What are you going to use the source of your posted problem for?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Geoff A SSChampion Points: 11511 More actions · Answer 6

we are running Dynamics AX 2012 for Retail here. (all in azure but that is irrelevant, only mentioning it for sympathy)

anyway, the transaction ID are suppose to be sequential. however, with the possibility of thansactions being either on the register, the store server or in back office, sometimes things get lost.

i have already created ways to identify, by transaction ID, where the transactions are. (one of the 3 places i mentioned)

so the plan is to generate a VP dashboard that shows that info.
the ID's are "suppose to be" all in back office. i query the back office table to find the gaps. then using your awesome function, fill them in. the filled in gaps should then be just a join away into the tables created for register transactions or store server transactions.

the dashboard will show missing transactions and where they are. either on the store server or the register.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 7

Geoff A - Tuesday, August 22, 2017 8:02 AM
we are running Dynamics AX 2012 for Retail here. (all in azure but that is irrelevant, only mentioning it for sympathy)
anyway, the transaction ID are suppose to be sequential. however, with the possibility of thansactions being either on the register, the store server or in back office, sometimes things get lost.
i have already created ways to identify, by transaction ID, where the transactions are. (one of the 3 places i mentioned)
so the plan is to generate a VP dashboard that shows that info.
the ID's are "suppose to be" all in back office. i query the back office table to find the gaps. then using your awesome function, fill them in. the filled in gaps should then be just a join away into the tables created for register transactions or store server transactions.
the dashboard will show missing transactions and where they are. either on the store server or the register.

Awesome. Thanks for the detail, Geoff. Much appreciated.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Geoff A SSChampion Points: 11511 More actions · Answer 8

Jeff Moden - Tuesday, August 22, 2017 8:05 AM
Geoff A - Tuesday, August 22, 2017 8:02 AM
we are running Dynamics AX 2012 for Retail here. (all in azure but that is irrelevant, only mentioning it for sympathy)
anyway, the transaction ID are suppose to be sequential. however, with the possibility of thansactions being either on the register, the store server or in back office, sometimes things get lost.
i have already created ways to identify, by transaction ID, where the transactions are. (one of the 3 places i mentioned)
so the plan is to generate a VP dashboard that shows that info.
the ID's are "suppose to be" all in back office. i query the back office table to find the gaps. then using your awesome function, fill them in. the filled in gaps should then be just a join away into the tables created for register transactions or store server transactions.
the dashboard will show missing transactions and where they are. either on the store server or the register.
Awesome. Thanks for the detail, Geoff. Much appreciated.

Function in place and working flawlessly.

my schedule is pretty hectic for the next few weeks, but I'll reach out to you before PASS. thanks again.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 9

I had forgotten that I was doing some experiments with the function and that the E16 line had been changed to "only" go to "E12" size. It won't affect any outcome you may need to produce but I've made the correction back to the original just to be technically correct.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 10

Geoff A - Tuesday, August 22, 2017 8:12 AM
Jeff Moden - Tuesday, August 22, 2017 8:05 AM
Geoff A - Tuesday, August 22, 2017 8:02 AM
we are running Dynamics AX 2012 for Retail here. (all in azure but that is irrelevant, only mentioning it for sympathy)
anyway, the transaction ID are suppose to be sequential. however, with the possibility of thansactions being either on the register, the store server or in back office, sometimes things get lost.
i have already created ways to identify, by transaction ID, where the transactions are. (one of the 3 places i mentioned)
so the plan is to generate a VP dashboard that shows that info.
the ID's are "suppose to be" all in back office. i query the back office table to find the gaps. then using your awesome function, fill them in. the filled in gaps should then be just a join away into the tables created for register transactions or store server transactions.
the dashboard will show missing transactions and where they are. either on the store server or the register.
Awesome. Thanks for the detail, Geoff. Much appreciated.
Function in place and working flawlessly.
my schedule is pretty hectic for the next few weeks, but I'll reach out to you before PASS. thanks again.

Awesome. I love instant gratification. Thanks for the feedback, Geoff.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Geoff A SSChampion Points: 11511 More actions · Answer 11

Jeff Moden - Tuesday, August 22, 2017 8:15 AM
I had forgotten that I was doing some experiments with the function and that the E16 line had been changed to "only" go to "E12" size. It won't affect any outcome you may need to produce but I've made the correction back to the original just to be technically correct.

my transactions ID's will never exceed 10⁷ in my working lifetime, so we we are good.