December 23, 2009 at 8:24 am
Hey Everyone... First off, Merry Christmas, or perhaps Happy Holidays to be more inclusive to all traditions!
I have a table that contains about 4 million records of data about a specific claim for a specific patient. Each record in this table also contains 45 fields that may or may not contain a service code or billing code. Sounds good so far! These fields are even numbered...for example code1, code2, code3...code45. Which still sounds fine...however, the sticking point is that the data is not consistently populated in these fields from 1 to 45 chronologically. So...code1 may have data, code4 may have data, and code33 may have data...but all of the other codeX fields do NOT contain data. The problem is that I'm manually generating HL7 messages for use in an HL7 parser by an associate organization to ours, and these codeX fields will need to be processed as PR1|1..., PR1|2..., PR1|3..., etc. until all "available" codes are listed in the HL7 message. So in the example mentioned above, if a code is found in fields Code1, Code4, and Code33, these would appear as PR1|1..., PR1|2..., PR1|3...!
Any ideas on how I can loop through certain fields or a range of fields within a record to determine whether or not the field has data or is NULL without having to hard-code each field...in for example a CASE statement. Could I use System Metadata and explicitly evaluate desired columns by column name since they are distinct? At this point I'm grasping at straws trying to come up with a reasonable solution without wearing out my fingertips coding!
Thanks in advance for any conceptual ideas! If I need to explain further or if I need to provide representive data, please let me know.
thanks again,
Rick Mills, MT(ASCP)
Analyst / Programmer
Indiana Health Information Exchange
rmills@ihie.com
December 23, 2009 at 8:44 am
I think the key is the expected output; if you can accept a single column with a string which contains the values, or do you still have to have up to 45 columns of data?
you can use a neat trick with FOR XML to append all the column values as a comma delimited list, so you'd get only three values in your example, but it depends on your desired output.
Lowell
December 23, 2009 at 8:44 am
First of all: Don't loop! (unless you'll get paid per second your code runs...) 😉
You could use UNPIVOT (see BOL for details) to get the table "flattened".
With the resulting table (or CTE) you could built your result set.
If you'd like to see how I'd do it please provide sample data and expected result as described in the first artilce I reference in my signature.
December 23, 2009 at 12:55 pm
OK...thanks for the suggestions, but I'm still not sure how to achieve the desired output...which in my case would be to manually create an HL7 interface message using T-SQL. This is usually quite easy because I have always received the data in separate records...so I can usually assign a row number to each record and then when compiling the HL7 message, each row number becomes a separate PR1|1... segment. But for some unknown reason the US government has decided that it would be nice to send this data in ONE record but with umpteen fields rather than records. I'm attaching two files. One is a (tab delimited) text file that has the raw data as it appears in our SQL db. The second file is the "Desired Output" which is an HL7 message for each patient...with the appropriate number of PR1 segments containing the codes from the fields labeled PROCXCODE (1-6), and HCPSCDXX (1-45) as long as there is a code in one of those fields. Within the HL7 message the numbering gets re-arranged based on the total number of fields with data. Those code fields without data are skipped. In the OUTPUT FILE, I've given 3 examples of how the HL7 message should be created or configured.
I hope this helps. I've never asked a question where I also needed to provide data files, so I'm hoping that I've provided what those who help out actually need. If not, I'm sure I'll hear about it...be gentle! 😀
Thanks again,
Rick Mills, MT(ASCP)
Analyst / Programmer
Indiana Health Information Exchange
rmills@ihie.com
December 23, 2009 at 4:02 pm
Attached please find an example of how I would do it.
Please note that I didn't completely populate MSH, EVN, PID, and PV1 rows. But it basically would follow the concept as shown below. It's a rough example and doesn't match exactly your output file since I would need more information than I have right now (e.g. column and position within the row related to each "block").
But I guess you can sort it out...
If I didn't misinterpreted your posts you already have a way to get those data in the expected format... (I focussed on the basic concept rather than the string concatenation...)
If you have any questions regarding the code below: just reply.
But: please note that I'll be off until 28th. So somebody else need to jump in - or you'll have to wait 😉
Merry Christmas and enjoy the Holidays (truly hoping you'll find the time to)!!
; WITH
ctePatientFormatted1 as
-- built the "end string" for pg1 and dg1
(
SELECT
membernbr,
claimnbr,
lastname,
firstname,
convert(char(8),dosend,112) + '|||||||||1|||' AS sub,
'' AS blank
FROM Sample_Data
),
ctePatientFormatted2 as
-- built the "master strings" for MSH,EVN, PID, and PV1
(
SELECT
membernbr,
CASE
WHEN t.n = 1 THEN 'MSH'+'|' + membernbr
WHEN t.n = 2 THEN 'EVN'+'|' + claimnbr
WHEN t.n = 3 THEN 'PID'+'|' + lastname
WHEN t.n = 4 THEN 'PV1'+'|' + firstname
ELSE ''
ENDAS result ,
t.n AS sort -- show a blank line after each membernbr
FROM ctePatientFormatted1
CROSS apply
(
SELECT 1 AS N UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 9
) t
),
cteDG1 AS
-- unpivot dg1 data
(
SELECT membernbr, DG1, row_number() OVER (partition BY membernbr ORDER BY membernbr) AS row
FROM
(SELECT membernbr,Diag1Code, Diag2Code, Diag3Code, Diag4Code, Diag5Code,Diag6Code, Diag7Code, Diag8Code, Diag9Code, Diag10Code
FROM Sample_Data) p
UNPIVOT
(DG1 FOR DiagCode IN
(Diag1Code, Diag2Code, Diag3Code, Diag4Code, Diag5Code,Diag6Code, Diag7Code, Diag8Code, Diag9Code, Diag10Code)
)AS unpvt
),
ctePR1 AS
-- unpivot pr1 data limited to 5 cols to show the concept
(
SELECT membernbr, PR1, row_number() OVER (partition BY membernbr ORDER BY membernbr) AS row
FROM
(SELECT membernbr,HCPSCD01,HCPSCD02,HCPSCD03,HCPSCD04,HCPSCD05
FROM Sample_Data) p
UNPIVOT
(PR1 FOR DiagCode IN
(HCPSCD01,HCPSCD02,HCPSCD03,HCPSCD04,HCPSCD05)
)AS unpvt
),
cteMergeCtes as
-- merge dg1 and pr1 data. Note: the [sort] column is used to define the order of final output
(
SELECT membernbr,'DG1'+'|'+cast(row AS varchar(2))+'||'+ dg1 + '^^I9||' AS result, 5 AS sort
FROM cteDG1
UNION ALL
SELECT membernbr,'PR1'+'|'+cast(row AS varchar(2))+'||'+pr1 + '^^HPC||' AS result, 6 AS sort
FROM ctePR1
UNION ALL
SELECT membernbr, result,sort
FROM ctePatientFormatted2
)
-- show final output
SELECT m.result + CASE WHEN sort IN (5,6) THEN p.sub ELSE '' END AS final
FROM cteMergeCtes m
INNER JOIN ctePatientFormatted1 p ON m.membernbr = p.membernbr
ORDER BY m.membernbr,m.sort
Edit: solution slightly modifed to include output for e.g. 'MSH'
December 29, 2009 at 5:55 am
Thanks Imu92 for the detailed solution. Sorry I haven't replied before now, but I too have been out for the Holiday. Clever idea of how to work with pulling the data together. I'm pretty much a rookie at T-SQL when it comes to the logic of applying the use of multiple solutions in one solution...however, thanks to guys like you I'm learning to think outside the proverbial "box".
I may hit you up if I have issues down the road... 🙂
thanks,
Rick Mills, MT(ASCP)
Analyst / Programmer
Indiana Health Information Exchange
rmills@ihie.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply