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;
-- 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 )
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 )
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.
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
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
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
Thank you for your time
