CODEWITHSUNDEEP
sqloracle-database
seth
seth

Reputation: 35

create calculated column to show which column is missing

I have a requirement where I need to bring in calculated column to show if Type A, B or C is missing.

For example, if Service ID 2 has Type A then the calculated column has to say "Type B and C is missing", if Service ID 3 has Type B then the calculated column has to say Type A and C is missing" etc...

I got the calculation to say "Type A B or C is missing" but having hard time to break it down to the specific Type that is missing. I will eventually union this result to few other queries that are similar. Can anyone help with this? 

SELECT ri.service_id, 
       ri.name, 
       ri.creation_date, 
       'Type A B or C is missing' AS missing 
FROM   service ri 
WHERE  ri.special = 'Yes' 
  AND  ri.service_id NOT IN (SELECT DISTINCT ri.service_id 
                             FROM   service ri, 
                                    service_cust rfni, 
                                    cust rfn 
                             WHERE  ri.service_id = rfni.service_id 
                               AND  rfni.cust_id = rfn.cust_id 
                               AND  ri.special = 'Yes' 
                               AND rfn.TYPE IN( 'A', 'B', 'C' ) 


                               GROUP  BY ri.service_id 
                                 HAVING Count(DISTINCT rfn.TYPE) = 3) 

Thanks`
CREATE TABLE SERVICE
(service_id  VARCHAR(50),
name  VARCHAR( 50 ),
special  VARCHAR( 50 )
)

Insert into SERVICE (service_id,name,special) values ('1','Service1','Yes');
Insert into SERVICE (service_id,name,special) values ('2','Service2','Yes');
Insert into SERVICE (service_id,name,special) values ('3','Service3','Yes');
Insert into SERVICE (service_id,name,special) values ('4','Service4','Yes');
Insert into SERVICE (service_id,name,special) values ('5','Service5','Yes');
Insert into SERVICE (service_id,name,special) values ('6','Service6','Yes');
Insert into SERVICE (service_id,name,special) values ('7','Service7','Yes');
Insert into SERVICE (service_id,name,special) values ('8','Service8','Yes');
Insert into SERVICE (service_id,name,special) values ('9','Service9','No');





CREATE TABLE SERVICE_CUST
(cust_id  VARCHAR(50),
service_id  VARCHAR( 50 )
)

Insert into SERVICE_CUST (cust_id,service_id) values ('1','1');
Insert into SERVICE_CUST (cust_id,service_id) values ('2','1');
Insert into SERVICE_CUST (cust_id,service_id) values ('3','1');
Insert into SERVICE_CUST (cust_id,service_id) values ('4','1');
Insert into SERVICE_CUST (cust_id,service_id) values ('5','2');
Insert into SERVICE_CUST (cust_id,service_id) values ('6','3');
Insert into SERVICE_CUST (cust_id,service_id) values ('7','4');
Insert into SERVICE_CUST (cust_id,service_id) values ('8','4');
Insert into SERVICE_CUST (cust_id,service_id) values ('9','5');


CREATE TABLE CUST
(cust_id  VARCHAR(50),
type  VARCHAR( 50 )
)

Insert into CUST (cust_id,type) values ('1','A');
Insert into CUST (cust_id,type) values ('2','B');
Insert into CUST (cust_id,type) values ('3','C');
Insert into CUST (cust_id,type) values ('4','D');
Insert into CUST (cust_id,type) values ('5','A');
Insert into CUST (cust_id,type) values ('6','B');
Insert into CUST (cust_id,type) values ('7','A');
Insert into CUST (cust_id,type) values ('8','B');
Insert into CUST (cust_id,type) values ('9','F');

when I run this query 

select ri.service_id, ri.name, rfn.type
from service ri, service_cust rfni, cust rfn
where ri.service_id = rfni.service_id
and rfni.cust_id = rfn.cust_id 
and  ri.special = 'Yes'

I get

1 Service1 A 1 Service1 B 1 Service1 C 1 Service1 D 2 Service2 A 3 Service3 B 4 Service4 A 4 Service4 B 5 Service5 F

I only need Services that is missing Type A,B or C. If it has all 3 (A,B,C) then don't need to see in results. so results i am looking for is Service2 with text 'Missing B,C' Service3 with text 'Missing A,C' Service4 with text 'Missing C'

i think my 1st part of the original query is not working properly as its bringing in:Service 2,3,4,5,6,7 and 8

select  ri.service_id, ri.name
from service ri 
where ri.special = 'Yes'
and ri.service_id not in (
select distinct ri.service_id
from service ri, service_cust rfni, cust rfn
where ri.service_id = rfni.service_id
and rfni.cust_id = rfn.cust_id 
and  ri.special = 'Yes'
and rfn.type in( 'A','B', 'C')
group by ri.service_id
having count(distinct rfn.type)=3)

thanks for help

`

Upvotes: 0

Views: 43

Answers (1)

Parfait
Parfait

Reputation: 107567

Consider a conditional aggregate query to count the occurrence of types:

SELECT ri.service_id,
       ri.name,
       ri.creation_date,
       SUM(CASE WHEN rfn.TYPE = 'A' THEN 1 ELSE 0 END) AS TypeA_Count,
       SUM(CASE WHEN rfn.TYPE = 'B' THEN 1 ELSE 0 END) AS TypeB_Count,
       SUM(CASE WHEN rfn.TYPE = 'C' THEN 1 ELSE 0 END) AS TypeC_Count
FROM   service ri
LEFT JOIN service_cust rfni 
     ON ri.service_id = rfni.service_id 
LEFT JOIN cust rfn
     ON rfni.cust_id = rfn.cust_id 
WHERE  ri.special = 'Yes' 
  AND  rfn.TYPE IN( 'A', 'B', 'C' ) 
GROUP  BY ri.service_id,
          ri.name,
          ri.creation_date
HAVING COUNT(DISTINCT rfn.type) < 3

And if needing to return a single column, integrate above in a CTE:

WITH cte (
   ...same as above...
)

SELECT cte.service_id,
       cte.name,
       cte.creation_date,
       CASE 
            WHEN TypeA_Count = 0 AND TypeB > 0 AND TypeC_Count > 0 THEN 'Type A is Missing'
            WHEN TypeA_Count = 0 AND TypeB = 0 AND TypeC_Count > 0 THEN 'Type A and B are Missing'
            WHEN TypeA_Count = 0 AND TypeB > 0 AND TypeC_Count = 0 THEN 'Type A and C are Missing'
            WHEN TypeA_Count > 0 AND TypeB = 0 AND TypeC_Count > 0 THEN 'Type B is Missing'
            WHEN TypeA_Count > 0 AND TypeB = 0 AND TypeC_Count = 0 THEN 'Type B and C are Missing'
            WHEN TypeA_Count > 0 AND TypeB > 0 AND TypeC_Count = 0 THEN 'Type C is Missing'
            WHEN TypeA_Count > 0 AND TypeB > 0 AND TypeC_Count > 0 THEN 'None are Missing'
       END Missing
FROM cte

Upvotes: 1

Related Questions