Range of values not from a table

Question

Range of values not from a table

datsun

SSCrazy

Points: 2397
More actions
October 16, 2019 at 3:53 pm

#3689323

Hi,
I want a range of values between a start number and end number say 10000 to 50000 using SQL, but these are not stored anywhere on a table.
The idea is to then compare each value with the user-ID of a database table to see which ID is missing and which exists.
e.g. In the "exists" below, i will replace it with the actual user-ID where a match is found and NULL or a zero for non-matching user-id
10000 exists
10001 exists
10002 NULL
10003 NULL
10003 exists
Any pointers on how to do this?
I don't want to create a range of numbered table, because in reality the range is in millions of big numbers.
Thank you,
Vinay

Viewing 4 posts - 1 through 3 (of 3 total)

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

DesNorton SSC-Insane Points: 24386 More actions · Answer 1

You can create an iTVF that will generate numbers on the fly

CREATE FUNCTION [dbo].[fn_GetNums](
    @low  AS BIGINT
  , @high AS BIGINT
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
  WITH
    T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
                                 , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
                                 , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
                                 , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
                                 , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
                                 , (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
                                 , (0),(0),(0),(0)) AS X(N))
  , Nums AS (SELECT TOP(@high - @low + 1)
             rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
             FROM T T1  -- Max =            64
                , T T2  -- Max =         4,096
                , T T3  -- Max =       262,144
                , T T4  -- Max =    16,777,216
            )
  SELECT
      rn
    , seqNum = @low + rn - 1
  FROM Nums;
GO

How to post data/code on a forum to get the best help.

drew.allen SSC Guru Points: 76985 More actions · Answer 2

Google "gaps and islands problem". Using that approach should perform better than joining to a numbers table, especially if the number of missing ids is very small relative to the existing ids.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

datsun SSCrazy Points: 2397 More actions · Answer 3

You can create an iTVF that will generate numbers on the fly
DesNorton,
Apologies for late response for this. But your function iTVF is perfect. Exactly what I needed. works for small to medium ranges, without the need of a table. Used it in CTE joined to a table and now am able to get missing IDs.
So useful.
Many thanks,
Vinay