Forum Discussion
SmithToronto
Aug 07, 2024Copper Contributor
To find the Missing rows
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. To more clear: if CEO has 'SAYT' in Acc_Pe...
- 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
LainRobertson
Silver Contributor
Hi, Nadeem.
Your expected results don't quite match your sample data, where "10006" has two entries for "SEYT" rather than one each for "SEYT" and "ZZZZ", meaning "10006" should feature in your results.
Setting that aside, here's an example.
T-SQL
SELECT
[ceos].[ceo]
FROM
(SELECT DISTINCT [ceo] FROM [tmp]) AS [ceos]
LEFT OUTER JOIN [tmp] AS [t] ON
[ceos].[ceo] = [t].[ceo]
AND [t].[acc_personal] = 'ZZZZ'
WHERE
[t].[ceo] IS NULL;
Output
Cheers,
Lain
SmithToronto
Aug 08, 2024Copper Contributor
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
- LainRobertsonAug 08, 2024Silver Contributor
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