2

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;
3

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;
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 |
1

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
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.