question

ArunChandramouli-6978 avatar image
0 Votes"
ArunChandramouli-6978 asked MelissaMa-msft commented

Finding the utilization percentage for claim creation for different vendors

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-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ArunChandramouli-6978

Could you please validate provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered ArunChandramouli-6978 commented

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
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Tom!.. Can you please let me know how I can get the output in the following format..

ctextid prid Sep-21 Oct-21
788 91 40 0
902 83 50 100


0 Votes 0 ·

Thanks @TomPhillips-1744 : Really appreciate your help!

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft commented

Hi @ArunChandramouli-6978

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @MelissaMa-msft !...Really appreciate your help..Sorry for the late response...

0 Votes 0 ·

Hi @ArunChandramouli-6978

Thanks for your update.

I found that the output of Tom's query and mine are a little different. Could you please double check which one is better for you? Thanks.

Best regards,
Melissa

0 Votes 0 ·