February 16, 2022 at 10:32 pm
I am trying to return all values from the lookup table to columns on the same row for the Diagnosis code.
Here is my query:
SELECT distinct
a.accession_no as "Accession Number",
(ISNULL((select m.code from medical_code m where ai.icd9_id = m.id and ai.sort_ord = 1), '')) + '~' +
(ISNULL((select m.code from medical_code m where ai.icd9_id = m.id and ai.sort_ord = 2), '')) + '~' +
(ISNULL((select m.code from medical_code m where ai.icd9_id = m.id and ai.sort_ord = 3), '')) + '~' +
(ISNULL((select m.code from medical_code m where ai.icd9_id = m.id and ai.sort_ord = 4), '')) + '~' +
(ISNULL((select m.code from medical_code m where ai.icd9_id = m.id and ai.sort_ord = 5), '')) + '~' +
(ISNULL((select m.code from medical_code m where ai.icd9_id = m.id and ai.sort_ord = 6), '')) + '~' +
(ISNULL((select m.code from medical_code m where ai.icd9_id = m.id and ai.sort_ord = 7), '')) + '~' as "Diagnosis Code"
FROM accession_2 a (NOLOCK)
LEFT OUTER JOIN acc_icd9 ai on a.id = ai.acc_id
WHERE
a.accession_no in ('S22-01587')
Here is my query, but it's returning 2 rows instead of 1 row with diagnosis code as code~code~code~code~code~code~code~
or in this case code~code~~~~~
Here are my results:
Accession Number Diagnosis Code
S22-01587 ~N17.9~~~~~~
S22-01587 E11.21~~~~~~~
Thank you for your assistance!
February 16, 2022 at 10:54 pm
You provided no sample data, so no way to test. But something like this should help. If table "accession_2" does not contain duplicate "accession_number"s, you should be able to remove the "distinct".
SELECT distinct
a.accession_no as "Accession Number",
diag_codes."Diagnosis Code"
FROM accession_2 a WITH (NOLOCK)
OUTER APPLY (
SELECT STUFF(
ISNULL(MAX(CASE WHEN ai.sort_ord = 1 THEN '~' + m.code END), '') +
ISNULL(MAX(CASE WHEN ai.sort_ord = 2 THEN '~' + m.code END), '') +
ISNULL(MAX(CASE WHEN ai.sort_ord = 3 THEN '~' + m.code END), '') +
ISNULL(MAX(CASE WHEN ai.sort_ord = 4 THEN '~' + m.code END), '') +
ISNULL(MAX(CASE WHEN ai.sort_ord = 5 THEN '~' + m.code END), '') +
ISNULL(MAX(CASE WHEN ai.sort_ord = 6 THEN '~' + m.code END), '') +
ISNULL(MAX(CASE WHEN ai.sort_ord = 7 THEN '~' + m.code END), '')
, 1, 1, '') AS "Diagnosis Code"
FROM medical_code m
LEFT OUTER JOIN acc_icd9 ai on a.id = ai.acc_id AND m.id = ai.icd9_id AND ai.sort_ord BETWEEN 1 AND 7
) AS diag_codes("Diagnosis Code")
WHERE
a.accession_no in ('S22-01587')
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 16, 2022 at 11:11 pm
>> I am trying to return all values from the lookup table to columns on the same row for the Diagnosis code. <<
Please read any book, and I do mean any book, on relational databases. This is a complete violation of first normal form. It also violates the concept of a tiered architecture. The database tier in a client/server architecture is supposed to return the data in a standardized format the table. If you want to print it in colors or use a fancy font or concatenated together or whatever else, then you do that in a presentation layer.
Again, you failed to post any DDL so we have no idea just what a mess this thing is. You might also start using the ANSI/ISO standard coalesce () instead of the old Sybase ISNULL ().
Please post DDL and follow ANSI/ISO standards when asking for help.
February 17, 2022 at 1:22 pm
You might also want to investigate the aggregate function STRING_AGG(), which should make what I think you want very easy.
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15
February 21, 2022 at 12:22 pm
This is a complete violation of first normal form. It also violates the concept of a tiered architecture.
While that is true, this is a common requirement for medical data. You do not argue with the government if you want to be a Medicare or Medicaid provider.
My question - ICD9?
February 21, 2022 at 12:46 pm
It would obviously be easier to offer help if you'd only provide some kind of test data for us to play around with, and the desired outcome for the test data.
February 21, 2022 at 1:13 pm
International classification of diseases amended – version 9
This is the standard encoding used in medicine. However, I think they might be on version 11 by now. Some of the newer codes included "sucked through a jet engine.", "Bitten by a shark." and some bizarre new diseases.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 21, 2022 at 6:44 pm
Okay, based on your query and your query output I have tried to guess some of your data and created some test tables (I have used declared table variables):
declare @accession_2 table (
Id int,
accession_no varchar(20)
)
insert into @accession_2 (Id, accession_no)
values
(1,'S22-01587'),
(2,'S22-01587')
declare @acc_icd9 table (
acc_id int,
icd9_id int,
sort_ord int
)
insert into @acc_icd9 (acc_id, icd9_id, sort_ord)
values
(1,1,2),
(2,2,1)
declare @medical_code table (
Id int,
Code varchar(20)
)
insert into @medical_code (Id, Code)
values
(1,'E11.21'),
(2,'N17.9')
I obviously don't know if this is a correct representation of your data, but at least I can run your query and arrive at the same output as you:
Accession Number Diagnosis Code
-------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------
S22-01587 ~E11.21~~~~~~
S22-01587 N17.9~~~~~~~
(2 rows affected)
This lets me have some confidence in the data, and based on those data I have come up with this query:
declare @AllowedSortId table (
sortId tinyint
)
insert into @AllowedSortId (sortId)
values
(1),
(2),
(3),
(4),
(5),
(6),
(7)
With Consolidated_accession AS (
SELECT
a.accession_no,
asi.sortId,
isnull(max(m.code),'') as Code
FROM @accession_2 a
cross join @AllowedSortId asi
LEFT OUTER JOIN @acc_icd9 ai on a.id = ai.acc_id and ai.sort_ord=asi.sortId
left outer join @medical_code m on m.Id=ai.icd9_id and ai.sort_ord = asi.sortId
GROUP BY a.accession_no, asi.sortId
)
select
accession_no as "Accession Number",
string_agg(code,'~') WITHIN GROUP (ORDER BY sortId ASC) as "Diagnosis Code"
from Consolidated_accession
WHERE
accession_no in ('S22-01587')
group by accession_no
order by accession_no
The output:
Accession Number Diagnosis Code
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
S22-01587 N17.9~E11.21~~~~~
Maybe this is closer to what you expect?
As you can see I've added an additional table with the allowable sort_ids.
February 22, 2022 at 9:39 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply