SQLTeam.com | Weblogs | Forums

SQL find a missing row in a list

if i get a list of records i need to find the missing row.
example
173652 1
173652.0 2
173652.0.018.01 4
173652.0.018.02 4

It should be
173652 1
173652.0 2
173652.0.018 3
173652.0.018.01 4
173652.0.018.02 4

I need to be able to generate the output as
173652.0.018 3
This row will include all the other columns that are associated to 173652.0.018.01 4
so i can just do an insert of this record.

I did try but nothing was generated
SELECT D.TARGET_PROJ_ID, S.TARGET_PROJ_ID
FROM PROJ D
LEFT OUTER JOIN PROJ S ON (S.TARGET_PROJ_ID = SUBSTRING(D.TARGET_PROJ_ID,0,CHARINDEX(D.TARGET_PROJ_ID,'.',-1)-1))
WHERE CHARINDEX(D.TARGET_PROJ_ID,'.') > 0
AND S.TARGET_PROJ_ID IS NULL
ORDER BY 1;

Table
Single field
TARGET_PROJ_ID

hi

you can get the missing row number 3 by using tally tables

but how do you know
173652.0.018 is the value that comes for .. 3

1 Like

please provide your data in proper DDL and DML

173652 1
173652.0 2
173652.0.018.01 4
173652.0.018.02 4

Doesnt tell us if it is 1 column or what

--DDL
create table #sample

--DML
insert into #sample

hi

please see tally table approach

-- please click arrow to the left for drop create data script
----------------------
-- create table 

create table Data
(
id varchar(100) ,
row_num int 
)

go 

---------------------------
-- insert into table 

insert into data select '173652            ', 1
insert into data select '173652.0          ', 2
insert into data select '173652.0.018.01   ', 4
insert into data select '173652.0.018.02   ', 4
go
;WITH Tally_Cte AS
(
    SELECT top 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))  as N  FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
select 
     a.N
   , b.id 
from 
  Tally_Cte a 
    left join 
  data b 
      on a.N = b.row_num
where 
     a.N <= (select max(row_num) from data ) 

Wow, for the row 3 that comes out as NULL is there a way to get the row below it so it becomes 173652.0.018

Plus it will have all the data that is on that row aswell..................column b, c, d etc,,,,i just didn't include that

yes you can ... add columns b c d etc to the case statement

;WITH Tally_Cte AS
(
    SELECT top 100 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))  as N  FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
select 
     a.N
   , case when b.id is null then LEAD(b.id) over(order by a.N) else b.id end 
from 
  Tally_Cte a 
    left join 
  data b 
      on a.N = b.row_num
where 
     a.N <= (select max(row_num) from data ) 

s

Thank you i am going to try and play with this to create all the missing data. The Row 3 would be without the 01 but i think you have given me enough to go away and play with creating this.

I couldn't think how to start, so i appreciate your help.

I ran this through, i think i need something else, not the missing number 3 but it is the missing value
173652.0.018

I could have
173652.0.018.02.02 and then the missing value is
173652.0.018.02

Each row should have an upper level
99999
99999.99
99999.99.99
99999.99.99.99
99999.99.99.99.99

If i have
99999
99999.99
99999.99.99.99
99999.99.99.99.99

The results is to generate 99999.99.99

If

Which doesn't match your original question

if i get a list of records i need to find the missing row.
example
173652 1
173652.0 2
173652.0.018.01 4
173652.0.018.02 4

It should be
173652 1
173652.0 2
173652.0.018 3
173652.0.018.01 4
173652.0.018.02 4

I allowed for 8 levels of PROJ_ID; naturally you can add more if you need them.

/* create sample data */
CREATE TABLE dbo.PROJ ( TARGET_PROJ_ID varchar(100) PRIMARY KEY )
GO
INSERT INTO dbo.PROJ VALUES
    ('173652'),
    ('173652.0'),
    ('173652.0.018.01'), --<<--missing level
    ('173652.0.018.02'),
    ('173999.0'),
    ('173999.0.018.02.02') --<<--2 missing levels!
GO

/*query to select missing level(s)*/
;WITH cte_sort_proj_ids AS (
    SELECT 
        *, 
        ROW_NUMBER() OVER(PARTITION BY proj_id_level1 ORDER BY proj_id_levels, TARGET_PROJ_ID) AS row_num 
    FROM dbo.PROJ
    CROSS APPLY (
        SELECT
            LEFT(TARGET_PROJ_ID, CHARINDEX('.', TARGET_PROJ_ID + '.') - 1) AS proj_id_level1, 
            LEN(TARGET_PROJ_ID) - LEN(REPLACE(TARGET_PROJ_ID, '.', '')) + 1 AS proj_id_levels
    ) AS new_columns
),
cte_tally10 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
)
SELECT
    p2.proj_id_level1 + 
    '.' + proj_id_level2 +
    CASE WHEN levels.number >= 3 THEN '.' + proj_id_level3 ELSE '' END +
    CASE WHEN levels.number >= 4 THEN '.' + proj_id_level4 ELSE '' END +
    CASE WHEN levels.number >= 5 THEN '.' + proj_id_level5 ELSE '' END +
    CASE WHEN levels.number >= 6 THEN '.' + proj_id_level6 ELSE '' END +
    CASE WHEN levels.number >= 7 THEN '.' + proj_id_level7 ELSE '' END +
    CASE WHEN levels.number >= 8 THEN '.' + proj_id_level8 ELSE '' END
        AS missing_proj_level
FROM cte_sort_proj_ids p1
INNER JOIN cte_sort_proj_ids p2 ON 
    p2.proj_id_level1 = p1.proj_id_level1 AND
    p2.row_num = p1.row_num + 1 
    AND p2.proj_id_levels > p1.proj_id_levels + 1
INNER JOIN cte_tally10 levels ON levels.number BETWEEN p1.proj_id_levels + 1 AND p2.proj_id_levels - 1
CROSS APPLY (
    SELECT 
        MAX(CASE WHEN ds.ItemNumber = 2 THEN ds.Item ELSE '' END) AS proj_id_level2,
        MAX(CASE WHEN ds.ItemNumber = 3 THEN ds.Item ELSE '' END) AS proj_id_level3,
        MAX(CASE WHEN ds.ItemNumber = 4 THEN ds.Item ELSE '' END) AS proj_id_level4,
        MAX(CASE WHEN ds.ItemNumber = 5 THEN ds.Item ELSE '' END) AS proj_id_level5,
        MAX(CASE WHEN ds.ItemNumber = 6 THEN ds.Item ELSE '' END) AS proj_id_level6,
        MAX(CASE WHEN ds.ItemNumber = 7 THEN ds.Item ELSE '' END) AS proj_id_level7,
        MAX(CASE WHEN ds.ItemNumber = 8 THEN ds.Item ELSE '' END) AS proj_id_level8
    FROM dbo.DelimitedSplit8K(p2.TARGET_PROJ_ID, '.') AS ds 
) AS get_all_proj_id_levels
ORDER BY 1
1 Like

Here's the code for the "standard" dbo.DelimitedSplit8K function, in case you don't already have it:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DelimitedSplit8K] (
    @pString varchar(8000),
    @pDelimiter char(1)
    )
RETURNS TABLE WITH SCHEMABINDING 
AS
/*SELECT * FROM dbo.DelimitedSplit8K('ab/c/def/ghijklm/no/prq/////st/u//', '/')*/
RETURN
/*Inline CTE-driven "tally table" produces values from 0 up to 10,000: enough to cover varchar(8000).*/
WITH E1(N) AS (SELECT N FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Ns(N)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max 
    ctetally(N) AS (/* This provides the "zero base" and limits the number of rows right up front,
                       for both a performance gain and prevention of accidental "overruns". */
        SELECT 0 UNION ALL
        SELECT TOP (DATALENGTH(ISNULL(@pString, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    ), cteStart(N1) AS ( /* This returns N+1 (starting position of each "element" just once for each delimiter). */
        SELECT t.N+1
        FROM ctetally t
        WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
    )
/* Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. */
SELECT ROW_NUMBER() OVER(ORDER BY s.N1) AS ItemNumber,
       SUBSTRING(@pString, s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1), 0) - s.N1, 8000)) AS Item
FROM cteStart s;
GO

Wow, thank you for the help i am going to try it tomorrow. I was only at google search tonight trying to find something.

I tested it and it works a treat.
i get '173652.0.018.01'

Now i have to go back to the table and copy everything from
('173652'),
('173652.0'),
('173652.0.018.01'), --<<--missing level
('173652.0.018.02'),
('173999.0'),
('173999.0.018.02.02') --<<--2 missing levels!

I have implemented this at it works a treat, i just wanted to send my appreciation again to you, as i couldn't work it out.
I am going to review it in detail and become this good one day :slight_smile:
Thank you for your time

You're welcome.

Thanks very much for the good feedback!

Hi Tracey

Your Comment
I am going to review it in detail and become this good one day :slight_smile:

Its real real easy to do this ( any one can i mean any Tom Dick and Harry Can ) ...
Real Simple ...
Google Seach .. Lots of Videos How To ...

==== Its all the same thoughts !! inter connecting !!! ... Thinking Skills That's It

This will be vague ... I guess if someone demos for you !!! u will understand .. how FAST it is