October 29, 2008 at 7:21 am
We have one table with a column named DiagPtr where the data can contain a string
in the format '1,2,3,4' where if the master record for this detail record contains
a value in the columns Diag1 (1), Diag2 (2), Diag3 (3), or Diag4 (4); i.e.
1. The master record has Diag1='174.1', and a Diag2='247.6' with Diag3=NULL and
Diag4=NULL and the detail record would contain either a '1', '2', '1,2' or a '2,1'
2. The master record has Diag1='174.1', and a Diag2='247.6' with Diag3='99012' and
Diag4=NULL and the detail record would contain either a '1', '2', '3', '1,2', '2,3', '1,3',
'1,2,3', '3,2,1', '2,3,1', '1,3,2', '2,1,3'.
Essentially, any permutation of the combination of DIAG1-Diag4 columns that are not null.
Would there be a way to determine if there were master records that didn't have a value
in any column that the detail DiagPtr "pointed" to?
We use this code to essentially get the values:
;WITH Diags (diag1, diag2, diag3, diag4, EncounterID)
AS (SELECT Diag1, diag2, diag3, diag4, EncounterID
FROM ptEncounter)
SELECT d.EncounterDetailID,
DX1 = CASE PARSENAME(REPLACE(DiagPtr, ',', '.'), 4)
WHEN NULL THEN NULL
ELSE
diags.Diag1
END,
DX2 = CASE PARSENAME(REPLACE(DiagPtr, ',', '.'), 3)
WHEN NULL THEN NULL
ELSE
diags.Diag2
END,
DX3 = CASE PARSENAME(REPLACE(DiagPtr, ',', '.'), 2)
WHEN NULL THEN NULL
ELSE
diags.Diag3
END,
DX4 = CASE PARSENAME(REPLACE(DiagPtr, ',', '.'), 1)
WHEN NULL THEN NULL
ELSE
diags.Diag4
END
FROM ptEncounterDetails d
INNER JOIN ptEncounterEncounterDetail eed
ON eed.EncounterDetailID = d.EncounterDetailID
INNER JOIN diags
ON diags.EncounterID = eed.EncounterID
I will post schemas and a subset of values a little later.
October 29, 2008 at 8:22 am
Please check if my solution suites you. I have no table to test the code I provide below, but IMHO it should do. The parsename function you use is not relevant to what you would like to achieve. I have covered cases when you have a number in the master but no corresponding detail. Probably you may have another type of mismatch, which is a detail that is not listed in the master: then you need to modify the code to cover both types of mismatches.
;WITH Diags (diag1, diag2, diag3, diag4, EncounterID)
AS (SELECT Diag1, diag2, diag3, diag4, EncounterID
FROM ptEncounter)
,Mismatches as (
select
EncounterId,
-- Check if field 1 should have a value
case when (DiagPtr like '%1%') and (diags.diag1 is null) then 1 end [Diag1Mismatch],
case when (DiagPtr like '%2%') and (diags.diag2 is null) then 1 end [Diag2Mismatch],
case when (DiagPtr like '%3%') and (diags.diag3 is null) then 1 end [Diag3Mismatch],
case when (DiagPtr like '%4%') and (diags.diag4 is null) then 1 end [Diag4Mismatch]
FROM ptEncounterDetails d
INNER JOIN ptEncounterEncounterDetail eed
ON eed.EncounterDetailID = d.EncounterDetailID
INNER JOIN diags
ON diags.EncounterID = eed.EncounterID
)
select EncounterId
from Mismatches
where ([Diag1Mismatch] = 1) or ([Diag2Mismatch] = 1) or ([Diag3Mismatch] = 1) or ([Diag4Mismatch]=1)
October 29, 2008 at 8:35 am
1976 (10/29/2008)
Probably you may have another type of mismatch, which is a detail that is not listed in the master: then you need to modify the code to cover both types of mismatches.
ParseName does work in this case, however.
Yes, we found several records with '0' in them. We're examining the data now.
Thanks, we'll take a look at the code you provided and follow up.
October 29, 2008 at 8:55 am
With the parsename function you check whether there is a value on a specific position of object name, but then you don't check what the value actually is.
October 30, 2008 at 8:39 am
The code worked as planned.
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply