Finding the utilization percentage for claim creation for different vendors

Hellothere8028 821 Reputation points
2021-11-01T09:51:35.523+00:00

Hi All,

Hope you are doing well!.. There are a variety of organizations that create claims ...this field is represented by the organizationid -the values PRACTICE implies the company itself creates claims ; the values other than PRACTICE implies the vendors creating claims...A claim is created at the level of ctextid , prid and claimid ....Now in my input table there is a field called claimcreated (this indicates when the claim got created)...Now I want to calculate the utilization percentage by claim created month (Utilization is calculated as the total number of claims created by the vendors divided by the total claims created for that company (indicated by the combination of ctextid and prid) -the denominator includes the claims created by the practice and the claims created by the vendors expressed as a percentage rounded to zero decimals

Please find below my input and output tables

Input

create table ##input
(ctextid int,
prid int,
claimid int,
claimcreated datetime2,
organizationid varchar(50),
)

insert into ##input values
('788','91','211','9/16/2021 4:59:18 PM','PRACTICE'),
('788','91','121','9/28/2021 10:32:33 PM','PRACTICE'),
('788','91','311','9/7/2021 8:22:40 PM','PRACTICE'),
('788','91','579','9/8/2021 5:52:27 AM','OG'),
('788','91','637','9/17/2021 11:14:20 AM','ACE'),
('788','91','87638','10/23/2021 12:34:00 AM','OG'),
('788','91','71395','10/20/2021 10:07:31 PM','PRACTICE'),
('788','91','133216','10/17/2021 4:02:15 AM','ACE'),
('788','91','29823','10/25/2021 4:06:38 PM','PRACTICE'),
('788','91','522','10/4/2021 10:51:53 AM','PRACTICE'),
('788','91','23728','10/27/2021 9:25:23 AM','ACE'),
('902','83','832','9/20/2021 10:32:33 PM','PRACTICE'),
('902','83','836','9/28/2021 10:32:33 PM','PRACTICE'),
('902','83','932','9/24/2021 10:32:33 PM','PRACTICE'),
('902','83','567','10/24/2021 10:32:33 PM','OG'),
('902','83','321','10/27/2021 10:32:33 PM','ACE'),
('902','83','2312','10/21/2021 10:32:33 PM','ACE')

OUTPUT

create table ##output
(ctextid int,
prid int,
sep2021 int,
oct 2021 int)

insert into ##output values
('788','91','40','0'),
('902','83','50','100')

Can you please help me here..

Thanks,
Arun

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,711 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
Accepted answer
  1. Tom Phillips 17,716 Reputation points
    2021-11-01T14:28:30.097+00:00

    This should get you started:
    ;with monthlist as (
    SELECT
    ctextid, prid
    ,FORMAT(claimcreated,'yyyyMM') as YearMonth
    ,CASE WHEN organizationid = 'PRACTICE' THEN 1 ELSE 0 END AS PRACTICE_Created
    ,1 as Any_Created
    FROM #input
    ),
    totals as (
    SELECT ctextid, prid,YearMonth,
    SUM(PRACTICE_Created) as Total_PRACTICE_Created,
    SUM(Any_Created) AS Total_Any_Created
    FROM monthlist
    GROUP BY ctextid, prid,YearMonth
    )

    SELECT *
    ,CAST((1-CAST((Total_PRACTICE_Created*1.00)/Total_Any_Created AS DECIMAL(5,2)))*100 AS INT) AS PercentAny
FROM totals
ORDER BY  ctextid, prid,YearMonth
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-11-02T01:57:20.75+00:00

    Hi @Hellothere8028 ，

    Please kindly double check your expected output since I checked from my side and found that two value should be exchanged according to your sample data. Thanks.

    Please refer to below: 

    ;WITH CTE AS (  
select ctextid,prid,FORMAT(claimcreated,'MMMyyyy') as MonthYear  
,CAST(sum(CASE WHEN organizationid = 'PRACTICE' THEN 1 ELSE 0 END) AS FLOAT) AS PRACTICE_Created  
,CAST(count(organizationid) AS FLOAT) Total   
from ##input  
group by ctextid,prid,FORMAT(claimcreated,'MMMyyyy'))  
select * from (   
SELECT ctextid,prid,MonthYear,(1-(PRACTICE_Created/TOTAL))*100 per  
FROM CTE  ) s  
pivot  
(max(per) for MonthYear in ([Sep2021],[Oct2021])) p  
order by ctextid

    OR dynamic way from below: 

    drop table if exists #temp  
  
;with cte as (  
select ctextid,prid,FORMAT(claimcreated,'yyyyMM') as MonthYear  
,CAST(sum(CASE WHEN organizationid = 'PRACTICE' THEN 1 ELSE 0 END) AS FLOAT) AS PRACTICE_Created  
,CAST(count(organizationid) AS FLOAT) Total   
from ##input  
group by ctextid,prid,FORMAT(claimcreated,'yyyyMM'))  
SELECT ctextid,prid,MonthYear,(1-(PRACTICE_Created/TOTAL))*100 per  
into #temp  
FROM CTE  
  
declare @col varchar(max)    
set @col=stuff((select ', ' + quotename(FORMAT(cast(MonthYear+'01' as date),'MMMyyyy')) from (select distinct MonthYear from #temp) a order by MonthYear for xml path('')), 1, 2, '')  
  
declare @sql nvarchar(max)=  
N'select * from (   
select ctextid,prid,FORMAT(cast(MonthYear+''01'' as date),''MMMyyyy'') MonthYear,per from #temp) s pivot  
(max(per) for MonthYear in ('+@col+')) p  
order by ctextid'  
  
exec(@sql)

    Output: 

    ctextid	prid	Sep2021	Oct2021  
788	91	40	50  
902	83	0	100

    Best regards,
    Melissa

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