![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(188.79999999999998, 73%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EH%3C/text%3E%3C/svg%3E)
Developer technologies | Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(236.8, 76%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EEM%3C/text%3E%3C/svg%3E)
In addition, check a possible initial script, for cCode and dCode:
drop table if exists ##input1
drop table if exists ##output1
create table ##input1
(
Billid int,
Ctextid int,
info varchar(max),
codedby varchar(30),
created datetime2
)
insert into ##input1 values
('7111','89321','
[
{
"cCode": "7832",
"Date": "08/26/2020",
"ID": "511",
"description": "holos",
"dgoses": [
{
"description": "disease",
"dCode": "Y564",
"CodeAllId": "8921",
"messages": [
""
],
"sequenceNumber": 1
},
{
"description": "acute pain",
"dCode": "U3321",
"CodeAllId": "33213",
"messages": [
""
],
"sequenceNumber": 2
},
{
"description": "height",
"dCode": "U1111",
"CodeAllId": "33278",
"messages": [
""
],
"sequenceNumber": 3
},
{
"description": "PIDEMIA ",
"dCode": "H8811",
"CodeAllId": "90000",
"messages": [
""
],
"sequenceNumber": 4
}
],
"familyPlan": "",
"hcpc": 5,
"id": "",
"isEPS": false,
"mod": "67",
"originalUnitAmount": "8888",
"type": "CHARGE",
"unitAmount": "9000",
"vId": "90001"
},
{
"cCode": "900114",
"Date": "08/26/2020",
"ID": "523",
"description": "heart valve",
"dgoses": [
{
"description": "Fever",
"dCode": "J8923",
"CodeAllId": "892138",
"messages": [
""
],
"sequenceNumber": 1
}
],
"familyPlan": "",
"hcpc": 1,
"id": "",
"mod": "26",
"originalUnitAmount": "19039",
"type": "CHARGE",
"unitAmount": "1039",
"vId": "5113"
}
]
','cmc5','2020-11-11 13:39:25.922'),
('7111','89321','
[
{
"cCode": "78832",
"Date": "08/26/2020",
"ID": "511",
"description": "holos",
"dgoses": [
{
"description": "disease",
"dCode": "Y564",
"CodeAllId": "8921",
"messages": [
""
],
"sequenceNumber": 1
},
{
"description": "acute pain",
"dCode": "U3321",
"CodeAllId": "33213",
"messages": [
""
],
"sequenceNumber": 2
},
{
"description": "height",
"dCode": "U41111",
"CodeAllId": "33278",
"messages": [
""
],
"sequenceNumber": 3
},
{
"description": "PIDEMIA ",
"dCode": "H8811",
"CodeAllId": "90000",
"messages": [
""
],
"sequenceNumber": 4
}
],
"familyPlan": "",
"hcpc": 8,
"id": "",
"isEPS": false,
"mod": "67",
"originalUnitAmount": "8888",
"type": "CHARGE",
"unitAmount": "9000",
"vId": "90001"
},
{
"cCode": "900114",
"Date": "08/26/2020",
"ID": "523",
"description": "heart valve",
"dgoses": [
{
"description": "Fever",
"dCode": "J8923",
"CodeAllId": "892138",
"messages": [
""
],
"sequenceNumber": 1
}
],
"familyPlan": "",
"hcpc": 1,
"id": "",
"mod": "126",
"originalUnitAmount": "19039",
"type": "CHARGE",
"unitAmount": "1039",
"vId": "5113"
}
]
','cmc6','2020-11-11 13:45:20.383')
create table ##output1
(Billid int,
codedby varchar(30),
ctextid int,
cCodecoder varchar(1000),
cCodeAuditor varchar(1000),
deletedcCode varchar(1000),
addedcCode varchar(1000),
dCodeCoder varchar(1000),
dCodeAuditor varchar(1000),
deleteddCode varchar(1000),
addeddCode varchar(1000),
hcpccoder varchar(1000),
hcpcauditor varchar(1000),
deletedhcpc varchar(1000),
addedhcpc varchar(1000),
modcoder varchar(1000),
modauditor varchar(1000),
deletedmod varchar(1000),
addedmod varchar(1000)
)
Insert into ##output1 values
('7111','cmc5','89321','7832,900114','78832,900114','7832','78832','Y564,U3321,U1111,H8811,J8923','Y564,U3321,U41111,H8811,J8923','U1111','U41111','5,1','8,1','5','8','67,26','67126','26','126')
select *
from ##input1
select *
from ##output1
---
;
with I as
(
select *,
dense_rank() over (order by Billid, Ctextid) as tid,
dense_rank() over (partition by Billid, Ctextid order by Created) as n
from ##input1
),
D as
(
select I.*, mk.[key] as mk, m.*, dk.[key] as dk, d.*
from I
cross apply openjson(info) mk
cross apply openjson(mk.value) with
(
cCode nvarchar(max) '$.cCode',
dgoses nvarchar(max) '$.dgoses' as json
) m
cross apply openjson(dgoses) dk
cross apply openjson(dk.value) with
(
dCode nvarchar(max) '$.dCode'
) d
),
C as
(
select * from D where n = 1
),
A as
(
select * from D where n = 2
)
select
Billid,
codedby,
Ctextid,
(
select string_agg(cCode, ',') within group (order by mk)
from
(
select distinct cCode, mk
from C
where tid = t.tid
) d
) as cCodeCoder,
(
select string_agg(cCode, ',') within group (order by mk)
from
(
select distinct cCode, mk
from A
where tid = t.tid
) d
) as cCodeAuditor,
(
select string_agg(cCode, ',')
from
(
select cCode
from C
where tid = t.tid
except
select cCode
from A
where tid = t.tid
) d
) as deletedcCode,
(
select string_agg(cCode, ',')
from
(
select cCode
from A
where tid = t.tid
except
select cCode
from C
where tid = t.tid
) d
) as addedcCode,
(
select string_agg(dCode, ',') within group (order by mk, dk)
from
(
select distinct dCode, mk, dk
from C
where tid = t.tid
) d
) as dCodeCoder,
(
select string_agg(dCode, ',') within group (order by mk, dk)
from
(
select distinct dCode, mk, dk
from A
where tid = t.tid
) d
) as dCodeAuditor,
(
select string_agg(dCode, ',')
from
(
select dCode
from C
where tid = t.tid
except
select dCode
from A
where tid = t.tid
) d
) as deleteddCode,
(
select string_agg(dCode, ',')
from
(
select dCode
from A
where tid = t.tid
except
select dCode
from C
where tid = t.tid
) d
) as addeddCode
from I as t
where n = 1
Extend it for other data, or find a more compact approach.
