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