Forum Discussion

SmithToronto's avatar
SmithToronto
Copper Contributor
Aug 07, 2024

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_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.

 

CREATE TABLE DBO.TMP (CEO VARCHAR(20), Acc_Personal VARCHAR(20), AMT INT, DATEA DATETIME, STAT VARCHAR(1))

 

INSERT INTO DBO.TMP VALUES ('10001','SEYT',78, '2024-04-09','N'

INSERT INTO DBO.TMP VALUES ('10001','ZZZZ',12, '2024-03-09','N'

INSERT INTO DBO.TMP VALUES ('10002','SEYT',45, '2024-06-02','N'

INSERT INTO DBO.TMP VALUES ('10002','ZZZZ',55, '2024-07-07','D'

INSERT INTO DBO.TMP VALUES ('10003','SEYT',76, '2024-08-09','N'

INSERT INTO DBO.TMP VALUES ('10004','SEYT',45, '2024-04-02','C'

INSERT INTO DBO.TMP VALUES ('10004','ZZZZ',21, '2024-07-09','N'

INSERT INTO DBO.TMP VALUES ('10005','SEYT',57, '2024-04-01','N'

INSERT INTO DBO.TMP VALUES ('10006','ZZZZ',59, '2024-04-01','B'

INSERT INTO DBO.TMP VALUES ('10006','SEYT',47, '2024-02-01','A'

INSERT INTO DBO.TMP VALUES ('10007','SEYT',59, '2024-04-09','N'

 

INSERT INTO DBO.TMP VALUES ('10010','AAAA',59, '2024-04-01','B'

INSERT INTO DBO.TMP VALUES ('10010','ZZZZ',47, '2024-02-01','A'

 

Note: CEO-10010 should not be in the result because this CEO has no record of SEYT in Acc_personal.

 

Desired Output

-----------------

CEO    Acc_personal AMT  DATEA  STAT

1003

1005

1007

 

  • LainRobertson's avatar
    LainRobertson
    Aug 08, 2024

    SmithToronto 

     

    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's avatar
    LainRobertson
    Silver Contributor
    Aug 08, 2024

    SmithToronto 

     

    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's avatar
      SmithToronto
      Copper Contributor
      Aug 08, 2024

      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

      • LainRobertson's avatar
        LainRobertson
        Silver Contributor
        Aug 08, 2024

        SmithToronto 

         

        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

Resources