Longitudinal Followup it

From CaisisWiki

(Difference between revisions)
Jump to: navigation, search
Line 1: Line 1:
[[Category:Data Management]]
[[Category:Data Management]]
[[Category:Policies and Procedures]]
[[Category:Policies and Procedures]]
-
 
== Stato del contatto - informazione demografica in Caisis ==
== Stato del contatto - informazione demografica in Caisis ==

Revision as of 14:22, 16 June 2008

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