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 updatedand when
when NullFlag updated
and
when conflict flag updated

can you explain to me according to sample below

result of query data details rows without grouping

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

sample data :

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)

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

ahmed elbarbary6y ago
sample data :
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)