How to calculate common and unique occurrences

Question

Hello,

My criteria is to look for value after "." in each row value and calculate the number of unique and common occurrences from the below query.

Looking at substring .123 & .999 and I need cumulative count of these occurences

Other occurences should be count of the ones other than .123 and .999.

And # of unique complaints should be 4. I am getting as 7.

Please assist.

CREATE TABLE #Temp(C_GUID VARCHAR(255), ComplaintID_1 VARCHAR(25),ComplaintID_2 VARCHAR(25)
 , NumOfComplaints INT,Occur_Of_123 INT
 ,Occur_Of_999 INT,OtherOccurence INT
 )

INSERT INTO #Temp(C_GUID, ComplaintID_1)
VALUES('1A2B-4C5E','12.123(a)');

INSERT INTO #Temp(C_GUID, ComplaintID_1)
VALUES('1A2B-4C5E','11.123(b)');
INSERT INTO #Temp(C_GUID, ComplaintID_1)
VALUES('1A2B-4C5E','10.12(p))');
INSERT INTO #Temp(C_GUID, ComplaintID_2)
VALUES('1A2B-4C5E','10.999(b)');
INSERT INTO #Temp(C_GUID, ComplaintID_2)
VALUES('1A2B-4C5E','11.999(a)');
INSERT INTO #Temp(C_GUID, ComplaintID_1)
VALUES('1A2B-4C5E','10.007');
INSERT INTO #Temp(C_GUID, ComplaintID_2)
VALUES('1A2B-4C5E','111.123(a)');


SELECT COUNT(DISTINCT(ComplaintID_1))+ COUNT(DISTINCT(ComplaintID_2)) AS 'NumOf Unique Complaints'
 ,1 AS '# Of .123 Occurences',1 AS '# Of .999 Occurences',1 AS '# Of Other Occurences'
FROM #Temp
GROUP BY C_GUID

DROP TABLE #Temp

Answer 1

Check this query:

;
with U as
(
    select ComplaintID_1 as C
    FROM #Temp
    union 
    select ComplaintID_2
    FROM #Temp
), 
V as
(
    select C, case 
            when a > 0 and b > 0 then substring(C, a, b - a)
            when a > 0 then substring(C, a, len(C) - a + 1) 
            else C end as s,
            a, b
    FROM U
    cross apply (values (charindex('.', C), charindex('(', C))) t(a,b)
)
select 
    count( distinct s) as [NumOf Unique Complaints],
    count( case s when '.123' then s end) as [# Of .123 Occurences],
    count( case s when '.999' then s end) as [# Of .999 Occurences],
    count( case s when '.123' then null when '.999' then null else s end) as [# Of Other Occurences]
from V

It is not clear if you want to group the counts by C_GUID.

Answer 2

Hi @Brian collins ,

Welcome to Microsoft Q&A!

Please also refer below:

;with cte as (  
select C_GUID,ComplaintID_1 as C  
FROM #Temp  
union   
select C_GUID,ComplaintID_2  
FROM #Temp)  
,cte1 as (  
select C_GUID,c,case when CHARINDEX('.',C)>0 then SUBSTRING(C,CHARINDEX('.',C),len(C)-CHARINDEX('.',C)+1) else null end S  
from cte   
where c is not null)  
,cte2 as (  
select C_GUID,C,case when CHARINDEX('(',S)>0 then SUBSTRING(S,1,CHARINDEX('(',S)-1) else S end V  
from cte1)  
select count(distinct V) AS 'NumOf Unique Complaints',  
sum(IIF(V='.123',1,0)) AS '# Of .123 Occurences',  
sum(IIF(V='.999',1,0)) AS '# Of .999 Occurences',  
sum(IIF(V<>'.123' and V<>'.999',1,0))AS '# Of Other Occurences'  
from cte2  
group by C_GUID  

Output:

NumOf Unique Complaints	#Of .123 Occurences	# Of .999 Occurences	# Of Other Occurences  
4	3	2	2  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.