question

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

Finding the overall Utilization by month of claim creation

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

sql-server-generalsql-server-transact-sql
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.

Viorel-1 avatar image
1 Vote"
Viorel-1 answered ArunChandramouli-6978 commented

Check if this query corresponds:

 drop table if exists #local
    
 ;
 with Q as
 (
     select *, eom = EOMONTH(claimcreated) 
     from input
 )
 select 
     eom,
     [month] = format(eom, 'MMM"-"yyyy'),
     utilization = count(case when organizationid <> 'PRACTICE' then 1 end) * 100 / count(organizationid),
     noofclients = (select count(*) from (select distinct ctextid, prid from Q where eom = t.eom) d)
 into #local
 from Q t
 group by eom
    
 declare @cols varchar(max) = 
     stuff((select ', ' + quotename(month) from #local order by eom for xml path('')), 1, 2, '')
    
 declare @sql varchar(max) = concat(
 'select ''utilization'' as [ ], *
 from ( select month, utilization from #local) t
 pivot ( max(utilization) for month in (', @cols, ')) p
 union all
 select ''noofclients'', *
 from ( select month, noofclients from #local) t
 pivot ( max(noofclients) for month in (', @cols, ')) p'
 )
 
 exec (@sql)

 /*
                    Aug-2021   Sep-2021   Oct-2021
      utilization     66          25        61
      noofclients     3            2         5
 */


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

@Viorel-1 : Thank you so much for your help!

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

This is the same question you already asked and I answered. Please see my response to your other thread.

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.

EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered ArunChandramouli-6978 commented

Please also check:

 ;WITH cte
 as(
 SELECT claimcreated ,COUNT(*) noofclients FROM (
 SELECT
 DISTINCT ctextid, prid ,left(claimcreated,7) claimcreated 
 FROM input) d
 GROUP BY claimcreated )
 ,cte2 as(
 SELECT left(claimcreated,7) claimcreated,
 COUNT(CASE WHEN organizationid <> 'PRACTICE' THEN organizationid END)*100/COUNT(organizationid)  utilization
 FROM input
 GROUP BY left(claimcreated,7))
    
 SELECT 'utilization',* FROM (SELECT  c1.claimcreated,utilization 
 FROM cte c1 JOIN cte2 c2 ON c1.claimcreated=c2.claimcreated) t
 PIVOT (MAX(utilization ) FOR claimcreated IN([2021-08],
 [2021-09],
 [2021-10])) p
 UNION ALL
 SELECT 'noofclients',* FROM (SELECT  c1.claimcreated,noofclients  
 FROM cte c1 JOIN cte2 c2 ON c1.claimcreated=c2.claimcreated) t
 PIVOT (MAX(noofclients) FOR claimcreated IN([2021-08],
 [2021-09],
 [2021-10])) p

Output:
145656-image.png


Regards,
Echo


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



image.png (3.5 KiB)
· 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.

@EchoLiu-msft :Really appreciate your help!

0 Votes 0 ·