We're occasionally getting an error related to quantitative syphilis results
Falanga, Rod, DOH
(I'm sorry of this topic has been covered already. I new to Google Groups, and it's taken me a while just to find out how to send this message.)
I'm a new employee of the New Mexico Department of Health. I've been put onto the team that works with PRISM. I'm really new to PRISM. It's my understanding that once a week we produce a file which gets sent to the CDC, that's related to something called NETSS Transmission. The format of that file hasn't changed since 2013 (I've checked back that far), but now for some reason the CDC has recently begun to have a problem with it. Since the format hasn't changed and they were accepting it fine for the last few years I have to conclude that something has changed at the CDC's end. But on Sunday it's going to do its thing again and produce that NETSS Transmission file. There may be data in it which the CDC will not like again. When the CDC doesn't like it there will be errors that read like this:
"QUANTITATIVE SYPHILIS TEST REULTS [COLUMN(S) 184-189] has an invalid value of '9999'"
In my investigation I believe I've found what the problem is. It's related to the SSIS package named NETSSTransmit which produces the NETSSTransmission.txt file. Attached to this email is what we've got for that file. The issue is at line 40. That line reads:
) AS VARCHAR(187))
I've found that it is truncating the last column included in the SELECT statement. The last column is SYPHILIS_TITER. When that column has "999999"in our database the SSIS package cuts it off and only putting in "9999". Thus the error.
So, do we have too old of a NETSSTransmit SSIS package? Or is there something new that's changed at CDC's end, which makes a once acceptable NETSSTransmission.txt file now no longer acceptable?
Rod Falanga, NM Department of Health
Mary White - Florida
- Look at the table Prism.NETSSTransmission and tell me the length of the column SYPHILIS_TITER. It should be char(6). If it is char(4), there is our problem.
- Look at the table Prism.NETSSTransmissionHold and tell me the length of the column SYPHILIS_TITER. It should be char(6). If it is char(4), there is our problem.
- Look at the stored procedure Prism.usp_BatchNETSS. Do a find on either SYPHILISTITER or SYPHILIS_TITER. See the length of the CAST expression to see if it is CHAR(6).
- Look at the stored procedure Prism.usp_BatchNETSS. Do a find on NETSSVERSION. Do you have this field?
- If in fact your SSIS package has the CAST as VARCHAR(187), that is even a bigger issue as that is not a size that is right for any version of the NETSS. If you are running the January 2011 version, it should have the following lengths:
- RECTYPE = 'D' should be CAST as VARCHAR(60)
- EVENT = '10316' should be CAST as VARCHAR(256)
- Verification Records should be CAST as VARCHAR(60)
- Everything else (regular records) should be CAST as VARCHAR(191)
- If you are running the July 2013 version:
- RECTYPE = 'D' should be CAST as VARCHAR(60)
- EVENT = '10316' should be CAST as VARCHAR(309)
- Verification Records should be CAST as VARCHAR(60)
- Everything else (regular records) should be CAST as VARCHAR(191)
- I think you are running a combination of the regular records (Syphilis, Chlamydia and Gonorrhea) as the July 2013 version and the Congenital Syphilis (EVENT = '10316') as January 2011. I just recently updated the Congenital Syphilis to the January 2014 version.
Falanga, Rod, DOH
Mary,
Thank you for responding to my questions. Here’s the answers to the questions you brought up:
· The SYPHILIS_TITER column in Prism.NETSSTransmission table is a CHAR[6], nullable.
· The SYPHILIS_TITER column in the Prism.NETSSTransmissionHold table is a CHAR[6], nullable.
· Concerning the SP Prism.usp_BatchNETSS here’s what I found:
o For SYPHILIS_TITER it has CAST of CHAR(4)
o For SYPHILISTITER it has a CAST as CHAR(6)
o For NETSSVERSION it is assigned a value of ‘03’
· Concerning your comments about our SSIS package, I have double checked. Yes the CAST to VARCHAR(187) is what is in our package. I’ve no explanation for that. As I mentioned I noticed that it was truncating the value of SYPHILIS_TITER from the Prism.NETSSTransmission table. But I also noticed that there’s no version number after that SYPHILIS_TITER value. And being new to this job I’ve no idea what the version number should be, although that January 2014 document from the CD suggests the latest is ‘04’.
o When there’s RECTYPE of ‘D’ it is CAST as VARCHAR(60)
o When EVENT is ‘10316’ it is CAST as VARCHAR(255)
o I don’t know enough about the system to know what a verification record is, so I’m sorry I can’t tell you how they’re cast.
I’ll pass along the changes to our SSIS package that you’ve recommended we use. However I’m concerned that there may be other discrepancies, like the SP named Prism.usp_BatchNETSS has some values correct, but other values incorrect.
Rod Falanga, NM Department of Health
--
You received this message because you are subscribed to the Google Groups "PRISM Friends" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
prism-friend...@googlegroups.com.
To post to this group, send email to
prism-...@googlegroups.com.
Visit this group at http://groups.google.com/group/prism-friends.
For more options, visit https://groups.google.com/d/optout.
Mary White
On Thursday, April 23, 2015 at 6:02:43 PM UTC-4, Falanga, Rod, DOH wrote:
Rod Falanga
Falanga, Rod, DOH
The most recent comment at the top of the usp_BatchNETSS stored procedure is dated 5/9/2013.
Now, just to double check to make certain I haven’t made a mistake here’s the relevant SQL code snippet from that stored procedure which is uses varchar(4) for SYPHILIS_TITER (I’ve highlighted the relevant line in bold):
INSERT INTO [Prism].[NETSSTransmissionHold]
([ID_Profile], [ID_FieldRecord], [ID_Interview], [ID_Lab], [IN_Transmit]
, [RECTYPE], [UPDATE], [STATE], [YEAR], [CASEID], [SITE]
, [WEEK], [EVENT], [COUNT], [COUNTY], [BIRTHDATE], [AGE], [AGETYPE], [SEX]
, [RACE], [HISPANIC], [EVENTDATE], [DATETYPE], [CASE_STATUS], [IMPORTED]
, [OUTBREAK], [FUTURE], [INFOSRCE], [DETECTED], [ZIP], [CITY], [PID]
, [PREGNANT], [ORIGIN], [DX_DATE], [SPECIMEN_SITE], [SPEC_DATE], [NEURO]
, [INTERVIEW], [PARTNER]
, [RACE_INDIAN], [RACE_ASIAN], [RACE_BLACK], [RACE_PACIFIC], [RACE_WHITE]
, [RACE_OTHER], [RACE_REFUSED], [RACE_UNKNOWN], [HISPANIC_LATINO]
, [CENSUS_TRACT], [STD_IMPORT], [EXAM_DATE], [FIRST_REPORT_DATE]
, [TREATMENT_DATE], [INITIAL_REPORT_DATE], [HIV_STATUS]
, [SEX_WITH_MALE], [SEX_WITH_FEMALE], [SEX_WITH_ANON], [SEX_WITH_IDU]
, [SEX_WHILE_HIGH], [SEX_FOR_DRUGS], [SEX_WITH_MSM], [IDU], [CRACK]
, [COCAINE], [HEROIN], [METH], [NITRATES], [ED_MEDICATION], [OTHER_DRUG]
, [NO_DRUG], [INCARCERATED], [STD_HISTORY], [SEX_INTERNET], [SEX_PARTNERS]
, [LESION_ANUS], [LESION_PENIS], [LESION_SCROTUM], [LESION_VAGINA]
, [LESION_CERVIX], [LESION_NASAL], [LESION_MOUTH], [LESION_EYE]
, [LESION_HEAD], [LESION_TORSO], [LESION_EXTREM], [NO_LESION]
, [LESION_OTHER], [LESION_UNKNOWN], [TYPE_NONTREP_TEST], [SYPHILIS_TITER]
, [MZIP], [FUTURE2]
, [SENTINEL], [MSTATE], [MCOUNTY], [MCITY], [MBIRTH], [MARITAL], [LMP]
, [PRENATAL], [PNCDATE1], [PNCNUM], [NONTREP], [DATEA], [RESULTA], [DATEB]
, [RESULTB], [DATEC], [RESULTC], [DATED], [RESULTD], [TITERA], [TREPONEM]
, [LESIONS], [LASTREAT], [TXADQBEF], [TXADQDUR], [RESPAPPR], [VITAL]
, [DEATHDAT], [BIRTHWT], [REACSTS], [REACDATE], [SIGNSCS], [DARKFLD]
, [DFA], [IGM], [XRAYS], [CSFVDRL], [CSFCOUNT], [TREATED], [CLASS]
, [ID126], [VERSION], [TITERB], [TITERC], [TITERD], [TREATDAT], [INFTITER]
, [AMIND], [ASIAN], [BLACK], [WHITE], [NAHAW], [RACEOTH], [RACEUNK]
, [MCOUNTRY], [REACTREP], [RTDATE], [NETSSVersion])
SELECT f.ID_Profile -- baby's profile id
, f.ID_FieldRecord -- baby's field record
, f.ID_Interview -- baby's interview (if there is one)
, LabMin.ID_Lab -- mother's lab (if there is one)
, [IN_Transmit] = 'X'
, [RECTYPE] = 'M'
, [UPDATE] = '9'
, [STATE] = Prism.fn_GetFIPSState()
, [YEAR] = Prism.fn_GetCDCMMWRInfo(fh.DT_Report,'Y')
, [CASEID] = NULL
, [SITE] = 'S02'
, [WEEK] = Prism.fn_GetCDCMMWRInfo(fh.DT_Report,'W')
, [EVENT] = d.CD_NNDSS
, [COUNT] = '00001'
, [COUNTY] =
CASE
WHEN fh.DS_County IS NOT NULL
THEN Prism.fn_GetFIPSForCounty(f.DS_CountyToCDC)
ELSE '999'
END
, [BIRTHDATE] =
CASE
WHEN p.DT_Birth IS NULL
THEN '99999999'
ELSE CAST(REPLACE(CONVERT(char(10), p.DT_Birth, 121),'-','') AS CHAR(8))
END
, [AGE] =
CASE
WHEN c.AM_GestationAgeWeeks IS NULL
THEN '999'
ELSE CAST(RIGHT('00' + CAST(c.AM_GestationAgeWeeks AS VARCHAR),3) AS CHAR(3))
END
, [AGETYPE] =
CASE
WHEN c.AM_GestationAgeWeeks IS NULL
THEN '9'
ELSE '2'
END
, [SEX] =
CASE
WHEN p.CD_Gender IS NULL
THEN '9'
ELSE CAST(Prism.fn_GetNETSSForCode('CD_Gender',p.CD_Gender) AS CHAR(1))
END
, [RACE] =
CASE
WHEN pm.CD_Race IS NOT NULL
THEN CAST(Prism.fn_GetNETSSForCode('CD_Race',pm.CD_Race) AS CHAR(1))
WHEN p.CD_Race IS NOT NULL
THEN CAST(Prism.fn_GetNETSSForCode('CD_Race',p.CD_Race) AS CHAR(1))
ELSE '9'
END
, [HISPANIC] =
CASE
WHEN pm.CD_Ethnicity IS NOT NULL AND pm.CD_Ethnicity = 'H'
THEN '1'
WHEN pm.CD_Ethnicity IS NOT NULL AND pm.CD_Ethnicity = 'NH'
THEN '2'
WHEN pm.CD_Ethnicity IS NOT NULL AND pm.CD_Ethnicity = 'R'
THEN '9'
WHEN pm.CD_Ethnicity IS NOT NULL AND pm.CD_Ethnicity = 'U'
THEN '9'
WHEN p.CD_Ethnicity IS NOT NULL AND p.CD_Ethnicity = 'H'
THEN '1'
WHEN p.CD_Ethnicity IS NOT NULL AND p.CD_Ethnicity = 'NH'
THEN '2'
WHEN p.CD_Ethnicity IS NOT NULL AND p.CD_Ethnicity = 'R'
THEN '9'
WHEN p.CD_Ethnicity IS NOT NULL AND p.CD_Ethnicity = 'U'
THEN '9'
ELSE '9'
END
, [EVENTDATE] = CONVERT(CHAR(6),fh.DT_Report,12)
, [DATETYPE] = '4'
, [CASE_STATUS] =
CASE
WHEN Prism.fn_GetCongenitalAnswer(c.ID_Profile,199,1,'N') = '1'
THEN '2'
WHEN Prism.fn_GetCongenitalAnswer(c.ID_Profile,199,1,'N') IN ('2','3','4')
THEN '1'
ELSE '9'
END
, [IMPORTED] = '9'
, [OUTBREAK] = '2'
, [FUTURE] = '99999'
, [INFOSRCE] =
CASE
WHEN LabMin.ID_ProviderOrdering IS NULL AND LabMin.DS_ProviderOrdering IS NULL
THEN '99'
WHEN Prism.fn_GetNETSSForCode('CD_ReferralService',im.CD_ReferralService) IS NOT NULL
THEN CAST(Prism.fn_GetNETSSForCode('CD_ReferralService',im.CD_ReferralService) AS CHAR(2))
WHEN LabMin.ID_ProviderOrdering IS NULL AND LabMin.DS_ProviderOrdering IS NOT NULL
AND CHARINDEX('Type: ', LabMin.DS_ProviderOrdering) <> 0
AND Prism.fn_GetNETSSForCode('CD_ProviderType',SUBSTRING(LabMin.DS_ProviderOrdering, (CHARINDEX('Type: ', LabMin.DS_ProviderOrdering) + 6),2)) IS NOT NULL
THEN CAST(Prism.fn_GetNETSSForCode('CD_ProviderType',SUBSTRING(LabMin.DS_ProviderOrdering, (CHARINDEX('Type: ', LabMin.DS_ProviderOrdering) + 6),2)) AS CHAR(2))
WHEN Prism.fn_GetNETSSForCode('CD_ProviderType',prov.CD_ProviderType) IS NOT NULL
THEN CAST(Prism.fn_GetNETSSForCode('CD_ProviderType',prov.CD_ProviderType) AS CHAR(2))
ELSE '99'
END
, [DETECTED] =
CASE
WHEN im.CD_DetectionMethod IS NULL
THEN '88'
ELSE im.CD_DetectionMethod
END
, [ZIP] = CAST(' ' AS CHAR(5))
, [CITY] = CAST(' ' AS CHAR(4))
, [PID] = CAST(' ' AS CHAR(1))
, [PREGNANT] = CAST(' ' AS CHAR(1))
, [ORIGIN] = CAST(' ' AS CHAR(1))
, [DX_DATE] = CAST(' ' AS CHAR(8))
, [SPECIMEN_SITE] = CAST(' ' AS CHAR(2))
, [SPEC_DATE] = CAST(' ' AS CHAR(8))
, [NEURO] = CAST(' ' AS CHAR(1))
, [INTERVIEW] = CAST(' ' AS CHAR(1))
, [PARTNER] = CAST(' ' AS CHAR(1))
-- ================================= FIELDS ADDED JANUARY 2008
, [RACE_INDIAN] = CAST(' ' AS CHAR(1))
, [RACE_ASIAN] = CAST(' ' AS CHAR(1))
, [RACE_BLACK] = CAST(' ' AS CHAR(1))
, [RACE_PACIFIC] = CAST(' ' AS CHAR(1))
, [RACE_WHITE] = CAST(' ' AS CHAR(1))
, [RACE_OTHER] = CAST(' ' AS CHAR(1))
, [RACE_REFUSED] = CAST(' ' AS CHAR(1))
, [RACE_UNKNOWN] = CAST(' ' AS CHAR(1))
, [HISPANIC_LATINO] = CAST(' ' AS CHAR(1))
, [CENSUS_TRACT] = CAST(' ' AS CHAR(6))
, [STDIMPORT] = CAST(COALESCE((SELECT CodeImported.CD_NETSS
FROM Prism.Codes CodeImported WITH(NOLOCK)
WHERE CodeImported.CD_Type = 'Imported'
AND CodeImported.CD_Value = f.CD_Imported),'U') AS CHAR(1))
, [EXAM_DATE] = CAST(' ' AS CHAR(8))
, [FIRST_REPORT_DATE] = CAST(' ' AS CHAR(8))
, [TREATMENT_DATE] = CAST(' ' AS CHAR(8))
, [INITIAL_REPORT_DATE] = CAST(' ' AS CHAR(8))
, [HIV_STATUS] = CAST(' ' AS CHAR(1))
, [SEX_WITH_MALE] = CAST(' ' AS CHAR(1))
, [SEX_WITH_FEMALE] = CAST(' ' AS CHAR(1))
, [SEX_WITH_ANON] = CAST(' ' AS CHAR(1))
, [SEX_WITH_IDU] = CAST(' ' AS CHAR(1))
, [SEX_WHILE_HIGH] = CAST(' ' AS CHAR(1))
, [SEX_FOR_DRUGS] = CAST(' ' AS CHAR(1))
, [SEX_WITH_MSM] = CAST(' ' AS CHAR(1))
, [IDU] = CAST(' ' AS CHAR(1))
, [CRACK] = CAST(' ' AS CHAR(1))
, [COCAINE] = CAST(' ' AS CHAR(1))
, [HEROIN] = CAST(' ' AS CHAR(1))
, [METH] = CAST(' ' AS CHAR(1))
, [NITRATES] = CAST(' ' AS CHAR(1))
, [ED_MEDICATION] = CAST(' ' AS CHAR(1))
, [OTHER_DRUG] = CAST(' ' AS CHAR(1))
, [NO_DRUG] = CAST(' ' AS CHAR(1))
, [INCARCERATED] = CAST(' ' AS CHAR(1))
, [STD_HISTORY] = CAST(' ' AS CHAR(1))
, [SEX_INTERNET] = CAST(' ' AS CHAR(1))
, [SEX_PARTNERS] = CAST(' ' AS CHAR(3))
, [LESION_ANUS] = CAST(' ' AS CHAR(1))
, [LESION_PENIS] = CAST(' ' AS CHAR(1))
, [LESION_SCROTUM] = CAST(' ' AS CHAR(1))
, [LESION_VAGINA] = CAST(' ' AS CHAR(1))
, [LESION_CERVIX] = CAST(' ' AS CHAR(1))
, [LESION_NASAL] = CAST(' ' AS CHAR(1))
, [LESION_MOUTH] = CAST(' ' AS CHAR(1))
, [LESION_EYE] = CAST(' ' AS CHAR(1))
, [LESION_HEAD] = CAST(' ' AS CHAR(1))
, [LESION_TORSO] = CAST(' ' AS CHAR(1))
, [LESION_EXTREM] = CAST(' ' AS CHAR(1))
, [NO_LESION] = CAST(' ' AS CHAR(1))
, [LESION_OTHER] = CAST(' ' AS CHAR(1))
, [LESION_UNKNOWN] = CAST(' ' AS CHAR(1))
, [TYPE_NONTREP_TEST] = CAST(' ' AS CHAR(1))
, [SYPHILIS_TITER] = CAST(' ' AS CHAR(4))
-- ===================== CONGENITAL FIELDS
, [MZIP] =
CASE
WHEN fm.DS_ZipToCDC IS NOT NULL
THEN fm.DS_ZipToCDC
WHEN pm.DS_Zip IS NOT NULL
THEN SUBSTRING(pm.DS_Zip,1,5)
ELSE '99999'
END
, [FUTURE2] = CAST(' ' AS CHAR(10))
, [SENTINEL] = CAST(' ' AS CHAR(1))
, [MSTATE] =
CASE
WHEN pm.DS_State IS NOT NULL
THEN Prism.fn_GetNETSSForCode('DS_State',pm.DS_State)
ELSE '97'
END
, [MCOUNTY] =
CASE
WHEN pm.DS_State = 'Mexico'
THEN '998'
WHEN fm.DS_CountyToCDC IS NOT NULL AND fm.DS_CountyToCDC <> 'OOJ'
THEN Prism.fn_GetFIPSForCounty(fm.DS_CountyToCDC)
WHEN fm.DS_DispositionCounty IS NOT NULL AND fm.DS_DispositionCounty <> 'OOJ'
THEN Prism.fn_GetFIPSForCounty(fm.DS_DispositionCounty)
WHEN pm.DS_County IS NOT NULL AND pm.DS_County <> 'OOJ'
THEN Prism.fn_GetFIPSForCounty(pm.DS_County)
ELSE '997'
END
, [MCITY] = CAST(' ' AS CHAR(4))
, [MBIRTH] =
CASE
WHEN pm.DT_Birth IS NULL
THEN ' '
ELSE CAST(REPLACE(CONVERT(char(10), pm.DT_Birth, 121),'-','') AS CHAR(8))
END
, [MARITAL] =
CASE
WHEN pm.CD_MaritalStatus IS NULL
THEN '9'
ELSE Prism.fn_GetNETSSForCode('CD_MaritalStatus',pm.CD_MaritalStatus)
END
, [LMP] =
CASE
WHEN m.DT_LastMenstrualBeforeDelivery IS NOT NULL
THEN CAST(REPLACE(CONVERT(char(10), m.DT_LastMenstrualBeforeDelivery, 121),'-','') AS CHAR(8))
WHEN m.DT_LastMenstrual IS NOT NULL
THEN CAST(REPLACE(CONVERT(char(10), m.DT_LastMenstrual, 121),'-','') AS CHAR(8))
ELSE ' '
END
, [PRENATAL] =
CASE
WHEN m.IN_PrenatalCare IS NULL
THEN ' '
ELSE Prism.fn_GetNETSSForCode('IN_PrenatalCare',m.IN_PrenatalCare)
END
, [PNCDATE1] =
CASE
WHEN m.DT_PrenatalFirstVisit IS NULL
THEN ' '
ELSE CAST(REPLACE(CONVERT(char(10), m.DT_PrenatalFirstVisit, 121),'-','') AS CHAR(8))
END
, [PNCNUM] =
CASE
WHEN m.NO_PrenatalVisits IS NULL
THEN ' '
ELSE CAST(RIGHT('00' + CAST(m.NO_PrenatalVisits AS VARCHAR),2) AS CHAR(2))
END
, [NONTREP] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,122,1,'N') IS NULL
THEN ' '
ELSE Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,122,1,'N')
END
, [DATEA] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,123,1,'DL') IS NULL
OR Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,123,1,'R') = '9'
THEN ' '
ELSE CAST(Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,123,1,'DL') AS CHAR(8))
END
, [RESULTA] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,123,1,'R') IS NULL
OR Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,123,1,'R') = '9'
THEN ' '
ELSE CAST(Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,123,1,'R') AS CHAR(1))
END
, [DATEB] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,132,1,'DL') IS NULL
OR Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,132,1,'R') = '9'
THEN ' '
ELSE CAST(Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,132,1,'DL') AS CHAR(8))
END
, [RESULTB] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,132,1,'R') IS NULL
OR Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,132,1,'R') = '9'
THEN ' '
ELSE CAST(Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,132,1,'R') AS CHAR(1))
END
, [DATEC] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,141,1,'DL') IS NULL
OR Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,141,1,'R') = '9'
THEN ' '
ELSE CAST(Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,141,1,'DL') AS CHAR(8))
END
, [RESULTC] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,141,1,'R') IS NULL
OR Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,141,1,'R') = '9'
THEN ' '
ELSE CAST(Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,141,1,'R') AS CHAR(1))
END
, [DATED] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,150,1,'DL') IS NULL
OR Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,150,1,'R') = '9'
THEN ' '
ELSE CAST(Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,150,1,'DL') AS CHAR(8))
END
, [RESULTD] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,150,1,'R') IS NULL
OR Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,150,1,'R') = '9'
THEN ' '
ELSE CAST(Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,150,1,'R') AS CHAR(1))
END
, [TITERA] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,123,1,'T') IS NULL
OR Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,123,1,'R') = '9'
THEN ' '
ELSE CAST(Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,123,1,'T') AS CHAR(4))
END
, [TREPONEM] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,163,1,'N') IS NULL
THEN ' '
ELSE Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,163,1,'N')
END
, [LESIONS] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,164,1,'N') IS NULL
THEN ' '
ELSE Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,164,1,'N')
END
, [LASTREAT] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,165,1,'N') IS NULL
THEN ' '
ELSE Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,165,1,'N')
END
, [TXADQBEF] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,165,1,'N') IS NOT NULL
AND Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,165,1,'N') IN ('2','3','9')
THEN ' '
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,166,1,'N') IS NULL
THEN ' '
ELSE Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,166,1,'N')
END
, [TXADQDUR] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,165,1,'N') IS NOT NULL
AND Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,165,1,'N') IN ('1','3','9')
THEN ' '
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,167,1,'N') IS NULL
THEN ' '
ELSE Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,167,1,'N')
END
, [RESPAPPR] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,168,1,'N') IS NULL
THEN ' '
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,166,1,'N') IS NOT NULL
AND Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,166,1,'N') = '1'
THEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,168,1,'N')
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,167,1,'N') IS NOT NULL
AND Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,167,1,'N') = '1'
THEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,168,1,'N')
ELSE ' '
END
, [VITAL] =
CASE
WHEN c.DS_BabyStatus IS NULL
THEN '9'
ELSE Prism.fn_GetNETSSForCode('DS_BabyStatus',c.DS_BabyStatus)
END
, [DEATHDAT] =
CASE
WHEN p.DT_Death IS NULL OR Prism.fn_GetNETSSForCode('DS_BabyStatus',c.DS_BabyStatus) = '3'
THEN ' '
ELSE CAST(REPLACE(CONVERT(char(10), p.DT_Death, 121),'-','') AS CHAR(8))
END
, [BIRTHWT] =
CASE
WHEN c.AM_WeightGrams IS NULL
THEN ' '
ELSE CAST(RIGHT('0000' + CAST(c.AM_WeightGrams AS VARCHAR),4) AS CHAR(4))
END
, [REACSTS] =
CASE
WHEN Prism.fn_GetCongenitalAnswer(p.ID_Profile,182,1,'N') IS NULL
THEN ' '
ELSE Prism.fn_GetCongenitalAnswer(p.ID_Profile,182,1,'N')
END
, [REACDATE] =
CASE
WHEN Prism.fn_GetCongenitalAnswer(p.ID_Profile,182,1,'DL') IS NULL
THEN ' '
ELSE CAST(Prism.fn_GetCongenitalAnswer(p.ID_Profile,182,1,'DL') AS CHAR(8))
END
, [SIGNSCS] =
CASE
WHEN Prism.fn_GetCongenitalAnswer(p.ID_Profile,191,1,'N') IS NULL
THEN ' '
ELSE Prism.fn_GetCongenitalAnswer(p.ID_Profile,191,1,'N')
END
, [DARKFLD] =
CASE
WHEN Prism.fn_GetCongenitalAnswer(p.ID_Profile,192,1,'N') IS NULL
THEN ' '
ELSE Prism.fn_GetCongenitalAnswer(p.ID_Profile,192,1,'N')
END
, [DFA] = ' '
, [IGM] =
CASE
WHEN Prism.fn_GetCongenitalAnswer(p.ID_Profile,194,1,'N') IS NULL
THEN ' '
ELSE Prism.fn_GetCongenitalAnswer(p.ID_Profile,194,1,'N')
END
, [XRAYS] =
CASE
WHEN Prism.fn_GetCongenitalAnswer(p.ID_Profile,195,1,'N') IS NULL
THEN ' '
ELSE Prism.fn_GetCongenitalAnswer(p.ID_Profile,195,1,'N')
END
, [CSFVDRL] =
CASE
WHEN Prism.fn_GetCongenitalAnswer(p.ID_Profile,196,1,'N') IS NULL
THEN ' '
ELSE Prism.fn_GetCongenitalAnswer(p.ID_Profile,196,1,'N')
END
, [CSFCOUNT] =
CASE
WHEN Prism.fn_GetCongenitalAnswer(p.ID_Profile,197,1,'N') IS NULL
THEN ' '
ELSE Prism.fn_GetCongenitalAnswer(p.ID_Profile,197,1,'N')
END
, [TREATED] =
CASE
WHEN Prism.fn_GetCongenitalAnswer(p.ID_Profile,198,1,'N') IS NULL
THEN ' '
ELSE Prism.fn_GetCongenitalAnswer(p.ID_Profile,198,1,'N')
END
, [CLASS] =
CASE
WHEN Prism.fn_GetCongenitalAnswer(p.ID_Profile,199,1,'N') IS NULL
THEN '1'
ELSE Prism.fn_GetCongenitalAnswer(p.ID_Profile,199,1,'N')
END
, [ID126] = CAST(' ' AS CHAR(7))
, [VERSION] = CAST('04-2010' AS CHAR(7))
, [TITERB] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,132,1,'T') IS NULL
OR Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,132,1,'R') = '9'
THEN ' '
ELSE CAST(Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,132,1,'T') AS CHAR(4))
END
, [TITERC] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,141,1,'T') IS NULL
OR Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,141,1,'R') = '9'
THEN ' '
ELSE CAST(Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,141,1,'T') AS CHAR(4))
END
, [TITERD] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,150,1,'T') IS NULL
OR Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,150,1,'R') = '9'
THEN ' '
ELSE CAST(Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,150,1,'T') AS CHAR(4))
END
, [TREATDAT] =
CASE
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,165,1,'N') IS NOT NULL
AND Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,165,1,'N') IN ('3','9')
THEN CAST(' ' AS CHAR(8))
WHEN Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,226,1,'DT') IS NULL
THEN CAST(' ' AS CHAR(8))
ELSE CAST(Prism.fn_GetMaternalAnswer(pm.ID_Profile,fh.ID_Maternal,226,1,'DT') AS CHAR(8))
END
, [INFTITER] =
CASE
WHEN Prism.fn_GetCongenitalAnswer(p.ID_Profile,182,1,'N') IS NOT NULL
AND Prism.fn_GetCongenitalAnswer(p.ID_Profile,182,1,'N') <> '1'
THEN ' '
WHEN Prism.fn_GetCongenitalAnswer(p.ID_Profile,182,1,'T') IS NULL
THEN ' '
ELSE CAST(Prism.fn_GetCongenitalAnswer(p.ID_Profile,182,1,'T') AS CHAR(4))
END
, [AMIND] = CAST(' ' AS CHAR(1))
, [ASIAN] = CAST(' ' AS CHAR(1))
, [BLACK] = CAST(' ' AS CHAR(1))
, [WHITE] = CAST(' ' AS CHAR(1))
, [NAHAW] = CAST(' ' AS CHAR(1))
, [RACEOTH] = CAST(' ' AS CHAR(1))
, [RACEUNK] = CAST(' ' AS CHAR(1))
, [MCOUNTRY] = CAST(' ' AS CHAR(2))
, [REACTREP] =
CASE
WHEN Prism.fn_GetCongenitalAnswer(p.ID_Profile,247,1,'N') IS NULL
THEN ' '
ELSE Prism.fn_GetCongenitalAnswer(p.ID_Profile,247,1,'N')
END
, [RTDATE] =
CASE
WHEN Prism.fn_GetCongenitalAnswer(p.ID_Profile,247,1,'DL') IS NULL
THEN ' '
ELSE CAST(Prism.fn_GetCongenitalAnswer(p.ID_Profile,247,1,'DL') AS CHAR(8))
END
, [NETSSVersion] = CAST(' ' AS CHAR(2))
-- Every NETSS transmission begins with a closed field record
FROM Prism.FieldRecordHold fh WITH(NOLOCK)
INNER JOIN Prism.FieldRecord f WITH(NOLOCK)
ON fh.ID_FieldRecord = f.ID_FieldRecord
-- Pick up the congenital cases
INNER JOIN Prism.Congenital c WITH(NOLOCK)
ON f.ID_Profile = c.ID_Profile
AND f.ID_Congenital = c.ID_Congenital
-- Need demographic information on baby
INNER JOIN Prism.[Profile] p WITH(NOLOCK)
ON f.ID_Profile = p.ID_Profile
-- Need NETSS codes for disease
INNER JOIN Prism.Disease d WITH(NOLOCK)
ON f.CD_Disease = d.CD_Disease
AND d.IN_ReportCDC = 'Y'
-- Find mother's information, if one is attached
LEFT JOIN Prism.Relationship rLP WITH(NOLOCK)
ON f.ID_Profile = rLP.ID_ProfileLink
AND rLP.CD_RelationshipType = 'CHD'
AND rLP.ID_Maternal IS NOT NULL
-- Need mother's demographic information
LEFT JOIN Prism.[Profile] pm WITH(NOLOCK)
ON rLP.ID_Profile = pm.ID_Profile
-- Get maternal information for the mother
LEFT JOIN Prism.Maternal m WITH(NOLOCK)
ON rLP.ID_Maternal = m.ID_Maternal
AND rLP.ID_Profile = m.ID_Profile
-- Get mother's most recent syphilis field record attached to this maternal record
LEFT JOIN (
SELECT fmom.*
, ROW_NUMBER( ) OVER (PARTITION BY fmom.ID_Profile, fmom.ID_Maternal
ORDER BY fmom.ID_Profile, fmom.ID_Maternal, fmom.DT_Disposition desc, fmom.DT_Added desc, fmom.ID_FieldRecord desc) AS ROWID
FROM Prism.FieldRecordHold frh WITH(NOLOCK)
INNER JOIN Prism.FieldRecord fmom WITH(NOLOCK)
ON fmom.ID_Maternal = frh.ID_Maternal
AND Prism.fn_GetDiseaseCategory(fmom.CD_Disease) = 'Syphilis'
AND fmom.IN_Morbidity = 'Y'
) AS fm
ON rLP.ID_Maternal = fm.ID_Maternal
AND rLP.ID_Profile = fm.ID_Profile
AND fm.ROWID = 1
-- Get interview information for the mother
LEFT JOIN Prism.Interview im WITH(NOLOCK)
ON fm.ID_Interview = im.ID_Interview
AND fm.ID_Profile = im.ID_Profile
-- Get mother's most recent syphilis field record treatment atttached to this field record
LEFT JOIN (
SELECT f.ID_Profile, f.ID_FieldRecord, ft.DT_Treated
, ROW_NUMBER( ) OVER (PARTITION BY f.ID_Profile, f.ID_FieldRecord ORDER BY f.ID_Profile, f.ID_FieldRecord, ft.DT_Treated desc, ft.DT_Added desc, ft.ID_FieldRecordTreatment desc) AS ROWID
FROM Prism.FieldRecordHold frh WITH(NOLOCK)
INNER JOIN Prism.FieldRecord f WITH(NOLOCK)
ON f.ID_Maternal = frh.ID_Maternal
AND Prism.fn_GetDiseaseCategory(f.CD_Disease) = 'Syphilis'
AND f.IN_Morbidity = 'Y'
INNER JOIN Prism.FieldRecordTreatment ft WITH(NOLOCK)
ON f.ID_Profile = ft.ID_Profile
AND f.ID_FieldRecord = ft.ID_FieldRecord
) AS ftm
ON fm.ID_FieldRecord = ftm.ID_FieldRecord
AND fm.ID_Profile = ftm.ID_Profile
AND ftm.ROWID = 1
-- Check to see if there is a lab attached (mother) and get the most recent specimen date attached to the field record
LEFT JOIN [Prism].[LabHold] AS LabMin WITH(NOLOCK)
ON fm.ID_FieldRecord = LabMin.ID_FieldRecord
AND rLP.ID_Profile = LabMin.ID_Profile
AND LabMin.ROWID = 1
-- Get Provider type for report
LEFT JOIN Prism.Provider prov WITH(NOLOCK)
ON LabMin.ID_ProviderOrdering = prov.ID_Provider
ORDER BY f.ID_Profile, f.ID_FieldRecord;
Rod Falanga, NM Department of Health
From: Mary White [mailto:mawg...@gmail.com]
Sent: Friday, April 24, 2015 11:23 AM
To: prism-...@googlegroups.com
Cc: Falanga, Rod, DOH
mary.white
On Thursday, April 23, 2015 at 6:02:43 PM UTC-4, Falanga, Rod, DOH wrote: