Forum Discussion
To find the Missing rows
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, 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
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
1007Having 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
- Thanks so much
