Longitudinal Followup it

From CaisisWiki

(Difference between revisions)
Jump to: navigation, search
m (Protected "Longitudinal Followup it" [edit=autoconfirmed:move=autoconfirmed])
Line 1: Line 1:
[[Category:Data Management]]
[[Category:Data Management]]
[[Category:Policies and Procedures]]
[[Category:Policies and Procedures]]
 +
 +
TRADUZIONE IN CORSO a cura di Andrea Fraietta
 +
 +
== Introduzione ==
 +
 +
Lo scopo di questo progetto è di mantenere costantemente aggiornato il nostro database con le informazioni mediche correnti e lo stato vitale di tutti i pazienti.
 +
Si tratta di un processo che sarà mantenuto aggiornato con frequenza giornaliera in modo da avare lo stato del follow-up aggiornato nel nostro sistema.
 +
 +
Il seguente schema riassume l'intero processo.
 +
 +
[[Image:FollowUpProcess_FlowDiagram.jpg]]
 +
 +
== II. Selezionare i pazienti che necessitano di follow-up ==
 +
[http://caisis.org Caisis] è un database basato su web del Dipartimento di Chirurgia del Memorial Sloan Kettering Cancer Center, di New York, che memorizza le informazioni dei pazienti che vengono visitati. Ogni paziente ha uno stato del contatto che può essere visualizzato in Caisis come mostrato qui sotto. Scegliendo uno stato di contatto dalla lista verranno elencati i pazienti che appartengono a quella categoria. Lo stato di contatto del singolo paziente può essere visualizzato anche dalla scheda "Patient Info" cliccando sulla scheda "Patient Data" dopo aver selezionato il paziente.
 +
 +
[[Image:PatientLists_ContactStatus.jpg]]
 +
 +
[[Image:PatientData_ContactStatus.JPG]]
 +
 +
'''**NOTE: THIS IS NOT REAL DATA'''
 +
 +
=== A. Patients that do not need any follow-up: ===
 +
 +
• '''DoNotContactPerPt''' - The patient or family has requested that they not be contacted.
 +
 +
• '''DoNotContactPerMD''' - The physician has requested that the patient not be contacted. (ie: VIPs).
 +
 +
• '''Current''' - Medical information on the patient is available in the database within the last 14 months. Deceased patients are in this category as well, unless their family has specified that they wish to not be contacted. Each night, Caisis takes patients who have no data record within the past 14 months, and places them in the ''NeedVitalStatus'' category, while filtering out patients who are deceased or did not have their primary surgery at your site.
 +
 +
• '''LTF''' -Lost to Followup. The patient has not responded after 3 tries at mailing a questionnaire to them, or we cannot find updated contact information on them.
 +
 +
=== B. Patients that require a series of 2 steps to determine if they need a follow-up letter: ===
 +
 +
• '''NeedVitalStatus'''
 +
 +
Step 1: Patients who have no data record within the past 14 months, are not deceased, are not in '''DoNotContactPerMD''' or '''DoNotContactPerPt''', the primary surgery was performed at your site, are not '''LTF''', and have not had vital status checked by the tumor registry in the last 60 days. The instituional tumor registry must be checked to see if there is any data record within the past 14 months and has not been captured in Caisis.
 +
 +
Step 2: Check the Social Security Death Index (SSDI)- patients who have had tumor registry checked in the last 60 days, but no SSDI checked in the last 60 days. The SSDI may provide death information if the patient has deceased.
 +
 +
==== 1. Checking Tumor Registry ====
 +
 +
In many cancer centers, the Tumor Registry is responsible for keeping a patient’s vital status updated. This information is stored in your site's electronic medical record (EMR.) Checking the Tumor Registry involves looking through EMR for vital status updates as well as for any recent medical information that is not already entered into Caisis.
 +
Look through the list of patient records for vital status and recent medical information.
 +
 +
===== a. Entering medical records information into Caisis =====
 +
 +
i. Vital Status-If the patient has died, enter the death date and cause of death under the Patient Info tab, and update the patient’s contact status to “Current”. If there is new information that indicates the patient is still alive, enter that date into the Status as “ALIVE.”
 +
 +
ii. Medical Information-If recent medical information is found that has not been entered into Caisis, enter it under the appropriate tabs in the database, and re-assess whether or not to update the patient to “Current.”
 +
 +
==== 2. Checking SSDI ====
 +
 +
a. To access the SSDI, go to click on the links to the [http://ssdi.rootsweb.com/ SSDI]. If you haven’t done an SSDI before, you can click on the link to SSDI tips. A general search involves using the patient’s last name, social security number, and date of birth as search references. If the patient has died, enter the death date and cause of death under the Patient Info tab in Caisis.
 +
 +
b. Enter into the Actions tab, “VitalStatusChecked” after completing Stei 1 and Step 2.
 +
 +
c. Notifying the Tumor Registry if a patient has died
 +
 +
=== NeedLetter1, 2, or 3 ===
 +
 +
NeedLetter1- Patients belonging to this category are those who have had vital status checked by the tumor registry, SSDI in the last 60 days and whose last item in the chronological list is > 14 months (i.e. no data record in the last 14 months). Once the action VitalStatusChecked is entered under the ACtions Tab, Caisis automatically updates the patient’s contact status to NeedLetter1. Three attempts are made at contacting the patient. If the patient hasn’t responded in 90 days (indicated by having no action entered), and the and tumor registry or SSDI hasn’t been checked again, the contact status is automatically updated to NeedVitalStatus (follow same instructions as before). Once their vital status has been checked, their contact status should be changed to NeedLetter2. The same events will occur if the patient still hasn’t responded after 90 days (NeedVitalStatus, then NeedLetter3). Please do not send out letters to patients during the holiday season.
 +
 +
==== Preparing mail out material: ====
 +
 +
*Print out cover letter, follow-up questionnaire, and authorization and mailing labels for each patient using Microsoft Access. Explain wizard
 +
 +
* The cover letter should have the current physician’s letterhead and correct contact information.
 +
 +
*Include a business reply envelope with the letter and questionnaire.
 +
 +
*All items should be stuffed in an envelope with the institutional logo.
 +
 +
 +
1. Correspondence to patients residing outside of US should be by UPS to expedite correspondence
 +
 +
2. Inform Physician Office Assistants which patients have been sent letters and questionnaires. Inform them who they should refer any calls to about this. In addition, give them a copy of the questionnaire and letter.
 +
 +
3. Any new questionnaires and cover letters must be approved by the responsible physician before being sent out.
 +
 +
IV. Receiving letters and questionnaires
 +
 +
Open LetterReceived wizard.
 +
Page 1-Lists number of letters received, returned to sender, and pending.
 +
Page 2-Instructions for processing the letters as they are received
 +
Page 3-Instructions for entering data from the questionnaire
 +
Page 4-Instructions/criteria for changing the patient’s contact status
 +
NeedMD_Contact
 +
 +
1) Call the physician’s office and explain that you will be sending an authorization to get medical records for a patient. Ask if they prefer to fax the authorization or send it in the mail. If they prefer sending it by mail, send cover letter to physician along with patient’s signed consent. Alternatively, if a fellow or medical student calls the doctors office, they can get the information over the phone. Copies of the medical records are still needed.
 +
2) Once this is done, enter the action MD_Contacted in the actions tab.
 +
3) Once the medical records are received, enter the data, or change the patient’s contact status to NeedDataEntry, if the data can’t be entered right away.
 +
 +
==== NeedMD_ToCallPt ====
 +
If the patient hasn’t responded after sending three letters, change the patient’s contact status to '''NeedMD_ToCallPt'''-patients who have been in the category '''Letter3_Sent''' and have had no action entered for 6 weeks. MDs (mainly research fellows) will then call the patient to collect a history, physician contact info, and get authorization.
 +
 +
The following is a list of pertinent questions to ask a patient if a fellow or medical student calls the patient. These can also be used when they call a patient’s doctor for records:
 +
## Other treatments since RP?
 +
## Have you had any problems with urination since the surgery (difficulty passing urine; urinary retention) Have you had any treatments for urethral strictures?
 +
## Have you had any other '''procedures''' for prostate cancer-related problems/urinary dysfunction?
 +
## Do you wear protective pads for urine loss/incontinence? How many pads do you wear during the day? How many pads do you wear at night? Have you had any treatment for incontinence (AUS)?
 +
## Do you have any spontaneous erections sufficient for intercourse? Are you capable of intercourse without erectile aids? If you use aids, which aids are you using? If you take Viagra, which dose are you using? Do you use injection therapy or urethral suppositories? Have you had any surgical treatment for impotence (prosthesis)?
 +
## Follow up lab reports since last records at your site? (total PSA, testosterone, free PSA)
 +
## Are you being followed by a physician; may we have your physician’s address and mail you a permission form to request your records from your physician?
 +
## Would you like to be mailed a QOL questionnaire that will ask questions about your general state of health and urologic function since your surgery?
 +
 +
# After calling the patient, enter the action '''Patient_WasCalled''' in the actions tab.
 +
# Enter updated information in the database.
 +
# Change the patient’s contact status to
 +
** '''Current''' if you do not need to contact their physician, and have up to date information.
 +
** '''NeedPtAuthorization''' if you need to send them a Release of Information form to sign.
 +
** '''NeedMD_Contact''' if you need to contact their physician.
 +
 +
==== NeedPtContactInfo ====
 +
If a letter gets returned undeliverable, and there is no forwarding address, enter an action, '''ReturnedToSender'''. Then change the patient’s contact status to '''NeedPtContactInfo'''. The following steps can be taken to obtain their contact info. Enter an action after each step is taken.
 +
 +
# Call the numbers listed in the database or DMS (patient’s numbers, emergency contact numbers).
 +
# Call information (411) to check to see if there are any listings in their area code.
 +
# Try their physicians listed to see if they have updated contact information.
 +
# Search the internet.
 +
# Try your site's Tumor registry to see if they can help. They can give you the last vital status they have for a patient or the last contact information for the patient.
 +
# If you obtain updated contact information through any of these steps, update Caisis, enter the action '''AddressUpdated''', and then change the patient’s contact status back to '''NeedLetter1, 2, or 3''', depending on where they are in the process.
 +
# If no contact information is obtained after all these steps, update the patient’s contact in the database to '''LTF''' “Lost to follow-up”.
 +
 +
==== Ordering Supplies ====
 +
# Order institutional letterhead and envelopes.
 +
# Business Reply envelopes are ordered as follows:
 +
 +
== Caisis Demographics Contact Status ==
 +
 +
{|border="1"
 +
! PtContactStatus || Drop downActionItem Drop down
 +
|-
 +
| Current  || CaseReviewed
 +
|-
 +
| DoNotContactPerMD ||
 +
|-
 +
|DoNotContactPerPt || DataReceived
 +
|-
 +
| Letter1_Sent ||  
 +
|-
 +
| Letter2_Sent || Letter2_Sent
 +
|-
 +
| Letter3_Sent || Letter3_Sent
 +
|-
 +
| LostToFollowup || PtWebSearched
 +
|-
 +
| MD_Contacted || MD_Contacted
 +
|-
 +
| NeedDataEntry || DataEntered
 +
|-
 +
| NeedLetter1 || Letter1_Sent
 +
|-
 +
| NeedLetter2 || SentToCDB
 +
|-
 +
| NeedLetter3 || SentToEMR
 +
|-
 +
| NeedMD_Contact || VitalStatusChecked
 +
|-
 +
| NeedMD_ToCall || PtWasCalled
 +
|-
 +
| NeedPtAuthorization ||  
 +
|-
 +
| NeedPtContactInfo || PtContactInfoUpdated
 +
|-
 +
| NeedVitalStatus || LetterReturnedToSender
 +
|}
 +
 +
==== Actions table Trigger ====
 +
Whenever a user edits or inserts a record in the Actions table (Immediate Response- Should remain simple or as simple as possible)
 +
 +
<code>
 +
UPDATE Patients
 +
SET PtContactStatus = CASE WHEN i.ActionItem = 'VitalStatusChecked' AND PtContactStatus = 'NeedVitalStatus' THEN 'Current'
 +
WHEN i.ActionItem = 'Letter1_Sent' AND PtContactStatus = 'NeedLetter1' THEN 'Letter1_Sent'
 +
WHEN i.ActionItem = 'Letter2_Sent' AND PtContactStatus = 'NeedLetter2' THEN 'Letter2_Sent'
 +
WHEN i.ActionItem = 'Letter3_Sent' AND PtContactStatus = 'NeedLetter3' THEN 'Letter3_Sent'
 +
WHEN i.ActionItem = 'LetterReturnedToSender' AND PtContactStatus LIKE 'Letter[1-3]%Sent' THEN 'NeedPtContactInfo' WHEN i.ActionItem = 'MD_Contacted' AND PtContactStatus = 'NeedMD_Contact' THEN 'MD_Contacted' WHEN i.ActionItem = 'PtWasCalled' AND PtContactStatus = 'NeedMD_ToCallPt' THEN 'Current' WHEN i.ActionItem = 'DataReceived' AND PtContactStatus IN ('Letter1_Sent','Letter2_Sent','Letter3_Sent','MD_Contacted') THEN 'NeedDataEntry' WHEN i.ActionItem = 'DataReceived' AND PtContactStatus = 'NeedPtAuthorization' THEN 'NeedMD_Contact' WHEN i.ActionItem = 'DataEntered' AND PtContactStatus = 'NeedDataEntry' THEN 'Current'
 +
WHEN i.ActionItem = 'PtContactInfoUpdated' AND PtContactStatus = 'NeedPtContactInfo' THEN 'Current' END
 +
FROM Patients INNER JOIN Inserted i ON Patients.PatientId = i.PatientId WHERE PtContactStatus <> CASE
 +
WHEN i.ActionItem = 'VitalStatusChecked' AND PtContactStatus = 'NeedVitalStatus' THEN 'Current'
 +
WHEN i.ActionItem = 'Letter1_Sent' AND PtContactStatus = 'NeedLetter1' THEN 'Letter1_Sent'
 +
WHEN i.ActionItem = 'Letter2_Sent' AND PtContactStatus = 'NeedLetter2' THEN 'Letter2_Sent'
 +
WHEN i.ActionItem = 'Letter3_Sent' AND PtContactStatus = 'NeedLetter3' THEN 'Letter3_Sent'
 +
WHEN i.ActionItem = 'LetterReturnedToSender' AND PtContactStatus LIKE 'Letter[1-3]%Sent' THEN 'NeedPtContactInfo'
 +
WHEN i.ActionItem = 'MD_Contacted' AND PtContactStatus = 'NeedMD_Contact' THEN 'MD_Contacted' WHEN i.ActionItem = 'PtWasCalled' AND PtContactStatus = 'NeedMD_ToCallPt' THEN 'Current' WHEN i.ActionItem = 'DataReceived' AND PtContactStatus IN ('Letter1_Sent','Letter2_Sent','Letter3_Sent','MD_Contacted') THEN 'NeedDataEntry' WHEN i.ActionItem = 'DataReceived' AND PtContactStatus = 'NeedPtAuthorization' THEN 'NeedMD_Contact' WHEN i.ActionItem = 'DataEntered' AND PtContactStatus = 'NeedDataEntry' THEN 'Current'
 +
WHEN i.ActionItem = 'PtContactInfoUpdated' AND PtContactStatus = 'NeedPtContactInfo' THEN 'Current' END
 +
</code>
 +
 +
Nightly Job(Delayed Response, Can be complex)
 +
 +
<code>
 +
CREATE PROCEDURE dbo.spFollowupUpdatePtContactStatus
 +
AS
 +
BEGIN TRANSACTION
 +
--Change to current if null
 +
UPDATE Patients
 +
SET PtContactStatus = 'Current'
 +
WHERE PtContactStatus = '0' OR PtContactStatus IS NULL
 +
/*
 +
If they are Current and (no data in the last 14 months or dead)
 +
and (vital status has not been checked in the last 60 days) then
 +
change to NeedVitalStatus
 +
*/
 +
UPDATE Patients
 +
SET PtContactStatus = 'NeedVitalStatus' FROM Patients LEFT OUTER JOIN FJ_CurrentDataView ON Patients.PatientId = FJ_CurrentDataView.PatientId
 +
LEFT OUTER JOIN FJ_VitalStatusCheckedView ON Patients.PatientId = FJ_VitalStatusCheckedView.PatientId
 +
WHERE PtContactStatus = 'Current' AND
 +
FJ_CurrentDataView.PatientId IS NULL AND FJ_VitalStatusCheckedView.PatientId IS NULL
 +
/*
 +
If they are Current and (no data in the last 14 months or dead)
 +
and (vital status has been checked in the last 60 days) then
 +
change to next letter/contact, If the last LetterX_Sent was more
 +
than 90 days ago then move to NeedLetter1 so can restart the process */
 +
UPDATE Patients
 +
SET PtContactStatus =CASE
 +
WHEN LastLetterSent IS NULL THEN 'NeedLetter1' WHEN LastLetterSent LIKE 'Letter1%Sent' THEN 'NeedLetter2'
 +
WHEN LastLetterSent LIKE 'Letter2%Sent' THEN 'NeedLetter3'
 +
WHEN LastLetterSent LIKE 'Letter3%Sent' THEN 'NeedPtContactInfo'
 +
END FROM Patients LEFT OUTER JOIN FJ_CurrentDataView ON Patients.PatientId = FJ_CurrentDataView.PatientId
 +
INNER JOIN FJ_VitalStatusCheckedView ON Patients.PatientId = FJ_VitalStatusCheckedView.PatientId
 +
LEFT OUTER JOIN FJ_LastLetterSentView ON Patients.PatientId = FJ_LastLetterSentView.PatientId
 +
WHERE PtContactStatus = 'Current' AND
 +
FJ_CurrentDataView.PatientId IS NULL
 +
/*
 +
If they are LetterX_Sent or NeedLetterX or NeedVitalStatus and
 +
(no LetterX_Sent in the last 90 days) and (have data in the last 14 months or dead) change to Current */
 +
UPDATE Patients
 +
SET PtContactStatus = 'Current'
 +
FROM Patients INNER JOIN FJ_CurrentDataView ON Patients.PatientId = FJ_CurrentDataView.PatientId
 +
LEFT OUTER JOIN FJ_RecentLetterSentView ON Patients.PatientId = FJ_RecentLetterSentView.PatientId
 +
WHERE (PtContactStatus LIKE 'Letter[1-3]%Sent' OR
 +
PtContactStatus LIKE 'NeedLetter[1-3]' OR
 +
PtContactStatus = 'NeedVitalStatus') AND
 +
FJ_RecentLetterSentView.PatientId IS NULL
 +
/*
 +
If they are LetterX_Sent only and (no LetterX_Sent in the last 90 days) and (no data in the last 14 months or dead) and
 +
(vital status has not been checked in the last 60 days) change to NeedVitalStatus
 +
*/
 +
UPDATE Patients
 +
SET PtContactStatus = 'NeedVitalStatus' FROM Patients LEFT OUTER JOIN FJ_CurrentDataView ON Patients.PatientId = FJ_CurrentDataView.PatientId
 +
LEFT OUTER JOIN FJ_RecentLetterSentView ON Patients.PatientId = FJ_RecentLetterSentView.PatientId
 +
LEFT OUTER JOIN FJ_VitalStatusCheckedView ON Patients.PatientId = FJ_VitalStatusCheckedView.PatientId
 +
WHERE PtContactStatus LIKE 'Letter[1-3]%Sent' AND
 +
FJ_CurrentDataView.PatientId IS NULL AND FJ_RecentLetterSentView.PatientId IS NULL AND
 +
FJ_VitalStatusCheckedView.PatientId IS NULL
 +
/*
 +
If they are LetterX_Sent and (no LetterX_Sent in the last 90 days) and (no data in the last 14 months or dead) and
 +
(vital status has been checked in the last 60 days) then change to next letter/contact */
 +
UPDATE Patients
 +
SET PtContactStatus =CASE
 +
WHEN PtContactStatus LIKE 'Letter1%Sent' THEN 'NeedLetter2'
 +
WHEN PtContactStatus LIKE 'Letter2%Sent' THEN 'NeedLetter3'
 +
WHEN PtContactStatus LIKE 'Letter3%Sent' THEN 'NeedPtContactInfo' END FROM Patients LEFT OUTER JOIN FJ_CurrentDataView ON Patients.PatientId = FJ_CurrentDataView.PatientId
 +
LEFT OUTER JOIN FJ_RecentLetterSentView ON Patients.PatientId = FJ_RecentLetterSentView.PatientId
 +
INNER JOIN FJ_VitalStatusCheckedView ON Patients.PatientId = FJ_VitalStatusCheckedView.PatientId
 +
WHERE PtContactStatus LIKE 'Letter[1-3]%Sent' AND
 +
FJ_CurrentDataView.PatientId IS NULL AND FJ_RecentLetterSentView.PatientId IS NULL /*
 +
If patient is dead change ContactStatus to Current but only if ContactStatus is not already Current and ContactStatus NOT LIKE '%DoNotContact%'
 +
*/
 +
UPDATE Patients
 +
SET PtContactStatus = 'Current'
 +
FROM Patients WHERE (PtContactStatus <> 'Current' AND PtContactStatus NOT LIKE '%DoNotContact%') AND
 +
(PtDeathDateText IS NOT NULL)
 +
COMMIT TRANSACTION
 +
GO</code>
 +
 +
 +
Queries used by Nightly Job
 +
 +
<code>
 +
CREATE VIEW dbo.FJ_CurrentDataView
 +
AS
 +
--Not all tables are represented here Includes deceased patients SELECT a.PatientId AS PatientId
 +
FROM
 +
(
 +
SELECT BxDate AS SortDate, 'Biopsies' AS TableName, PatientId
 +
FROM Biopsies WHERE BxDate > DATEADD(m,-14,GETDATE())
 +
UNION
 +
SELECT BxDate AS SortDate, 'BiopsiesProstate' AS TableName, PatientId
 +
FROM BiopsiesProstate WHERE BxDate > DATEADD(m,-14,GETDATE())
 +
UNION
 +
SELECT ClinStageDate, 'ClinicalStages', PatientId
 +
FROM ClinicalStages
 +
WHERE ClinStageDate > DATEADD(m,-14,GETDATE())
 +
UNION
 +
SELECT DxDate, 'Diagnostics', PatientId
 +
FROM Diagnostics
 +
WHERE DxDate > DATEADD(m,-14,GETDATE())
 +
UNION
 +
SELECT LabDate, 'LabTests', PatientId
 +
FROM LabTests WHERE LabDate > DATEADD(m,-14,GETDATE())
 +
UNION
 +
SELECT MedTxDate, 'MedicalTherapy', PatientId
 +
FROM MedicalTherapy WHERE MedTxDate > DATEADD(m,-14,GETDATE())
 +
UNION
 +
SELECT OpDate, 'Procedures', PatientId
 +
FROM Procedures
 +
WHERE OpDate > DATEADD(m,-14,GETDATE())
 +
UNION
 +
SELECT SurgDate, 'Surgery', PatientId
 +
FROM Surgeries
 +
WHERE SurgDate > DATEADD(m,-14,GETDATE())
 +
UNION
 +
SELECT EndoDate, 'Endoscopy', PatientId
 +
FROM ProcEndoscopies
 +
WHERE EndoDate > DATEADD(m,-14,GETDATE())
 +
UNION
 +
SELECT CytoDate, 'Cytology', PatientId
 +
FROM Cytologies
 +
WHERE CytoDate > DATEADD(m,-14,GETDATE())
 +
UNION
 +
SELECT BrachyDate, 'BrachyTherapy', PatientId
 +
FROM BrachyTherapy WHERE BrachyDate > DATEADD(m,-14,GETDATE())
 +
UNION
 +
SELECT RadTxDate, 'RadiationTherapy', PatientId
 +
FROM RadiationTherapy WHERE RadTxDate > DATEADD(m,-14,GETDATE())
 +
UNION
 +
SELECT StatusDate, 'Status', PatientId
 +
FROM Status
 +
WHERE StatusDate > DATEADD(m,-14,GETDATE())
 +
UNION
 +
SELECT PtProtocolStatusDate, 'Protocols', PatientId
 +
FROM PatientProtocols INNER JOIN PatientProtocolStatus ON PatientProtocolStatus.PatientProtocolId = PatientProtocols.PatientProtocolId
 +
WHERE PtProtocolStatusDate > DATEADD(m,-14,GETDATE())
 +
UNION
 +
SELECT EncDate, 'Encounters', PatientId
 +
FROM Encounters WHERE EncDate > DATEADD(m,-14,GETDATE())
 +
UNION
 +
SELECT SurveyDate, 'Surveys', PatientId
 +
FROM Surveys
 +
WHERE SurveyDate > DATEADD(m,-14,GETDATE())
 +
--Add in deceased patients classified as having a DeathDateText
 +
UNION
 +
SELECT PtDeathDate, 'Patients',PatientId
 +
FROM Patients
 +
WHERE (PtDeathDateText IS NOT NULL)
 +
) AS a
 +
GROUP BY a.PatientId
 +
CREATE VIEW dbo.FJ_VitalStatusCheckedView
 +
AS
 +
SELECT DISTINCT PatientId
 +
FROM Actions
 +
WHERE ActionItem = 'VitalStatusChecked' AND
 +
ActionDate >= DATEADD(d, - 60, GETDATE())
 +
CREATE VIEW dbo.FJ_LastLetterSentView
 +
AS
 +
SELECT Actions.PatientId AS PatientId, ActionItem AS LastLetterSent
 +
FROM
 +
(
 +
SELECT MAX(ActionDate) AS LastLetterDate, PatientId
 +
FROM Actions
 +
WHERE (ActionItem LIKE 'Letter[1-3]%Sent') AND ActionDate >= DATEADD(d, - 90, GETDATE())
 +
GROUP BY PatientId
 +
) AS a INNER JOIN Actions ON a.PatientId = Actions.PatientId AND
 +
a.LastLetterDate = Actions.ActionDate
 +
WHERE (ActionItem LIKE 'Letter[1-3]%Sent')
 +
CREATE VIEW dbo.FJ_RecentLetterSentView
 +
AS
 +
SELECT DISTINCT PatientId
 +
FROM Actions
 +
WHERE ActionItem LIKE 'Letter[1-3]%Sent' AND
 +
ActionDate >= DATEADD(d, - 90, GETDATE())</code>
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
!"£--------------
 +
== Stato del contatto - informazione demografica in Caisis ==
== Stato del contatto - informazione demografica in Caisis ==

Revision as of 06:00, 15 September 2008


TRADUZIONE IN CORSO a cura di Andrea Fraietta

Contents

Introduzione

Lo scopo di questo progetto è di mantenere costantemente aggiornato il nostro database con le informazioni mediche correnti e lo stato vitale di tutti i pazienti. Si tratta di un processo che sarà mantenuto aggiornato con frequenza giornaliera in modo da avare lo stato del follow-up aggiornato nel nostro sistema.

Il seguente schema riassume l'intero processo.

Image:FollowUpProcess_FlowDiagram.jpg

II. Selezionare i pazienti che necessitano di follow-up

Caisis è un database basato su web del Dipartimento di Chirurgia del Memorial Sloan Kettering Cancer Center, di New York, che memorizza le informazioni dei pazienti che vengono visitati. Ogni paziente ha uno stato del contatto che può essere visualizzato in Caisis come mostrato qui sotto. Scegliendo uno stato di contatto dalla lista verranno elencati i pazienti che appartengono a quella categoria. Lo stato di contatto del singolo paziente può essere visualizzato anche dalla scheda "Patient Info" cliccando sulla scheda "Patient Data" dopo aver selezionato il paziente.

Image:PatientLists_ContactStatus.jpg

Image:PatientData_ContactStatus.JPG

**NOTE: THIS IS NOT REAL DATA

A. Patients that do not need any follow-up:

DoNotContactPerPt - The patient or family has requested that they not be contacted.

DoNotContactPerMD - The physician has requested that the patient not be contacted. (ie: VIPs).

Current - Medical information on the patient is available in the database within the last 14 months. Deceased patients are in this category as well, unless their family has specified that they wish to not be contacted. Each night, Caisis takes patients who have no data record within the past 14 months, and places them in the NeedVitalStatus category, while filtering out patients who are deceased or did not have their primary surgery at your site.

LTF -Lost to Followup. The patient has not responded after 3 tries at mailing a questionnaire to them, or we cannot find updated contact information on them.

B. Patients that require a series of 2 steps to determine if they need a follow-up letter:

NeedVitalStatus

Step 1: Patients who have no data record within the past 14 months, are not deceased, are not in DoNotContactPerMD or DoNotContactPerPt, the primary surgery was performed at your site, are not LTF, and have not had vital status checked by the tumor registry in the last 60 days. The instituional tumor registry must be checked to see if there is any data record within the past 14 months and has not been captured in Caisis.

Step 2: Check the Social Security Death Index (SSDI)- patients who have had tumor registry checked in the last 60 days, but no SSDI checked in the last 60 days. The SSDI may provide death information if the patient has deceased.

1. Checking Tumor Registry

In many cancer centers, the Tumor Registry is responsible for keeping a patient’s vital status updated. This information is stored in your site's electronic medical record (EMR.) Checking the Tumor Registry involves looking through EMR for vital status updates as well as for any recent medical information that is not already entered into Caisis. Look through the list of patient records for vital status and recent medical information.

a. Entering medical records information into Caisis

i. Vital Status-If the patient has died, enter the death date and cause of death under the Patient Info tab, and update the patient’s contact status to “Current”. If there is new information that indicates the patient is still alive, enter that date into the Status as “ALIVE.”

ii. Medical Information-If recent medical information is found that has not been entered into Caisis, enter it under the appropriate tabs in the database, and re-assess whether or not to update the patient to “Current.”

2. Checking SSDI

a. To access the SSDI, go to click on the links to the SSDI. If you haven’t done an SSDI before, you can click on the link to SSDI tips. A general search involves using the patient’s last name, social security number, and date of birth as search references. If the patient has died, enter the death date and cause of death under the Patient Info tab in Caisis.

b. Enter into the Actions tab, “VitalStatusChecked” after completing Stei 1 and Step 2.

c. Notifying the Tumor Registry if a patient has died

NeedLetter1, 2, or 3

NeedLetter1- Patients belonging to this category are those who have had vital status checked by the tumor registry, SSDI in the last 60 days and whose last item in the chronological list is > 14 months (i.e. no data record in the last 14 months). Once the action VitalStatusChecked is entered under the ACtions Tab, Caisis automatically updates the patient’s contact status to NeedLetter1. Three attempts are made at contacting the patient. If the patient hasn’t responded in 90 days (indicated by having no action entered), and the and tumor registry or SSDI hasn’t been checked again, the contact status is automatically updated to NeedVitalStatus (follow same instructions as before). Once their vital status has been checked, their contact status should be changed to NeedLetter2. The same events will occur if the patient still hasn’t responded after 90 days (NeedVitalStatus, then NeedLetter3). Please do not send out letters to patients during the holiday season.

Preparing mail out material:

  • Print out cover letter, follow-up questionnaire, and authorization and mailing labels for each patient using Microsoft Access. Explain wizard
  • The cover letter should have the current physician’s letterhead and correct contact information.
  • Include a business reply envelope with the letter and questionnaire.
  • All items should be stuffed in an envelope with the institutional logo.


1. Correspondence to patients residing outside of US should be by UPS to expedite correspondence

2. Inform Physician Office Assistants which patients have been sent letters and questionnaires. Inform them who they should refer any calls to about this. In addition, give them a copy of the questionnaire and letter.

3. Any new questionnaires and cover letters must be approved by the responsible physician before being sent out.

IV. Receiving letters and questionnaires

Open LetterReceived wizard. Page 1-Lists number of letters received, returned to sender, and pending. Page 2-Instructions for processing the letters as they are received Page 3-Instructions for entering data from the questionnaire Page 4-Instructions/criteria for changing the patient’s contact status NeedMD_Contact

1) Call the physician’s office and explain that you will be sending an authorization to get medical records for a patient. Ask if they prefer to fax the authorization or send it in the mail. If they prefer sending it by mail, send cover letter to physician along with patient’s signed consent. Alternatively, if a fellow or medical student calls the doctors office, they can get the information over the phone. Copies of the medical records are still needed. 2) Once this is done, enter the action MD_Contacted in the actions tab. 3) Once the medical records are received, enter the data, or change the patient’s contact status to NeedDataEntry, if the data can’t be entered right away.

NeedMD_ToCallPt

If the patient hasn’t responded after sending three letters, change the patient’s contact status to NeedMD_ToCallPt-patients who have been in the category Letter3_Sent and have had no action entered for 6 weeks. MDs (mainly research fellows) will then call the patient to collect a history, physician contact info, and get authorization.

The following is a list of pertinent questions to ask a patient if a fellow or medical student calls the patient. These can also be used when they call a patient’s doctor for records:

    1. Other treatments since RP?
    2. Have you had any problems with urination since the surgery (difficulty passing urine; urinary retention) Have you had any treatments for urethral strictures?
    3. Have you had any other procedures for prostate cancer-related problems/urinary dysfunction?
    4. Do you wear protective pads for urine loss/incontinence? How many pads do you wear during the day? How many pads do you wear at night? Have you had any treatment for incontinence (AUS)?
    5. Do you have any spontaneous erections sufficient for intercourse? Are you capable of intercourse without erectile aids? If you use aids, which aids are you using? If you take Viagra, which dose are you using? Do you use injection therapy or urethral suppositories? Have you had any surgical treatment for impotence (prosthesis)?
    6. Follow up lab reports since last records at your site? (total PSA, testosterone, free PSA)
    7. Are you being followed by a physician; may we have your physician’s address and mail you a permission form to request your records from your physician?
    8. Would you like to be mailed a QOL questionnaire that will ask questions about your general state of health and urologic function since your surgery?
  1. After calling the patient, enter the action Patient_WasCalled in the actions tab.
  2. Enter updated information in the database.
  3. Change the patient’s contact status to
    • Current if you do not need to contact their physician, and have up to date information.
    • NeedPtAuthorization if you need to send them a Release of Information form to sign.
    • NeedMD_Contact if you need to contact their physician.

NeedPtContactInfo

If a letter gets returned undeliverable, and there is no forwarding address, enter an action, ReturnedToSender. Then change the patient’s contact status to NeedPtContactInfo. The following steps can be taken to obtain their contact info. Enter an action after each step is taken.

  1. Call the numbers listed in the database or DMS (patient’s numbers, emergency contact numbers).
  2. Call information (411) to check to see if there are any listings in their area code.
  3. Try their physicians listed to see if they have updated contact information.
  4. Search the internet.
  5. Try your site's Tumor registry to see if they can help. They can give you the last vital status they have for a patient or the last contact information for the patient.
  6. If you obtain updated contact information through any of these steps, update Caisis, enter the action AddressUpdated, and then change the patient’s contact status back to NeedLetter1, 2, or 3, depending on where they are in the process.
  7. If no contact information is obtained after all these steps, update the patient’s contact in the database to LTF “Lost to follow-up”.

Ordering Supplies

  1. Order institutional letterhead and envelopes.
  2. Business Reply envelopes are ordered as follows:

Caisis Demographics Contact Status

PtContactStatus Drop downActionItem Drop down
Current CaseReviewed
DoNotContactPerMD
DoNotContactPerPt DataReceived
Letter1_Sent  
Letter2_Sent Letter2_Sent
Letter3_Sent Letter3_Sent
LostToFollowup PtWebSearched
MD_Contacted MD_Contacted
NeedDataEntry DataEntered
NeedLetter1 Letter1_Sent
NeedLetter2 SentToCDB
NeedLetter3 SentToEMR
NeedMD_Contact VitalStatusChecked
NeedMD_ToCall PtWasCalled
NeedPtAuthorization  
NeedPtContactInfo PtContactInfoUpdated
NeedVitalStatus LetterReturnedToSender

Actions table Trigger

Whenever a user edits or inserts a record in the Actions table (Immediate Response- Should remain simple or as simple as possible)

UPDATE Patients SET PtContactStatus = CASE WHEN i.ActionItem = 'VitalStatusChecked' AND PtContactStatus = 'NeedVitalStatus' THEN 'Current' WHEN i.ActionItem = 'Letter1_Sent' AND PtContactStatus = 'NeedLetter1' THEN 'Letter1_Sent' WHEN i.ActionItem = 'Letter2_Sent' AND PtContactStatus = 'NeedLetter2' THEN 'Letter2_Sent' WHEN i.ActionItem = 'Letter3_Sent' AND PtContactStatus = 'NeedLetter3' THEN 'Letter3_Sent' WHEN i.ActionItem = 'LetterReturnedToSender' AND PtContactStatus LIKE 'Letter[1-3]%Sent' THEN 'NeedPtContactInfo' WHEN i.ActionItem = 'MD_Contacted' AND PtContactStatus = 'NeedMD_Contact' THEN 'MD_Contacted' WHEN i.ActionItem = 'PtWasCalled' AND PtContactStatus = 'NeedMD_ToCallPt' THEN 'Current' WHEN i.ActionItem = 'DataReceived' AND PtContactStatus IN ('Letter1_Sent','Letter2_Sent','Letter3_Sent','MD_Contacted') THEN 'NeedDataEntry' WHEN i.ActionItem = 'DataReceived' AND PtContactStatus = 'NeedPtAuthorization' THEN 'NeedMD_Contact' WHEN i.ActionItem = 'DataEntered' AND PtContactStatus = 'NeedDataEntry' THEN 'Current' WHEN i.ActionItem = 'PtContactInfoUpdated' AND PtContactStatus = 'NeedPtContactInfo' THEN 'Current' END FROM Patients INNER JOIN Inserted i ON Patients.PatientId = i.PatientId WHERE PtContactStatus <> CASE WHEN i.ActionItem = 'VitalStatusChecked' AND PtContactStatus = 'NeedVitalStatus' THEN 'Current' WHEN i.ActionItem = 'Letter1_Sent' AND PtContactStatus = 'NeedLetter1' THEN 'Letter1_Sent' WHEN i.ActionItem = 'Letter2_Sent' AND PtContactStatus = 'NeedLetter2' THEN 'Letter2_Sent' WHEN i.ActionItem = 'Letter3_Sent' AND PtContactStatus = 'NeedLetter3' THEN 'Letter3_Sent' WHEN i.ActionItem = 'LetterReturnedToSender' AND PtContactStatus LIKE 'Letter[1-3]%Sent' THEN 'NeedPtContactInfo' WHEN i.ActionItem = 'MD_Contacted' AND PtContactStatus = 'NeedMD_Contact' THEN 'MD_Contacted' WHEN i.ActionItem = 'PtWasCalled' AND PtContactStatus = 'NeedMD_ToCallPt' THEN 'Current' WHEN i.ActionItem = 'DataReceived' AND PtContactStatus IN ('Letter1_Sent','Letter2_Sent','Letter3_Sent','MD_Contacted') THEN 'NeedDataEntry' WHEN i.ActionItem = 'DataReceived' AND PtContactStatus = 'NeedPtAuthorization' THEN 'NeedMD_Contact' WHEN i.ActionItem = 'DataEntered' AND PtContactStatus = 'NeedDataEntry' THEN 'Current' WHEN i.ActionItem = 'PtContactInfoUpdated' AND PtContactStatus = 'NeedPtContactInfo' THEN 'Current' END

Nightly Job(Delayed Response, Can be complex)

CREATE PROCEDURE dbo.spFollowupUpdatePtContactStatus AS BEGIN TRANSACTION --Change to current if null UPDATE Patients SET PtContactStatus = 'Current' WHERE PtContactStatus = '0' OR PtContactStatus IS NULL /* If they are Current and (no data in the last 14 months or dead) and (vital status has not been checked in the last 60 days) then change to NeedVitalStatus

  • /

UPDATE Patients SET PtContactStatus = 'NeedVitalStatus' FROM Patients LEFT OUTER JOIN FJ_CurrentDataView ON Patients.PatientId = FJ_CurrentDataView.PatientId LEFT OUTER JOIN FJ_VitalStatusCheckedView ON Patients.PatientId = FJ_VitalStatusCheckedView.PatientId WHERE PtContactStatus = 'Current' AND FJ_CurrentDataView.PatientId IS NULL AND FJ_VitalStatusCheckedView.PatientId IS NULL /* If they are Current and (no data in the last 14 months or dead) and (vital status has been checked in the last 60 days) then change to next letter/contact, If the last LetterX_Sent was more than 90 days ago then move to NeedLetter1 so can restart the process */ UPDATE Patients SET PtContactStatus =CASE WHEN LastLetterSent IS NULL THEN 'NeedLetter1' WHEN LastLetterSent LIKE 'Letter1%Sent' THEN 'NeedLetter2' WHEN LastLetterSent LIKE 'Letter2%Sent' THEN 'NeedLetter3' WHEN LastLetterSent LIKE 'Letter3%Sent' THEN 'NeedPtContactInfo' END FROM Patients LEFT OUTER JOIN FJ_CurrentDataView ON Patients.PatientId = FJ_CurrentDataView.PatientId INNER JOIN FJ_VitalStatusCheckedView ON Patients.PatientId = FJ_VitalStatusCheckedView.PatientId LEFT OUTER JOIN FJ_LastLetterSentView ON Patients.PatientId = FJ_LastLetterSentView.PatientId WHERE PtContactStatus = 'Current' AND FJ_CurrentDataView.PatientId IS NULL /* If they are LetterX_Sent or NeedLetterX or NeedVitalStatus and (no LetterX_Sent in the last 90 days) and (have data in the last 14 months or dead) change to Current */ UPDATE Patients SET PtContactStatus = 'Current' FROM Patients INNER JOIN FJ_CurrentDataView ON Patients.PatientId = FJ_CurrentDataView.PatientId LEFT OUTER JOIN FJ_RecentLetterSentView ON Patients.PatientId = FJ_RecentLetterSentView.PatientId WHERE (PtContactStatus LIKE 'Letter[1-3]%Sent' OR PtContactStatus LIKE 'NeedLetter[1-3]' OR PtContactStatus = 'NeedVitalStatus') AND FJ_RecentLetterSentView.PatientId IS NULL /* If they are LetterX_Sent only and (no LetterX_Sent in the last 90 days) and (no data in the last 14 months or dead) and (vital status has not been checked in the last 60 days) change to NeedVitalStatus

  • /

UPDATE Patients SET PtContactStatus = 'NeedVitalStatus' FROM Patients LEFT OUTER JOIN FJ_CurrentDataView ON Patients.PatientId = FJ_CurrentDataView.PatientId LEFT OUTER JOIN FJ_RecentLetterSentView ON Patients.PatientId = FJ_RecentLetterSentView.PatientId LEFT OUTER JOIN FJ_VitalStatusCheckedView ON Patients.PatientId = FJ_VitalStatusCheckedView.PatientId WHERE PtContactStatus LIKE 'Letter[1-3]%Sent' AND FJ_CurrentDataView.PatientId IS NULL AND FJ_RecentLetterSentView.PatientId IS NULL AND FJ_VitalStatusCheckedView.PatientId IS NULL /* If they are LetterX_Sent and (no LetterX_Sent in the last 90 days) and (no data in the last 14 months or dead) and (vital status has been checked in the last 60 days) then change to next letter/contact */ UPDATE Patients SET PtContactStatus =CASE WHEN PtContactStatus LIKE 'Letter1%Sent' THEN 'NeedLetter2' WHEN PtContactStatus LIKE 'Letter2%Sent' THEN 'NeedLetter3' WHEN PtContactStatus LIKE 'Letter3%Sent' THEN 'NeedPtContactInfo' END FROM Patients LEFT OUTER JOIN FJ_CurrentDataView ON Patients.PatientId = FJ_CurrentDataView.PatientId LEFT OUTER JOIN FJ_RecentLetterSentView ON Patients.PatientId = FJ_RecentLetterSentView.PatientId INNER JOIN FJ_VitalStatusCheckedView ON Patients.PatientId = FJ_VitalStatusCheckedView.PatientId WHERE PtContactStatus LIKE 'Letter[1-3]%Sent' AND FJ_CurrentDataView.PatientId IS NULL AND FJ_RecentLetterSentView.PatientId IS NULL /* If patient is dead change ContactStatus to Current but only if ContactStatus is not already Current and ContactStatus NOT LIKE '%DoNotContact%'

  • /

UPDATE Patients SET PtContactStatus = 'Current' FROM Patients WHERE (PtContactStatus <> 'Current' AND PtContactStatus NOT LIKE '%DoNotContact%') AND (PtDeathDateText IS NOT NULL) COMMIT TRANSACTION GO


Queries used by Nightly Job

CREATE VIEW dbo.FJ_CurrentDataView AS --Not all tables are represented here Includes deceased patients SELECT a.PatientId AS PatientId FROM ( SELECT BxDate AS SortDate, 'Biopsies' AS TableName, PatientId FROM Biopsies WHERE BxDate > DATEADD(m,-14,GETDATE()) UNION SELECT BxDate AS SortDate, 'BiopsiesProstate' AS TableName, PatientId FROM BiopsiesProstate WHERE BxDate > DATEADD(m,-14,GETDATE()) UNION SELECT ClinStageDate, 'ClinicalStages', PatientId FROM ClinicalStages WHERE ClinStageDate > DATEADD(m,-14,GETDATE()) UNION SELECT DxDate, 'Diagnostics', PatientId FROM Diagnostics WHERE DxDate > DATEADD(m,-14,GETDATE()) UNION SELECT LabDate, 'LabTests', PatientId FROM LabTests WHERE LabDate > DATEADD(m,-14,GETDATE()) UNION SELECT MedTxDate, 'MedicalTherapy', PatientId FROM MedicalTherapy WHERE MedTxDate > DATEADD(m,-14,GETDATE()) UNION SELECT OpDate, 'Procedures', PatientId FROM Procedures WHERE OpDate > DATEADD(m,-14,GETDATE()) UNION SELECT SurgDate, 'Surgery', PatientId FROM Surgeries WHERE SurgDate > DATEADD(m,-14,GETDATE()) UNION SELECT EndoDate, 'Endoscopy', PatientId FROM ProcEndoscopies WHERE EndoDate > DATEADD(m,-14,GETDATE()) UNION SELECT CytoDate, 'Cytology', PatientId FROM Cytologies WHERE CytoDate > DATEADD(m,-14,GETDATE()) UNION SELECT BrachyDate, 'BrachyTherapy', PatientId FROM BrachyTherapy WHERE BrachyDate > DATEADD(m,-14,GETDATE()) UNION SELECT RadTxDate, 'RadiationTherapy', PatientId FROM RadiationTherapy WHERE RadTxDate > DATEADD(m,-14,GETDATE()) UNION SELECT StatusDate, 'Status', PatientId FROM Status WHERE StatusDate > DATEADD(m,-14,GETDATE()) UNION SELECT PtProtocolStatusDate, 'Protocols', PatientId FROM PatientProtocols INNER JOIN PatientProtocolStatus ON PatientProtocolStatus.PatientProtocolId = PatientProtocols.PatientProtocolId WHERE PtProtocolStatusDate > DATEADD(m,-14,GETDATE()) UNION SELECT EncDate, 'Encounters', PatientId FROM Encounters WHERE EncDate > DATEADD(m,-14,GETDATE()) UNION SELECT SurveyDate, 'Surveys', PatientId FROM Surveys WHERE SurveyDate > DATEADD(m,-14,GETDATE()) --Add in deceased patients classified as having a DeathDateText UNION SELECT PtDeathDate, 'Patients',PatientId FROM Patients WHERE (PtDeathDateText IS NOT NULL) ) AS a GROUP BY a.PatientId CREATE VIEW dbo.FJ_VitalStatusCheckedView AS SELECT DISTINCT PatientId FROM Actions WHERE ActionItem = 'VitalStatusChecked' AND ActionDate >= DATEADD(d, - 60, GETDATE()) CREATE VIEW dbo.FJ_LastLetterSentView AS SELECT Actions.PatientId AS PatientId, ActionItem AS LastLetterSent FROM ( SELECT MAX(ActionDate) AS LastLetterDate, PatientId FROM Actions WHERE (ActionItem LIKE 'Letter[1-3]%Sent') AND ActionDate >= DATEADD(d, - 90, GETDATE()) GROUP BY PatientId ) AS a INNER JOIN Actions ON a.PatientId = Actions.PatientId AND a.LastLetterDate = Actions.ActionDate WHERE (ActionItem LIKE 'Letter[1-3]%Sent') CREATE VIEW dbo.FJ_RecentLetterSentView AS SELECT DISTINCT PatientId FROM Actions WHERE ActionItem LIKE 'Letter[1-3]%Sent' AND ActionDate >= DATEADD(d, - 90, GETDATE())












!"£--------------

Stato del contatto - informazione demografica in Caisis

PtContactStatus azione/dato determinante
Current CaseReviewed
DoNotContactPerMD
DoNotContactPerPt DataReceived
Letter1_Sent  
Letter2_Sent Letter2_Sent
Letter3_Sent Letter3_Sent
LostToFollowup PtWebSearched
MD_Contacted MD_Contacted
NeedDataEntry DataEntered
NeedLetter1 Letter1_Sent
NeedLetter2 SentToCDB
NeedLetter3 SentToEMR
NeedMD_Contact VitalStatusChecked
NeedMD_ToCall PtWasCalled
NeedPtAuthorization  
NeedPtContactInfo PtContactInfoUpdated
NeedVitalStatus LetterReturnedToSender

Trigger sulla tabella "Actions"

Ogni volta che un utente modifica o inserisce un record nella tabella "Actions" (Risposta immediata- Dovrebbe rimanere simplice o quanto più semplice)

UPDATE Patients SET PtContactStatus = CASE WHEN i.ActionItem = 'VitalStatusChecked' AND PtContactStatus = 'NeedVitalStatus' THEN 'Current' WHEN i.ActionItem = 'Letter1_Sent' AND PtContactStatus = 'NeedLetter1' THEN 'Letter1_Sent' WHEN i.ActionItem = 'Letter2_Sent' AND PtContactStatus = 'NeedLetter2' THEN 'Letter2_Sent' WHEN i.ActionItem = 'Letter3_Sent' AND PtContactStatus = 'NeedLetter3' THEN 'Letter3_Sent' WHEN i.ActionItem = 'LetterReturnedToSender' AND PtContactStatus LIKE 'Letter[1-3]%Sent' THEN 'NeedPtContactInfo' WHEN i.ActionItem = 'MD_Contacted' AND PtContactStatus = 'NeedMD_Contact' THEN 'MD_Contacted' WHEN i.ActionItem = 'PtWasCalled' AND PtContactStatus = 'NeedMD_ToCallPt' THEN 'Current' WHEN i.ActionItem = 'DataReceived' AND PtContactStatus IN ('Letter1_Sent','Letter2_Sent','Letter3_Sent','MD_Contacted') THEN 'NeedDataEntry' WHEN i.ActionItem = 'DataReceived' AND PtContactStatus = 'NeedPtAuthorization' THEN 'NeedMD_Contact' WHEN i.ActionItem = 'DataEntered' AND PtContactStatus = 'NeedDataEntry' THEN 'Current' WHEN i.ActionItem = 'PtContactInfoUpdated' AND PtContactStatus = 'NeedPtContactInfo' THEN 'Current' END FROM Patients INNER JOIN Inserted i ON Patients.PatientId = i.PatientId WHERE PtContactStatus <> CASE WHEN i.ActionItem = 'VitalStatusChecked' AND PtContactStatus = 'NeedVitalStatus' THEN 'Current' WHEN i.ActionItem = 'Letter1_Sent' AND PtContactStatus = 'NeedLetter1' THEN 'Letter1_Sent' WHEN i.ActionItem = 'Letter2_Sent' AND PtContactStatus = 'NeedLetter2' THEN 'Letter2_Sent' WHEN i.ActionItem = 'Letter3_Sent' AND PtContactStatus = 'NeedLetter3' THEN 'Letter3_Sent' WHEN i.ActionItem = 'LetterReturnedToSender' AND PtContactStatus LIKE 'Letter[1-3]%Sent' THEN 'NeedPtContactInfo' WHEN i.ActionItem = 'MD_Contacted' AND PtContactStatus = 'NeedMD_Contact' THEN 'MD_Contacted' WHEN i.ActionItem = 'PtWasCalled' AND PtContactStatus = 'NeedMD_ToCallPt' THEN 'Current' WHEN i.ActionItem = 'DataReceived' AND PtContactStatus IN ('Letter1_Sent','Letter2_Sent','Letter3_Sent','MD_Contacted') THEN 'NeedDataEntry' WHEN i.ActionItem = 'DataReceived' AND PtContactStatus = 'NeedPtAuthorization' THEN 'NeedMD_Contact' WHEN i.ActionItem = 'DataEntered' AND PtContactStatus = 'NeedDataEntry' THEN 'Current' WHEN i.ActionItem = 'PtContactInfoUpdated' AND PtContactStatus = 'NeedPtContactInfo' THEN 'Current' END

Job notturno (Risposta ritardata, può essere completta)

CREATE PROCEDURE dbo.spFollowupUpdatePtContactStatus AS BEGIN TRANSACTION --Change to current if null UPDATE Patients SET PtContactStatus = 'Current' WHERE PtContactStatus = '0' OR PtContactStatus IS NULL /* If they are Current and (no data in the last 14 months or dead) and (vital status has not been checked in the last 60 days) then change to NeedVitalStatus

  • /

UPDATE Patients SET PtContactStatus = 'NeedVitalStatus' FROM Patients LEFT OUTER JOIN FJ_CurrentDataView ON Patients.PatientId = FJ_CurrentDataView.PatientId LEFT OUTER JOIN FJ_VitalStatusCheckedView ON Patients.PatientId = FJ_VitalStatusCheckedView.PatientId WHERE PtContactStatus = 'Current' AND FJ_CurrentDataView.PatientId IS NULL AND FJ_VitalStatusCheckedView.PatientId IS NULL /* If they are Current and (no data in the last 14 months or dead) and (vital status has been checked in the last 60 days) then change to next letter/contact, If the last LetterX_Sent was more than 90 days ago then move to NeedLetter1 so can restart the process */ UPDATE Patients SET PtContactStatus =CASE WHEN LastLetterSent IS NULL THEN 'NeedLetter1' WHEN LastLetterSent LIKE 'Letter1%Sent' THEN 'NeedLetter2' WHEN LastLetterSent LIKE 'Letter2%Sent' THEN 'NeedLetter3' WHEN LastLetterSent LIKE 'Letter3%Sent' THEN 'NeedPtContactInfo' END FROM Patients LEFT OUTER JOIN FJ_CurrentDataView ON Patients.PatientId = FJ_CurrentDataView.PatientId INNER JOIN FJ_VitalStatusCheckedView ON Patients.PatientId = FJ_VitalStatusCheckedView.PatientId LEFT OUTER JOIN FJ_LastLetterSentView ON Patients.PatientId = FJ_LastLetterSentView.PatientId WHERE PtContactStatus = 'Current' AND FJ_CurrentDataView.PatientId IS NULL /* If they are LetterX_Sent or NeedLetterX or NeedVitalStatus and (no LetterX_Sent in the last 90 days) and (have data in the last 14 months or dead) change to Current */ UPDATE Patients SET PtContactStatus = 'Current' FROM Patients INNER JOIN FJ_CurrentDataView ON Patients.PatientId = FJ_CurrentDataView.PatientId LEFT OUTER JOIN FJ_RecentLetterSentView ON Patients.PatientId = FJ_RecentLetterSentView.PatientId WHERE (PtContactStatus LIKE 'Letter[1-3]%Sent' OR PtContactStatus LIKE 'NeedLetter[1-3]' OR PtContactStatus = 'NeedVitalStatus') AND FJ_RecentLetterSentView.PatientId IS NULL /* If they are LetterX_Sent only and (no LetterX_Sent in the last 90 days) and (no data in the last 14 months or dead) and (vital status has not been checked in the last 60 days) change to NeedVitalStatus

  • /

UPDATE Patients SET PtContactStatus = 'NeedVitalStatus' FROM Patients LEFT OUTER JOIN FJ_CurrentDataView ON Patients.PatientId = FJ_CurrentDataView.PatientId LEFT OUTER JOIN FJ_RecentLetterSentView ON Patients.PatientId = FJ_RecentLetterSentView.PatientId LEFT OUTER JOIN FJ_VitalStatusCheckedView ON Patients.PatientId = FJ_VitalStatusCheckedView.PatientId WHERE PtContactStatus LIKE 'Letter[1-3]%Sent' AND FJ_CurrentDataView.PatientId IS NULL AND FJ_RecentLetterSentView.PatientId IS NULL AND FJ_VitalStatusCheckedView.PatientId IS NULL /* If they are LetterX_Sent and (no LetterX_Sent in the last 90 days) and (no data in the last 14 months or dead) and (vital status has been checked in the last 60 days) then change to next letter/contact */ UPDATE Patients SET PtContactStatus =CASE WHEN PtContactStatus LIKE 'Letter1%Sent' THEN 'NeedLetter2' WHEN PtContactStatus LIKE 'Letter2%Sent' THEN 'NeedLetter3' WHEN PtContactStatus LIKE 'Letter3%Sent' THEN 'NeedPtContactInfo' END FROM Patients LEFT OUTER JOIN FJ_CurrentDataView ON Patients.PatientId = FJ_CurrentDataView.PatientId LEFT OUTER JOIN FJ_RecentLetterSentView ON Patients.PatientId = FJ_RecentLetterSentView.PatientId INNER JOIN FJ_VitalStatusCheckedView ON Patients.PatientId = FJ_VitalStatusCheckedView.PatientId WHERE PtContactStatus LIKE 'Letter[1-3]%Sent' AND FJ_CurrentDataView.PatientId IS NULL AND FJ_RecentLetterSentView.PatientId IS NULL /* If patient is dead change ContactStatus to Current but only if ContactStatus is not already Current and ContactStatus NOT LIKE '%DoNotContact%'

  • /

UPDATE Patients SET PtContactStatus = 'Current' FROM Patients WHERE (PtContactStatus <> 'Current' AND PtContactStatus NOT LIKE '%DoNotContact%') AND (PtDeathDateText IS NOT NULL) COMMIT TRANSACTION GO


Queries utilizzate dal job notturno

CREATE VIEW dbo.FJ_CurrentDataView AS --Not all tables are represented here Includes deceased patients SELECT a.PatientId AS PatientId FROM ( SELECT BxDate AS SortDate, 'Biopsies' AS TableName, PatientId FROM Biopsies WHERE BxDate > DATEADD(m,-14,GETDATE()) UNION SELECT BxDate AS SortDate, 'BiopsiesProstate' AS TableName, PatientId FROM BiopsiesProstate WHERE BxDate > DATEADD(m,-14,GETDATE()) UNION SELECT ClinStageDate, 'ClinicalStages', PatientId FROM ClinicalStages WHERE ClinStageDate > DATEADD(m,-14,GETDATE()) UNION SELECT DxDate, 'Diagnostics', PatientId FROM Diagnostics WHERE DxDate > DATEADD(m,-14,GETDATE()) UNION SELECT LabDate, 'LabTests', PatientId FROM LabTests WHERE LabDate > DATEADD(m,-14,GETDATE()) UNION SELECT MedTxDate, 'MedicalTherapy', PatientId FROM MedicalTherapy WHERE MedTxDate > DATEADD(m,-14,GETDATE()) UNION SELECT OpDate, 'Procedures', PatientId FROM Procedures WHERE OpDate > DATEADD(m,-14,GETDATE()) UNION SELECT SurgDate, 'Surgery', PatientId FROM Surgeries WHERE SurgDate > DATEADD(m,-14,GETDATE()) UNION SELECT EndoDate, 'Endoscopy', PatientId FROM ProcEndoscopies WHERE EndoDate > DATEADD(m,-14,GETDATE()) UNION SELECT CytoDate, 'Cytology', PatientId FROM Cytologies WHERE CytoDate > DATEADD(m,-14,GETDATE()) UNION SELECT BrachyDate, 'BrachyTherapy', PatientId FROM BrachyTherapy WHERE BrachyDate > DATEADD(m,-14,GETDATE()) UNION SELECT RadTxDate, 'RadiationTherapy', PatientId FROM RadiationTherapy WHERE RadTxDate > DATEADD(m,-14,GETDATE()) UNION SELECT StatusDate, 'Status', PatientId FROM Status WHERE StatusDate > DATEADD(m,-14,GETDATE()) UNION SELECT PtProtocolStatusDate, 'Protocols', PatientId FROM PatientProtocols INNER JOIN PatientProtocolStatus ON PatientProtocolStatus.PatientProtocolId = PatientProtocols.PatientProtocolId WHERE PtProtocolStatusDate > DATEADD(m,-14,GETDATE()) UNION SELECT EncDate, 'Encounters', PatientId FROM Encounters WHERE EncDate > DATEADD(m,-14,GETDATE()) UNION SELECT SurveyDate, 'Surveys', PatientId FROM Surveys WHERE SurveyDate > DATEADD(m,-14,GETDATE()) --Add in deceased patients classified as having a DeathDateText UNION SELECT PtDeathDate, 'Patients',PatientId FROM Patients WHERE (PtDeathDateText IS NOT NULL) ) AS a GROUP BY a.PatientId CREATE VIEW dbo.FJ_VitalStatusCheckedView AS SELECT DISTINCT PatientId FROM Actions WHERE ActionItem = 'VitalStatusChecked' AND ActionDate >= DATEADD(d, - 60, GETDATE()) CREATE VIEW dbo.FJ_LastLetterSentView AS SELECT Actions.PatientId AS PatientId, ActionItem AS LastLetterSent FROM ( SELECT MAX(ActionDate) AS LastLetterDate, PatientId FROM Actions WHERE (ActionItem LIKE 'Letter[1-3]%Sent') AND ActionDate >= DATEADD(d, - 90, GETDATE()) GROUP BY PatientId ) AS a INNER JOIN Actions ON a.PatientId = Actions.PatientId AND a.LastLetterDate = Actions.ActionDate WHERE (ActionItem LIKE 'Letter[1-3]%Sent') CREATE VIEW dbo.FJ_RecentLetterSentView AS SELECT DISTINCT PatientId FROM Actions WHERE ActionItem LIKE 'Letter[1-3]%Sent' AND ActionDate >= DATEADD(d, - 90, GETDATE())

Personal tools