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
;