Finding the overall Utilization by month of claim creation

Hellothere8028 821 Reputation points
2021-11-01T17:38:15.997+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,484 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 110.8K Reputation points
    2021-11-01T19:52:55.087+00:00

    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 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-11-02T06:49:37.98+00:00

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

    1 person found this answer helpful.

  2. Tom Phillips 17,716 Reputation points
    2021-11-01T18:00:51.903+00:00

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

    0 comments No comments