Generate unique id using checksum in SQL Server

ahmedsa
 
on Jun 10, 2021 10:23 PM
686 Views

I work on SQL server 2012 I face issue I can't display Part Number with Mask related for family

CREATE TABLE [dbo].[Partspc](
     [PortionKey] [nvarchar](255) NULL,
     [GroupID] [float] NULL,
     [familyid] [float] NULL
 ) ON [PRIMARY]
    
 INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
 INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
 INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
 INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
 INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
 INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'K', 4, 7524090)
 INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'M', 4, 7524090)
 INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'blank', 4, 7524090)
    
    
 CREATE TABLE [dbo].[Masksspc](
     [PortionKey] [nvarchar](255) NULL,
     [GroupID] [float] NULL,
     [familyid] [float] NULL
 ) ON [PRIMARY]
    
 INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
 INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
 INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
 INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
 INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
 INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'_', 4, 7524090)
 INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES ('blank', 4, 7524090)
    
 DECLARE @GetFinalResultParts as table
  (    [familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
      [GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
      [PartNumber] [nvarchar](200) NOT NULL INDEX IXkpart NONCLUSTERED
      )
     
  insert into @GetFinalResultParts 
  SELECT distinct r.familyid,r.GroupID,IIF(r.PortionKey='blank','',r.PortionKey)  
  FROM    dbo.Partspc r with(nolock)
        
  ;WITH cte AS (
          SELECT  t1.familyid,t1.GroupID,cast((t1.PartNumber) as nvarchar(200)) PartNumber
          FROM    @GetFinalResultParts t1
          WHERE   t1.GroupID = 1
          UNION ALL
          SELECT  t.familyid,t.GroupID,cast((s.PartNumber+ t.PartNumber) as nvarchar(200)) PartNumber
        
          FROM    @GetFinalResultParts t INNER JOIN
                  cte s ON T.GroupID = s.GroupID + 1
  )
  SELECT  familyid,PartNumber 
  into  getfinaldatapc      
  from    cte
  where groupid =(select max(GroupID) from dbo.Partspc with(nolock))
  group by familyid,PartNumber
    
    
 DECLARE @GetFinalMasks as table
  (    [familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
      [GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
      [MaskNumber] [nvarchar](200) NOT NULL INDEX IXkmask NONCLUSTERED)

  insert into @GetFinalMasks 
  SELECT distinct m.familyid,m.GroupID,IIF(m.PortionKey='blank','',m.PortionKey)  
  FROM    dbo.Masksspc m with(nolock)
    
        
  ;WITH cte AS (
          SELECT  t1.familyid,t1.GroupID,cast((t1.MaskNumber) as nvarchar(200)) MaskNumber 
          FROM    @GetFinalMasks t1
          WHERE   t1.GroupID = 1
          UNION ALL
          SELECT  t.familyid,t.GroupID,cast((s.MaskNumber+t.MaskNumber) as nvarchar(200)) MaskNumber
        
          FROM    @GetFinalMasks t INNER JOIN
                  cte s ON T.GroupID = s.GroupID + 1
  )
  SELECT  familyid,MaskNumber 
  into  getfinaldatapcmask      
  from    cte
  where groupid =(select max(GroupID) from dbo.Masksspc with(nolock)) 
  group by familyid,MaskNumber
    
   SELECT * FROM getfinaldatapc
  select * from getfinaldatapcmask

Expected Result 

familyid    PartNumber    MaskNumber
 7524090    T496B754    T496B754
 7524090    T496B754K    T496B754_
 7524090    T496B754M    T496B754_
 7524090    T496B755    T496B755
 7524090    T496B755K    T496B755_
 7524090    T496B755M    T496B755_
 7524090    T496X754    T496X754
 7524090    T496X754K    T496X754_
 7524090    T496X754M    T496X754_
 7524090    T496X755    T496X755
 7524090    T496X755K    T496X755_
 7524090    T496X755M    T496X755_

this is solution but i dont need to use like can you help me

get same result above but without using like

can i generate unique id for table getfinaldatapc by checksum or what every and generate unique id for table getfinaldatapcmask also by checksum then join between two tables by id generated if this possible

SELECT * FROM getfinaldatapc g
JOIN getfinaldatapcmask gm
ON g.familyid=gm.familyid AND (g.PartNumber=gm.MaskNumber OR
(g.PartNumber LIKE gm.MaskNumber+'%' AND LEN(g.PartNumber)=LEN(gm.MaskNumber))

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
Results 1 - 5 of 6
dharmendr
 
on Jun 10, 2021 10:42 PM

I will get back to you soon.

dharmendr
 
on Jun 11, 2021 05:37 AM

Hi ahmedsa,

Refer below query.

SQL

DECLARE @GetFinalResultParts as table
(    
	[familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
    [GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
    [PartNumber] [nvarchar](200) NOT NULL INDEX IXkpart NONCLUSTERED
)
         
insert into @GetFinalResultParts
SELECT distinct r.familyid,r.GroupID,IIF(r.PortionKey='blank','',r.PortionKey) 
FROM    dbo.Partspc r with(nolock)

;WITH cte AS (
    SELECT  t1.familyid,t1.GroupID,cast((t1.PartNumber) as nvarchar(200)) PartNumber
    FROM    @GetFinalResultParts t1
    WHERE   t1.GroupID = 1
    UNION ALL
    SELECT  t.familyid,t.GroupID,cast((s.PartNumber+ t.PartNumber) as nvarchar(200)) PartNumber
         
    FROM    @GetFinalResultParts t INNER JOIN
            cte s ON T.GroupID = s.GroupID + 1
)
SELECT  familyid,PartNumber
into  #getfinaldatapc     
from    cte
where groupid =(select max(GroupID) from dbo.Partspc with(nolock))
group by familyid,PartNumber


DECLARE @GetFinalMasks as table
(    
	[familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
	[GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
	[MaskNumber] [nvarchar](200) NOT NULL INDEX IXkmask NONCLUSTERED
)
         
insert into @GetFinalMasks
SELECT distinct m.familyid,m.GroupID,IIF(m.PortionKey='blank','',m.PortionKey) 
FROM dbo.Masksspc m with(nolock)
         
         
;WITH cte AS (
    SELECT  t1.familyid,t1.GroupID,cast((t1.MaskNumber) as nvarchar(200)) MaskNumber
    FROM    @GetFinalMasks t1
    WHERE   t1.GroupID = 1
    UNION ALL
    SELECT  t.familyid,t.GroupID,cast((s.MaskNumber+t.MaskNumber) as nvarchar(200)) MaskNumber
         
    FROM    @GetFinalMasks t INNER JOIN
            cte s ON T.GroupID = s.GroupID + 1
)

SELECT  familyid,MaskNumber
into  #getfinaldatapcmask     
from    cte
where groupid =(select max(GroupID) from dbo.Masksspc with(nolock))
group by familyid,MaskNumber
     
SELECT familyid,PartNumber,REPLACE(REPLACE(PartNumber,'M','_'),'K','_') MaskNumber FROM #getfinaldatapc

DROP TABLE #getfinaldatapc
DROP TABLE #getfinaldatapcmask

 

ahmedsa
 
on Jun 11, 2021 06:16 AM

thank you for reply

this is static solution

i need to detect difference length automatice

ahmedsa
 
on Jun 11, 2021 06:18 AM

this is solution 

SELECT * FROM getfinaldatapc g
JOIN getfinaldatapcmask gm
ON g.familyid=gm.familyid AND (g.PartNumber=gm.MaskNumber OR
(g.PartNumber LIKE gm.MaskNumber+'%' AND LEN(g.PartNumber)=LEN(gm.MaskNumber))

this is solution but i dont need to use like can you help me

get same result above but without using like

ahmedsa
 
on Jun 11, 2021 10:17 AM

can i generate unique id for table getfinaldatapc by checksum or what every
and generate unique id for table getfinaldatapcmask also by checksum
then join between two tables by id generated
if this possible can you help me applying

Results 1 - 5 of 6