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