question

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

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

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-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
0 Votes"
Viorel-1 answered ArunChandramouli-6978 commented

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

Thanks Viorel1!..This works!...Really appreciate your help:)..

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ArunChandramouli-6978 commented

Hi @ArunChandramouli-6978,

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.

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

Thanks MelissaMa-msft !..Really appreciate your help!!..

0 Votes 0 ·