|
|
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 ||
| + | |
- | |-
| + | |
- | | 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>
| + | |