Notifications
Clear all
Topic starter
We just made the jump to CPS 12 and we are noticing that our front desk staff has been selecting an Ethnicity Sub-Category of "Other or Undetermined" but we don't have that mapped to anything so we are getting a lot of errors in the Link Logic DTS log.
Does anyone know of a report or query I can run against CPS to show which patients would have the "Ethnicity Sub-Category" of Other or Undetermined? We are trying to remove that sub-category from CPS but it tells us patients are assigned to it and it cannot be removed until that information is changed.
Thanks!
Posted : September 15, 2015 1:33 pm
Codyh3,
I can help with query. Send me an email and I'll send you what I got. When we upgraded to SP12 I did a bunch of query work to put patients in the right categories since we had a bunch of custom ones in the CPS10 days.
Posted : September 15, 2015 2:35 pm
This will help you with what you are looking for:
select pp.PatientId, pp.First, pp.last, convert(NVARCHAR, pp.Birthdate,101) as DOB, m.Description as Race, m1.Description as RaceSubCategory,lc.ABBREVNAME
from PatientRace pr
inner join PatientProfile pp on pp.PId = pr.PID
inner join MedLists m on m.MedListsId = pr.PatientRaceMid
inner join MedLists m1 on m1.MedListsId = pr.PatientRaceSubCategoryMid
inner join LOCREG lc on pp.LocationId = lc.LOCID
where m.TableName = 'Race'
and m1.TableName = 'RaceSubCategory'
and pp.pstatus = 'A'
and m.Description = 'Unspecified'
Posted : September 15, 2015 2:52 pm
Changed my mind I'll go ahead and post what I got,
1) This should give you First, Middle, Last Name, PatientID and the Race and Race Sub that patients are in.
SELECT PP.First ,
PP.Middle ,
PP.Last ,
PR.PatientProfileId AS PatientID ,
MLR.Description AS PatientRaceMid ,
MLSR.Description AS PatientRaceSubCategoryMid
FROM PatientRace PR
LEFT OUTER JOIN Medlists MLR ON MLR.MedlistsId = PR.PatientRaceMid
LEFT OUTER JOIN Medlists MLSR ON MLSR.MedlistsId = PR.PatientRaceSubCategoryMid
LEFT OUTER JOIN PatientProfile PP ON PP.PID = PR.PID
ORDER BY PatientRaceMid;
Posted : September 15, 2015 3:24 pm
2) Here is the one you have to be careful with. As with any query I make no promises. Make sure you know what you are doing and have a current backup. Also TEST it before running it on a production database.
If you follow this update script it does the following.
1) Set's patient PatientRaceMid 23126 (In my database this is White)
2) Modifies that row to say last modified by Race Update SQL and the current date/time.
3) Adds a new row for and sets PatientRaceSubCategoryMid 23125 (In my database this is Asian)
4) For that row uses last modified by Race Update SQL and the date/time from the first row it edited to White.
WHERE the PatientRaceMid was 251996 which in my database was White and Asian Race.
This is also set to roll the transaction back. So you'll have to un comment commit and comment out rollback.
BEGIN TRANSACTION
DECLARE @patientRaceSubCategoryMid as INTEGER = 251996; -- White and Asian Race Subcatagory ID
DECLARE @firstHalfRace as INTEGER = 23126; -- White First NEW Race ID
DECLARE @secondHalfRace as INTEGER = 23125; -- Asian Second NEW Race ID
DECLARE @PatientRaceTemp TABLE(
[PatientRaceId] [int] NOT NULL,
[PID] [numeric](19, 0) NOT NULL,
[PatientProfileId] [int] NOT NULL,
[PatientRaceMid] [int] NOT NULL,
[PatientRaceSubCategoryMid] [int] NULL,
[Created] [datetime2](7) NOT NULL,
[CreatedBy] [varchar](30) NOT NULL,
[LastModified] [datetime2](7) NOT NULL,
[LastModifiedBy] [varchar](30) NOT NULL
);
UPDATE [PatientRace]
SET PatientRaceSubCategoryMid = NULL,
PatientRaceMid = @firstHalfRace,
[LastModifiedBy] = 'Race Update SQL',
[LastModified] = GETDATE()
OUTPUT
deleted.[PatientRaceId],
deleted.[PID],
deleted.[PatientProfileId],
@secondHalfRace,
NULL,
deleted.[Created],
deleted.[CreatedBy],
inserted.[LastModified],
inserted.[LastModifiedBy]
INTO @PatientRaceTemp
WHERE PatientRaceSubCategoryMid = @patientRaceSubCategoryMid;
INSERT INTO [PatientRace]
([PID]
,[PatientProfileId]
,[PatientRaceMid]
,[PatientRaceSubCategoryMid]
,[Created]
,[CreatedBy]
,[LastModified]
,[LastModifiedBy])
SELECT [PID]
,[PatientProfileId]
,[PatientRaceMid]
,[PatientRaceSubCategoryMid]
,[Created]
,[CreatedBy]
,[LastModified]
,[LastModifiedBy]
FROM @PatientRaceTemp;
ROLLBACK TRANSACTION
--Commit TRANSACTION
Posted : September 15, 2015 3:37 pm
Topic starter
Thanks Bovie. That's exactly what I needed!
Posted : September 16, 2015 10:27 am
No problem glad I could help.
Posted : September 16, 2015 12:45 pm
