Display summary results based on empty value in another column

Question

I need some assistance and guidance.

I want to retrieve a summary of results based on values in another column. For example, I have a column in my data set that shows an audit date. There will be some records which will have an empty audit date, in that case I want to display summary results based on it which where so long as there is a specific date which is empty,a value will be assigned to it based on its grouping. I have made a sample illustration of the problem below:

ORIGINAL TABLE :

Table A
+--------------+--------------+--------------+
| officecode   | Username     |  auditdate   |
+--------------+--------------+--------------+
| ABC          |  ABCTOM      | 02-Dec-2012  |       
| ABC          |  ABCPET      | 08-Dec-2012  |
| ABC          |  ABCLEE      | 12-Dec-2012  |
| ABC          |  ABCHET      | 25-Dec-2012  |
| DEF          |  DEFJKT      | 23-Dec-2012  |
| DEF          |  DEFGET      | 12-Dec-2012  |
| DEF          |  DEFHTT      | 18-Dec-2012  |
| DEF          |  DEFEET      | 16-Dec-2012  |
| XYZ          |  XYZOOP      | 16-Dec-2012  |
| XYZ          |  XYZIOO      |              |
| XYZ          |  XYZJMN      | 16-Dec-2012  |
| XYZ          |  XYZGHB      |              |
+--------------+--------------+--------------+

INTENDED RESULT :

+------------+------------+
| officecode | audit_status  
+------------+------------+
| ABC        | Complete   |       
| DEF        | Complete   |
| XYZ        | Incomplete |
+------------+------------+

I've done some research and tried pairing group by with a case but i seem to not be getting the results.

SELECT distinct officecode, 
CASE WHEN auditdate IS NULL 
      THEN 'INCOMPLETE'
     WHEN auditdate IS NOT NULL 
      THEN 'COMPLETE'
     END AS AUDIT_STATUS
       FROM A GROUP BY officecode;

Answer 1

COUNT(expr) will count the number of rows where expr is not null, which can be used to figure out which groups have any nulls:

select officecode
     , case when count(*)>count(auditdate) then 'Incomplete' 
            else 'Complete' end audit_status
from foo
group by officecode;

OFFICECODE | AUDIT_STATUS
:--------- | :-----------
DEF        | Complete    
ABC        | Complete    
XYZ        | Incomplete  

dbfiddle here

you can get the same result with nvl2 (or decode, etc):

select officecode
     , case when sum(nvl2(auditdate,0,1))>0 then 'Incomplete' 
            else 'Complete' end audit_status
from foo
group by officecode;

Answer 2

If auditdate has NULL values you could use COUNT(auditdate) <> COUNT(*) because COUNT(auditdate) returns a count of records that has a value.

CREATE TABLE #A
(
    officecode CHAR(3),
    Username   CHAR(6),
    auditdate  DATETIME NULL
);

INSERT INTO #A VALUES ('ABC', 'ABCTOM', '20180101');
INSERT INTO #A VALUES ('ABC', 'ABCPET', '20180101');
INSERT INTO #A VALUES ('ABC', 'ABCLEE', '20180101');
INSERT INTO #A VALUES ('ABC', 'ABCHET', '20180101');
INSERT INTO #A VALUES ('DEF', 'DEFJKT', '20180101');
INSERT INTO #A VALUES ('DEF', 'DEFGET', '20180101');
INSERT INTO #A VALUES ('DEF', 'DEFHTT', '20180101');
INSERT INTO #A VALUES ('DEF', 'DEFEET', '20180101');
INSERT INTO #A VALUES ('XYZ', 'XYZOOP', '20180101');
INSERT INTO #A VALUES ('XYZ', 'XYZIOO', NULL);
INSERT INTO #A VALUES ('XYZ', 'XYZJMN', '20180101');
INSERT INTO #A VALUES ('XYZ', 'XYZGHB', NULL);

SELECT officecode,
       CASE WHEN COUNT(*) <> COUNT(auditdate) THEN 'Incomplete'
            ELSE 'Complete'
            END  Status
FROM   #A
GROUP BY officecode;

DROP TABLE #A;


| officecode |   Status   |
|:----------:|:----------:|
|     ABC    |  Complete  |
|     DEF    |  Complete  |
|     XYZ    | Incomplete |

Answer 3

count(*) returns all rows in a group. count(column_name) returns the number of not null values in a group.

select
  officecode, 
  case when count(*) = count(auditdate) then 'Complete' else 'Incomplete' end 
from
  original_table
group by
  officecode
;