Hi All,
Hope you are doing well!...I am trying to build a dataset that calculates the number of clients (distinct count of (ctextid,prid) by month of claim creation -> claimcreated field) and the utilization (number of claims created by organizationid other than PRACTICE divided by the total number of claims created (by PRACTICE and other vendors).. The claim created is at the level of (ctextid->prid->claimid)... I want to do all reactions by month of claim creation ->( claimcreated).....Please find the input and output DDL below... Can you please help me here...
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')
('567','27','3111','10/5/2021 10:32:33 PM','PRACTICE'),
('567','27','3131','10/7/2021 10:32:33 PM','OG'),
('775','456','221','10/15/2021 10:32:33 PM','ACE'),
('775','489','900','10/18/2021 10:32:33 PM','PRACTICE')
('999','121','78','8/18/2021 10:32:33 PM','PRACTICE'),
('998','1341','889','8/4/2021 10:32:33 PM','OG'),
('965','671','212','8/7/2021 10:32:33 PM','ACE')
OUTPUT
create table output
(yearmonth varchar(40),
utilization int,
noofclients int)
insert into output values
('Aug-21','Sep-21','Oct-21'),
('67','25','62'),
('3','2','5')
Output below:
yearmonth Aug-21 Sep-21 Oct-21
utilization 67 25 62
noofclients 3 2 5
Thanks,
Arun