SQLTeam.com | Weblogs | Forums

What must updated null flag updated or conflict flag updated?

I work on SQL server 2012 .really I don't understand what statement below do updated
when NullFlag updated
and
when conflict flag updated

result of query data details without count

Masked_ID DocumentID PartID NULLCount
29283933 76724 31345983 NULL
29283933 76724 31345984 NULL
29283933 76724 31345985 NULL
29283933 76724 31345986 NULL
29283933 NULL NULL 1
29283933 NULL NULL 1
29283933 76724 31345989 NULL
29283933 NULL NULL 1
29283933 NULL NULL 1
29283933 76724 31345992 NULL
UPDATE FFFF
SET 
Conflictflag= IIF((NotNULL+NuLLCount)<>RowsCount AND Ex.MaskExceptionID IS NULL ,CONCAT(Conflictflag,'PCN','|'),Conflictflag),
NULLflag=IIF((NotNULL+NuLLCount)=RowsCount AND NuLLCount>0 AND NULEX.NULLExceptionID IS NULL,CONCAT(NULLflag,'PCN','|'),NULLflag)
FROM 
(
--SELECT Masked_ID,SUM(CNT)/COUNT(DocumentID) AS NotNULL ,SUM(NULLCount)AS NuLLCount
SELECT Masked_ID,SUM(CNT)/nullif(COUNT(DocumentID),0) AS NotNULL ,SUM(NULLCount)AS NuLLCount
	FROM (
		SELECT FF.Masked_ID,  LC.DocumentID, 
		COUNT(DISTINCT LC.PartID) AS CNT,
		COUNT( CASE WHEN DocumentID IS NULL THEN 1 ELSE NULL END )NULLCount
		FROM ExtractReports.dbo.MultiMask FF 
		INNER JOIN Parts.Nop_Part ptt WITH(NOLOCK) ON ptt.Masked_ID=ff.Masked_ID 
		LEFT JOIN PCN.DocumentParts LC WITH(NOLOCK) ON ptt.PartID=LC.PartID	
		WHERE FF.PCNs LIKE '%|%'  
		GROUP BY FF.Masked_ID ,LC.DocumentID
		)DD
		GROUP BY DD.Masked_ID
	) DDFF 
	INNER JOIN  ExtractReports.dbo.MultiMask FFFF ON  DDFF.Masked_ID=FFFF.Masked_ID
	LEFT JOIN [ConflictReport].dbo.MaskExceptions EX ON EX.MaskID=FFFF.Masked_ID AND EX.FunctionName='PCN'
	LEFT JOIN [ConflictReport].dbo.NULLExceptions NULEX ON NULEX.MaskID=FFFF.Masked_ID AND NULEX.FunctionName='PCN'
	WHERE 
	FFFF.PCNs LIKE '%|%' 

on case of data above what must updated Null flag or conflict flag
and why
can you please help me

CREATE TABLE [Parts].[Nop_Part](
	[PartID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[PartNumber] [nvarchar](70) NOT NULL,
	[Masked_ID] [int] NULL,
 CONSTRAINT [PK_Nop_Part] PRIMARY KEY CLUSTERED 
(
	[PartID] ASC
)

insert into [Parts].[Nop_Part](PartID,PartNumber,Masked_ID)
values
('31345983','PLT0603Z1372ABT0','29283933') ,
('31345984','PLT0603Z1372ABT1','29283933') ,
('31345985','PLT0603Z1372ABT5','29283933') ,
('31345986','PLT0603Z1372ABTF','29283933') ,
('31345989','PLT0603Z1372ABTS','29283933') ,
('31345992','PLT0603Z1372ABTS','29283933') 

CREATE TABLE [PCN].[DocumentParts](
	[DocumentPartID] [int] IDENTITY(1,1) NOT NULL,
	[DocumentID] [int] NOT NULL,
	[PartID] [int] NULL,
 CONSTRAINT [PK_PCNParts] PRIMARY KEY CLUSTERED 
(
	[DocumentPartID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
   insert into [PCN].[DocumentParts](DocumentID,PartID) 
				   values
				  
(76724,31345983),
(76724,31345984) ,
(76724,31345985),
(76724,31345986) ,
(76724,31345989),
(76724,31345992)

CREATE TABLE [dbo].[MaskExceptions](
	[MaskExceptionID] [int] IDENTITY(1,1) NOT NULL,
	[MaskID] [int] NOT NULL,
	[FunctionName] [varchar](50) NULL,
	[FunctionID] [int] NULL
) ON [PRIMARY]
insert into [dbo].[MaskExceptions] (MaskID,FunctionName,FunctionID)
values
(29283933,'Introduction Date',4)

CREATE TABLE [dbo].[NULLExceptions](
	[NULLExceptionID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[MaskID] [int] NOT NULL,
	[FunctionName] [varchar](50) NULL,
	[FunctionID] [int] NULL
) ON [PRIMARY]

  insert into [dbo].[NULLExceptions] (MaskID,FunctionName,FunctionID)
values
(29283933,'PCN',18)


 CREATE TABLE [dbo].[MultiMask](
	[Masked_ID] [int] NOT NULL,
	[RowsCount] [int] NULL,
	[IntroductionDate] [varchar](150) NULL,
	[PCNs] [varchar](1200) NULL,
	[NULLflag] [varchar](1000) NULL,
	[Conflictflag] [varchar](2000) NULL,
	[MaskExceptions] [nvarchar](500) NULL,
 CONSTRAINT [PK_MultiMask] PRIMARY KEY CLUSTERED 
(
	[Masked_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 insert into [dbo].[MultiMask](Masked_ID,RowsCount,PCNS,MaskExceptions,NULLflag,Conflictflag)
 values
 (29283933,10,'NULL(4)|76724(6)','Introduction Date',NULL,NULL)

table
MultiMask

columns
NULLflag
Conflictflag

updated .. based on some logic in the SQL Statement

https://www.sqlshack.com/sql-update-syntax-explained/
https://www.w3schools.com/sql/sql_update.asp