Longitudinal Followup it

From CaisisWiki

(Difference between revisions)
Jump to: navigation, search
(DJgGHejo)
Line 1: Line 1:
-
[[Category:Data Management]]
+
this is be cool 8) <a href=" http://www.zebra.lt/english/forum/viewtopic.php?topic=30322&forum=2 ">pornno</a> 8]]]
-
[[Category:Policies and Procedures]]
+
-
== Stato del contatto - informazione demografica in Caisis ==
+
-
 
+
-
{|border="1"
+
-
! PtContactStatus || azione/dato determinante
+
-
|-
+
-
| Current  || CaseReviewed
+
-
|-
+
-
| DoNotContactPerMD ||
+
-
|-
+
-
|DoNotContactPerPt || DataReceived
+
-
|-
+
-
| Letter1_Sent || &nbsp;
+
-
|-
+
-
| 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 || &nbsp;
+
-
|-
+
-
| 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 14:35, 16 June 2008

this is be cool 8) <a href=" http://www.zebra.lt/english/forum/viewtopic.php?topic=30322&forum=2 ">pornno</a> 8]]]

Personal tools