Counting the number of Diagnosis codes by context ,Specialty and provider

Hellothere8028 721 Reputation points
2021-05-07T05:48:30.323+00:00

Hi Team,

Hope you are doing well!.. I am trying to I am trying to count the number of diagnosis codes by context ,Specialty and provider..Can you please help me here.. Please find below the input and the output tables .
Input table

create table ##input
(contextid int,
visitbillid int ,
specialty varchar (20),
provider varchar (20),
diagnosiscode varchar (1000)
)

insert into ##input values
('78','34','OB','TITLO','Z01419,Z951,E663,M810'),
('78','35','OB','TITLO','Z01419,N952,E663,E559'),
('78','36','OB','TITLO','Z01419,E559,E663,Z1151,Z780,Z86010'),
('78','37','OB','TITLO','Z01411,N952,E663,E559,Z780,Z713'),
('712','89','Medicine','JAGLO','Z00129,Z130,Z1388,Z713,Z6853'),
('712','90','Medicine','JAGLO','Z00129,Z713,Z7182,Z6852'),
('712','91','Medicine','JAGLO','Z00121,H50011,Z713,Z6851,Z7182'),
('712','92','Medicine','JAGLO','H6693,T85628A,Y838')

Output table

create table ##output
(contextid int,
specialty varchar(20),
provider varchar(20),
diagnosiscode varchar(500),
countofdiagnosiscode int)

insert into ##output values

('78','OB','TITLO','Z01419','3'),
('78','OB','TITLO','Z951','1'),
('78','OB','TITLO','E663','4'),
('78','OB','TITLO','M810','1'),
('78','OB','TITLO','N952','2'),
('78','OB','TITLO','E559','3'),
('78','OB','TITLO','Z1151','1'),
('78','OB','TITLO','Z780','2'),
('78','OB','TITLO','286010','1'),
('78','OB','TITLO','Z713','1'),
('712','Medicine','JAGLO','Z00129','2'),
('712','Medicine','JAGLO','Z130','1'),
('712','Medicine','JAGLO','Z1388','1'),
('712','Medicine','JAGLO','Z713','3'),
('712','Medicine','JAGLO','Z6853','1'),
('712','Medicine','JAGLO','Z7182','2'),
('712','Medicine','JAGLO','Z6852','1'),
('712','Medicine','JAGLO','Z00121','1'),
('712','Medicine','JAGLO','H50011','1'),
('712','Medicine','JAGLO','Z6851','1'),
('712','Medicine','JAGLO','Z7182','1'),
('712','Medicine','JAGLO','H6693','1'),
('712','Medicine','JAGLO','T85628A','1'),
('712','Medicine','JAGLO','Y838','1')

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.
8,588 questions
No comments
{count} votes

Accepted answer
  1. Viorel 83,016 Reputation points
    2021-05-07T07:06:55.487+00:00

    Check this query:

    select contextid, specialty, provider, t.value as diagnosiscode , count(*) as countofdiagnosiscode 
    from ##input
    cross apply string_split(diagnosiscode, ',') t
    group by contextid, specialty, provider, t.value
    order by contextid, specialty, provider, diagnosiscode
    

    Prepend 'insert into...' to insert the results to ##output table.


1 additional answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,126 Reputation points Microsoft Employee
    2021-05-07T07:07:04.477+00:00

    Hi @Hellothere8028 ,

    Welcome to Microsoft Q&A!

    Please refer below:

    ;with cte as (  
    select contextid,specialty,provider,value diagnosiscode  
     from ##input  
     cross apply STRING_SPLIT(diagnosiscode, ','))  
     select distinct  contextid,specialty,provider,diagnosiscode  
     ,count(diagnosiscode) over (partition by  contextid,specialty,provider,diagnosiscode) countofdiagnosiscode  
     from cte   
    order by contextid,specialty  
    

    If your version is under SQL Server 2016, you could use user-defined function instead of STRING_SPLIT.

    CREATE FUNCTION [dbo].[SplitString]  
    (  
        @List NVARCHAR(MAX),  
        @Delim VARCHAR(255)  
    )  
    RETURNS TABLE  
    AS  
        RETURN ( SELECT [Value] FROM   
          (   
            SELECT   
              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],  
              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))  
            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)  
              FROM sys.all_objects) AS x  
              WHERE Number <= LEN(@List)  
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim  
          ) AS y  
        );  
    

    Then call this function as below:

    ;with cte as (  
    select contextid,specialty,provider,value diagnosiscode  
     from ##input  
     cross apply [dbo].[SplitString](diagnosiscode, ','))  
     select distinct  contextid,specialty,provider,diagnosiscode  
     ,count(diagnosiscode) over (partition by  contextid,specialty,provider,diagnosiscode) countofdiagnosiscode  
     from cte   
    order by contextid,specialty  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.