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

Transact-SQL
1

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

2

Your DDL doesn't work and your output doesn't match the sample data. You can try something like this, but can't verify

select Concat(Month(GoLiveDate),'-',Year(GoLiveDate)) as MMYY
	  ,sum(case when orid = 'PRACTICE' then 0 else 1 end)/(count(1)*1.00) as UtilizationRate
	  ,Count(Distinct(concat(ctextid,'-',provid))) as DistinctProviders
from #input
group by Concat(Month(GoLiveDate),'-',Year(GoLiveDate))
3

Thanks @mike01 !..Appreciate your help!