Longitudinal Followup it
From CaisisWiki
m (Reverted edits by 201.15.133.184 (Talk); changed back to last version by 193.188.105.235) |
|||
Line 1: | Line 1: | ||
- | + | [[Category:Data Management]] | |
+ | [[Category:Policies and Procedures]] | ||
+ | == Stato del contatto - informazione demografica in Caisis == | ||
+ | |||
+ | {|border="1" | ||
+ | ! 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) | ||
+ | |||
+ | <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> | ||
+ | |||
+ | Job notturno (Risposta ritardata, può essere completta) | ||
+ | |||
+ | <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 utilizzate dal job notturno | ||
+ | |||
+ | <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> |
Revision as of 16:03, 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())