none
Looking to write a query to give me the missing numbers in a sequence

    Question

  • My query here gives me a list of numbers:

     select distinct cast(SUBSTRING(docket,7,999) as INT) from [DHI_IL_Stage].[dbo].[Violation] where InsertDataSourceID='40'
      and ViolationCounty='Carroll' and SUBSTRING(docket,5,2)='TR' and LEFT(docket,4)='2011'
      order by 1

    Gives back:

    I want to find the numbers missing in the sequence

    Thursday, May 15, 2014 5:55 PM

Answers

All replies

  • ;With Numbers
    AS
    (
    SELECT MIN(cast(SUBSTRING(docket,7,999) as INT)) AS StartVal,
    MAX(cast(SUBSTRING(docket,7,999) as INT)) AS EndVal 
    from [DHI_IL_Stage].[dbo].[Violation]
    UNION ALL
    SELECT StartVal + 1,EndVal
    FROM Numbers
    WHERE StartVal + 1 <= EndVal
    )
    
    select StartVal
    from Numbers n
    WHERE StartVal NOT IN
    (
    select distinct cast(SUBSTRING(docket,7,999) as INT) 
    from [DHI_IL_Stage].[dbo].[Violation] where InsertDataSourceID='40'
      and ViolationCounty='Carroll' and SUBSTRING(docket,5,2)='TR' and LEFT(docket,4)='2011'
      order by 1
    )


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, May 15, 2014 6:03 PM
  • It gave back this error:

    Msg 1033, Level 15, State 1, Line 21
    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified

    Thursday, May 15, 2014 6:16 PM
  • It gave back this error:

    Msg 1033, Level 15, State 1, Line 21
    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified

    ah didnt notice you'd an order by. just remove it

    ;With Numbers
    AS
    (
    SELECT MIN(cast(SUBSTRING(docket,7,999) as INT)) AS StartVal,
    MAX(cast(SUBSTRING(docket,7,999) as INT)) AS EndVal 
    from [DHI_IL_Stage].[dbo].[Violation]
    UNION ALL
    SELECT StartVal + 1,EndVal
    FROM Numbers
    WHERE StartVal + 1 <= EndVal
    )
    
    select StartVal
    from Numbers n
    WHERE StartVal NOT IN
    (
    select distinct cast(SUBSTRING(docket,7,999) as INT) 
    from [DHI_IL_Stage].[dbo].[Violation] where InsertDataSourceID='40'
      and ViolationCounty='Carroll' and SUBSTRING(docket,5,2)='TR' and LEFT(docket,4)='2011'
    )


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, May 15, 2014 6:32 PM
  • Gave me back this:

    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value 'T 000904' to data type int.

    Thursday, May 15, 2014 6:49 PM
  • That means you've non numeric data also present in the field. then you might have to do this

    ;With Numbers
    AS
    (
    SELECT MIN(cast(SUBSTRING(docket,7,999) as INT)) AS StartVal,
    MAX(cast(SUBSTRING(docket,7,999) as INT)) AS EndVal 
    from [DHI_IL_Stage].[dbo].[Violation]
    UNION ALL
    SELECT StartVal + 1,EndVal
    FROM Numbers
    WHERE StartVal + 1 <= EndVal
    )
    
    select StartVal
    from Numbers n
    WHERE StartVal NOT IN
    (
    select distinct cast(SUBSTRING(docket,7,999) as INT) 
    from [DHI_IL_Stage].[dbo].[Violation] where InsertDataSourceID='40'
      and ViolationCounty='Carroll' and SUBSTRING(docket,5,2)='TR' and LEFT(docket,4)='2011'
    AND ISNUMERIC(SUBSTRING(docket,7,999)+'0.0E0')=1
    )


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, May 15, 2014 7:19 PM
  • That gave back the same error, but yes the number is not entirely an int, its starts out in this format

    2012TR557

    and parsing out the trailing digits to give me back my numbers.

    Thursday, May 15, 2014 7:29 PM
  • The common theme with your errors is that it is issues with your data, it is for you to figure out why the data doesn't fit with the solution, not for everyone else to figure out why the solution doesn't work with your data.

    More than likely this

    MIN(cast(SUBSTRING(docket,7,999) as INT)) AS StartVal,
    MAX
    (cast(SUBSTRING(docket,7,999) as INT)) AS EndVal

    since you are already filtering non-numeric further on

    Thursday, May 15, 2014 9:37 PM
  • Well, that's what I need to know-if its possible to get the missing numbers in the sequence in filtered data. If I knew what the issue with my data is and how it would work, I wouldn't be asking.
    Friday, May 16, 2014 1:39 AM
  • CREATE TABLE t1  (C INT NOT NULL PRIMARY KEY)
    insert t1 select 1 union all
     select 3 union all
    select 4 union all
    select 5 union all
    select 7 union all
    select 10 


    SELECT
      c+n FROM t1, (
      SELECT 0 n UNION ALL SELECT 1
    ) T
    GROUP BY c+n
    HAVING MIN(n) = 1 and c+n < (select max(c) from t1)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Friday, May 16, 2014 3:21 AM