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

Hellothere8028 821 Reputation points
2021-12-15T17:50:24.167+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,581 Reputation points
    2021-12-16T09:34:43.827+00:00

    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:
    158177-image.png

    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".

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,581 Reputation points
    2021-12-16T07:23:03.227+00:00

    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:
    158056-image.png

    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".