Finding the Utilize Rate By Go-live month across claim created months

Question

Hi All,

Hope you are doing well!...I am trying to build the Utilize rate for clients (a client is denoted by the combination of ctextid and provid) by go-live month across the claim created months... (claimcreatedmonth is denoted by the field claimcreated)

For the calculation of utilize rate by go-live month the field to be used is golivedate ( Utilize rate =(Number of records other than PRACTICE in Orid column / Total number of records in Orid column for that particular go-live month)..-Month should extracted from golivedate

Also I need to calculate the count of distinct providers (denoted by the combination of ctextid and provid for that particular golive month)... Please find below the input and output tables and the DDL for the same....Can you please help me here...

INPUT TABLE

create table ##input
(ctextid int,
provid int,
vbillid int,
golivedate datetime2,
orid varchar (50),
claimcreated datetime2)

insert into ##input1 values
('892','2','911','2/16/2021 2:00:31 AM','oc','2/22/2021 2:00:31 AM'),
('488','1','41','2/17/2021 2:00:31 AM','toc','2/22/2021 2:00:31 AM'),
('616','1','90','2/19/2021 2:00:31 AM','kqg','2/22/2021 2:00:31 AM'),
('616','1','832','2/16/2021 2:00:31 AM','PRACTICE','2/22/2021 2:00:31 AM'),
('617','45','58','2/21/2021 2:00:31 AM','oc ','2/22/2021 2:00:31 AM'),
('767','23','9034','2/25/2021 2:00:31 AM','PRACTICE','3/20/2021 2:00:31 AM'),
('892','3','892','2/19/2021 2:00:31 AM','PRACTICE','3/20/2021 2:00:31 AM'),
('945','2','6677','2/10/2021 2:00:31 AM','PRACTICE','3/20/2021 2:00:31 AM'),
('211','1','2453','2/7/2021 2:00:31 AM','oc','3/20/2021 2:00:31 AM'),
('567','2','789','2/9/2021 2:00:31 AM','toc','3/20/2021 2:00:31 AM'),
('892','6','22','3/25/2021 2:00:31 AM','PRACTICE','2/2/2021 2:00:31 AM'),
('488','7','4441','3/19/2021 2:00:31 AM','PRACTICE','2/2/2021 2:00:31 AM'),
('778','1','890','3/10/2021 2:00:31 AM','toc','2/2/2021 2:00:31 AM'),
('778','9','901','3/7/2021 2:00:31 AM','jkl','2/2/2021 2:00:31 AM'),
('623','678','658','3/9/2021 2:00:31 AM','kgq','2/2/2021 2:00:31 AM'),
('892','6','789','3/21/2021 2:00:31 AM','PRACTICE','3/2/2021 2:00:31 AM'),
('488','7','321','3/19/2021 2:00:31 AM','PRACTICE','3/2/2021 2:00:31 AM'),
('778','1','721','3/9/2021 2:00:31 AM','PRACTICE','3/2/2021 2:00:31 AM'),
('778','9','76','3/7/2021 2:00:31 AM','PRACTICE','3/2/2021 2:00:31 AM'),
('623','678','56','3/9/2021 2:00:31 AM','toc','3/2/2021 2:00:31 AM')

OUTPUT TABLE

create table ##output
( clientsgolivemonth varchar(50),
utilizerate feb 2021 int,
utilizerate mar 2021 int,
numberofdistinctproviders int
)

insert into ##output values
('Feb-21','80','40','9'),
('Mar-21','60','20','5')

Thanks,
Arun

Answer 1

Please check:

    ;WITH cte  
    as(SELECT DISTINCT convert(varchar(6),golivedate,112) gmm,  
    convert(varchar(6),claimcreated,112) cmm,  
    COUNT(convert(varchar(6),claimcreated,112)) OVER(PARTITION BY convert(varchar(6),golivedate,112),  
    convert(varchar(6),claimcreated,112)) ncount  
    FROM ##input  
    WHERE orid<>'PRACTICE')  
    ,cte2 as(SELECT DISTINCT convert(varchar(6),claimcreated,112) cmm,  
    COUNT(convert(varchar(6),claimcreated,112)) OVER(PARTITION BY convert(varchar(6),golivedate,112),  
    convert(varchar(6),claimcreated,112)) acount  
    FROM ##input)  
    ,cte3 as(SELECT DISTINCT cmm,COUNT(cmm) OVER(PARTITION BY cmm) [numberofdistinctproviders]   
    FROM   
        (SELECT DISTINCT ctextid,provid,convert(varchar(6),golivedate,112) cmm  
         FROM ##input) t)  
    ,cte4 as(SELECT DISTINCT c1.cmm,[numberofdistinctproviders]  
    FROM cte c1  
    JOIN cte2 c2 ON c1.cmm=c2.cmm  
    JOIN cte3 c3 ON c1.cmm=c3.cmm)  
    ,cte5 as(SELECT c1.cmm,c1.gmm,ncount*100/acount Utilizerate,[numberofdistinctproviders]  
    FROM cte c1  
    JOIN cte2 c2 ON c1.cmm=c2.cmm  
    JOIN cte3 c3 ON c1.cmm=c3.cmm  
    WHERE c1.cmm='202102')  
    ,cte6 as(SELECT c1.cmm,c1.gmm,ncount*100/acount Utilizerate,[numberofdistinctproviders]  
    FROM cte c1  
    JOIN cte2 c2 ON c1.cmm=c2.cmm  
    JOIN cte3 c3 ON c1.cmm=c3.cmm  
    WHERE c1.cmm='202103')  
      
    SELECT c5.gmm,c5.Utilizerate [Utilizerate Feb 2021],  
    c6.Utilizerate [Utilizerate Mar 2021],c4.[numberofdistinctproviders]  
    FROM cte5 c5  
    JOIN cte6 c6 ON c5.gmm=c6.gmm  
    JOIN cte4 c4 ON c5.gmm=c4.cmm  

Output:

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Hi @Hellothere8028 ,

Please check:

;WITH cte  
as(SELECT DISTINCT convert(varchar(6),golivedate,112)  mm,  
COUNT(convert(varchar(6),golivedate,112)) OVER(PARTITION BY convert(varchar(6),golivedate,112)) ncount  
FROM ##input  
WHERE orid<>'PRACTICE')  
,cte2 as(SELECT DISTINCT convert(varchar(6),golivedate,112) mm,  
COUNT(convert(varchar(6),golivedate,112)) OVER(PARTITION BY convert(varchar(6),golivedate,112)) acount  
FROM ##input)  
,cte3 as(SELECT DISTINCT mm,COUNT(mm) OVER(PARTITION BY mm) [numberofdistinctproviders]   
FROM   
    (SELECT DISTINCT ctextid,provid,convert(varchar(6),golivedate,112) mm  
     FROM ##input) t)  
  
SELECT c1.mm,ncount*100/acount Utilizerate,[numberofdistinctproviders]  
FROM cte c1  
JOIN cte2 c2 ON c1.mm=c2.mm  
JOIN cte3 c3 ON c1.mm=c3.mm  

Output:

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".