Forum Discussion
To find the Missing rows
- Aug 08, 2024
Having accurate data and an in-depth description certainly makes things easier.
T-SQL
SELECT [ceos].[ceo] FROM (SELECT DISTINCT [ceo] FROM [tmp]) AS [ceos] LEFT OUTER JOIN [tmp] AS [t1] ON [ceos].[ceo] = [t1].[ceo] AND [t1].[acc_personal] = 'SEYT' LEFT OUTER JOIN [tmp] AS [t2] ON [ceos].[ceo] = [t2].[ceo] AND [t2].[acc_personal] = 'ZZZZ' WHERE [t2].[ceo] IS NULL;
Output
Cheers,
Lain
Hi, I have corrected the data in original request and more clarified in original request. But query is not correct. There is no condition for "SEYT" in the query that's why it will grab every CEO record if CEO has no 'ZZZZ' in Acc_Personal.
I said that "Every CEO should have both 'SEYT' and 'ZZZZ' records in Acc_Personal. I want to find any CEO record if anyone is missing 'ZZZZ' record. See table below.".
Meaning if CEO has 'SAYT' in Acc_Personal but not 'ZZZZ' then this record should show in the desired output. This condition should be in the query to grab the correct result.
If CEO has both 'SAYT' and 'ZZZZ' in Acc_Personal then this CEO number should not be in the desired output. This condition should be in the query to grab the correct result.
Thanks,
Desired Output:
-----------------
CEO Acc_personal AMT DATEA STAT
1003
1005
1007
Having accurate data and an in-depth description certainly makes things easier.
T-SQL
SELECT
[ceos].[ceo]
FROM
(SELECT DISTINCT [ceo] FROM [tmp]) AS [ceos]
LEFT OUTER JOIN [tmp] AS [t1] ON
[ceos].[ceo] = [t1].[ceo]
AND [t1].[acc_personal] = 'SEYT'
LEFT OUTER JOIN [tmp] AS [t2] ON
[ceos].[ceo] = [t2].[ceo]
AND [t2].[acc_personal] = 'ZZZZ'
WHERE
[t2].[ceo] IS NULL;
Output
Cheers,
Lain
- SmithTorontoAug 08, 2024Copper ContributorThanks so much