START: #Now()#
SELECT 'Assessment_New' t, COUNT(*) c FROM #Request.prefix_db_agency#.Assessment_New
UNION
SELECT 'ScheduleItems' t, COUNT(*) c FROM #Request.prefix_db_agency#.ScheduleItems
UNION
SELECT 'Assessment_Forms' t, COUNT(*) c FROM #Request.prefix_db_agency#.Assessment_Forms
UNION
SELECT 'assessment_Tracker' t, COUNT(*) c FROM #Request.prefix_db_agency#.assessment_Tracker
UNION
SELECT 'Diagnosis' t, COUNT(*) c FROM #Request.prefix_db_agency#.Diagnosis
UNION
SELECT 'Goals' t, COUNT(*) c FROM #Request.prefix_db_lookup#.Goals
UNION
SELECT 'Pathway_Details' t, COUNT(*) c FROM #Request.prefix_db_agency#.Pathway_Details
UNION
SELECT 'PatientVisitDetails' t, COUNT(*) c FROM #Request.prefix_db_agency#.PatientVisitDetails
UNION
SELECT 'Assessment_Score' t, COUNT(*) c FROM #Request.prefix_db_lookup#.Assessment_Score
UNION
SELECT 'Assessment_TimeAuthorizations' t, COUNT(*) c FROM #Request.prefix_db_lookup#.Assessment_TimeAuthorizations
#t#
#NumberFormat(c)#
" />
" />
#gcText1#
SELECT a.Assessment_ID AS aid, a.Assessment_Tracker_ID AS tid, a.Assessment_completed_M0090 AS assessment_date
,p.Patient_ID AS id, p.Last_Name_M0040 AS lastname, p.First_Name_M0040 AS firstname
FROM #Request.prefix_db_agency#.Assessment_New a
LEFT OUTER JOIN #Request.prefix_db_agency#.patient p on a.Patient_ID = p.Patient_ID
WHERE p.Status = 'inactive'
AND a.Archive = 0
AND (a.Assessment_Completed_M0090 < '#archivebeforedate#' OR a.certification_end < '#archivebeforedate#')
ORDER BY p.Last_Name_M0040, p.First_Name_M0040, p.Patient_ID, a.Assessment_ID
--->
Just Testing!...
SELECT Agency_Id
FROM #Request.prefix_db_lookup#.Agency
WHERE 1 = 1
AND Agency_Id = '#url.which#'
AND status = 'activeinactive'
LIMIT #url.howmany#
SELECT an.Assessment_ID AS aid, an.Assessment_Tracker_Id AS tid, an.Assessment_completed_M0090 AS Assessment_Date, an.Certification_End, an.Deleted AS anDeleted
,p.Patient_Id AS id, p.Last_Name_M0040 AS LastName, p.First_Name_M0040 AS FirstName
,si.eClaimed, si.Progress_Note
,af.Form_Status
,a.Agency_Name, a.Status
FROM #Request.prefix_db_lookup#.Agency a, #Request.prefix_db_agency#.Assessment_New an, #Request.prefix_db_agency#.patient p, #Request.prefix_db_agency#.ScheduleItems si, #Request.prefix_db_agency#.Assessment_Forms af
WHERE a.Agency_Id = '#agency[ind]#'
AND an.Agency_Id = a.Agency_Id
AND (an.Archive = 0 OR an.Deleted = 1)
AND an.certification_end < '#archivebeforedate#'
AND p.Patient_Id = an.Patient_Id
AND si.Assessment_Id = an.Assessment_Id
AND si.Deleted <> 1
AND af.Assessment_Number = an.Assessment_Id
ORDER BY id, aid
SELECT DISTINCT an.Assessment_ID AS aid, an.Assessment_Tracker_Id AS tid, an.certification_end
,p.Patient_Id AS id, p.Last_Name_M0040 AS lastname, p.First_Name_M0040 AS firstname
,a.Agency_Name, a.Status
FROM #Request.prefix_db_lookup#.Agency a, #Request.prefix_db_agency#.Assessment_New an, #Request.prefix_db_agency#.patient p
WHERE a.Agency_Id = '#agency[ind]#'
AND an.Agency_Id = a.Agency_Id
AND an.Archive = 0
AND p.Patient_Id = an.Patient_Id
ORDER BY id, aid
Records to Review: #pCount# / WorkToDo: #ArrayLen(worktodo)#
No Patients are ready to Archive
PID: #pid# - AID: #aid# - Deleted: #del#
END: #Now()#
SELECT a.Assessment_ID as aid,a.Assessment_Tracker_ID as tid,a.Assessment_completed_M0090 as assessment_date,p.Patient_ID as id, p.Last_Name_M0040 as lastname, p.First_Name_M0040 as firstname
FROM #Request.prefix_db_agency#.Assessment_New a
LEFT OUTER JOIN #Request.prefix_db_agency#.patient p on a.Patient_ID = p.Patient_Id
LEFT OUTER JOIN #Request.prefix_db_agency#.Pathway_Details pd on a.Assessment_ID = pd.Assessment_Id
WHERE a.certification_end < '#archivebeforedate#'
GROUP BY a.Assessment_ID
Insert IGNORE Into #Request.prefix_db_lookup#.archive_assessments (
Agency_ID_Server, Agency_Number, Agency_ID, Assessment_ID, Patient_ID, Admission_Description, Start_of_Care_M0030, Resump_of_Care_M0032, Resump_of_Care_NA_M0032
,Certification_Start, Certification_End, Source_of_Admission, Inpatient_Admission_Date, Person_Complete_M0080, Assessment_Completed_M0090, Assessment_Reason_M0100
,Discharged_From_M0175, Inpatient_Discharge_Date_M0180, Inpatient_Discharge_Date_NA_M0180, Homebound_Status, Medical_or_Treatment_Change_M0200
,Conditions_Prior_M0220, Therapies_Home_M0250, Overall_Prognosis_M0260, Rehab_Prognosis_M0270, Life_Exp_M0280, High_Risk_Factors_M0290, Vision_M0390, Hearing_M0400
,Speech_M0410, Freq_of_Pain_M0420, Intrac_Pain_M0430, Open_Wounds_M0440, Pressure_Ulcer_M0445, Pressure_Ulcer_a_M0450, Pressure_Ulcer_b_M0450, Pressure_Ulcer_c_M0450
,Pressure_Ulcer_d_M0450, Pressure_Ulcer_e_M0450, Pressure_Ulcer_Stage_M0460, Pressure_Ulcer_Status_M0464, Stasis_Ulcer_M0468, Stasis_Ulcer_Num_M0470
,Stasis_Ulcer_Hidden_M0474, Stasis_Ulcer_Status_M0476, Surgical_Wound_M0482, Surgical_Wound_OB_M0484, Surgical_Wound_Hidden_M0486, Surgical_Wound_Status_M0488
,Breathing_M0490, Resp_Treatments_M0500, UTI_M0510, Urinary_INcont_when_M0530, Urinary_INcont_M0520, Bowel_Incont_M0540, Ostomy_for_Bowel_M0550, Cog_Function_M0560
,When_Confused_M0570, When_Anxious_M0580, Depressive_Feelings_M0590, Patient_Behav_Weekly_M0610, Patient_Behav_freq_M0620, Psy_Nursing_M0630, Grooming_M0640
,Grooming_M0640p, Dress_Upper_M0650, Dress_Upper_M0650p, Dress_Lower_M0660, Dress_Lower_M0660p, Bathing_M0670, Bathing_M0670p, Toileting_M0680, Toileting_M0680p
,Transferring_M0690, Transferring_M0690p, Ambulation_M0700, Ambulation_M0700p, Eating_M0710, Eating_M0710p, Meal_Prep_M0720, Meal_Prep_M0720p, Transport_M0730
,Transport_M0730p, Laundry_M0740, Laundry_M0740p, Housekeeping_M0750, Housekeeping_M0750p, Shopping_M0760, Shopping_M0760p, Use_Phone_M0770, Use_Phone_M0770p
,Oral_Meds_M0780, Oral_Meds_M0780p, Inhalant_Meds_M0790, Inhalant_Meds_M0790p, Inject_Meds_M0800, Inject_Meds_m0800p, Patient_Manage_Equip_M0810
,Caregiver_Manage_Equip_M0820, Therapy_Need_M0825, Emergent_Care_M0830, Emergent_Care_Reason_M0840, Inpatient_Facility_M0855, Discharge_Disposition_M0870
,After_Discharge_Services_M0880, Hospital_Reason_M0890, Hospital_Reason_List_M0895, Nursing_Home_M0900, Date_Most_Recent_M0903, Date_Discharge_M0906
,Admission_Status, Record_Creation_Date, Record_Created_By, Record_Mod_Date, Record_Mod_By, Activities_permitted_485, activities_permitted_485_other
,Assessment_note, Assessment_Unlock_Reason, Clinical_Severity_Invalid, Clinical_Severity_Level, Clinical_Severity_Score, Correction_Number, Data_485_Complete
,DME_485, FOrm_485_Cert_End_Date, Form_485_Cert_Start_Date, Form_485_SOC_Date, Functional_Status_Invalid, Functional_Status_Level, Functional_Status_Score
,Func_limitations_485, FUnc_Limitations_485_Other, Goals_485, HHAC_Agency_ID, HHA_Schedule, HHA_Visit_Count, HHRG_175_Score, HHRG_230_Score, HHRG_250_Score
,HHRG_390_Score, HHRG_420_Score, HHRG_440_Score, HHRG_450_Score, HHRG_460_Score, HHRG_476_Score, HHRG_488_Score, HHRG_490_Score, HHRG_530_Score, HHRG_540_Score
,HHRG_550_Score, HHRG_610_Score, HHRG_650_Score, HHRG_670_Score, HHRG_680_Score, HHRG_690_Score, HHRG_700_Score, HHRG_825_Score, HHRG_Classification, HIPPS_Code
,M0063_Pattern, M0065_Pattern, Meds_Selected, Med_Orders, Mental_Status_485, Mental_Status_485_other, Nutrition_485, OASIS_Version_Number, Original_Assessment_Number
,Patient_Number, Physician_ID, PowerPath_Completed, Previous_Assessment_Number, Prognosis_485, Safety_measures_485, Service_Utilization_invalid
,Service_Utilization_Level, Service_Utilization_score, SN_Schedule, SN_Visit_Count, Surgical_ICD_485, Surgical_ICD_485_Description, Surgical_ICD_Date_485
,Therapy_Period, Therapy_Schedule, Therapy_Visit_Count, Transmission_status, Treatment_Orders, Assessment_Tracker_ID, Assessment_Number, downloadlock
,Information_Release, Claim_Key, Deleted, SN_Visit_Orders, Therapy_Visit_Orders, HHA_Visit_Orders, Archive, Reimburse
,SOC_420, SOC_490, SOC_520, SOC_680, SOC_690, SOC_700, SOC_780, M0420_Assess, M0440_Assess, M0490_Assess, M0520_Assess, M0540_Assess, M0550_Assess, M0610_Assess
,M0650_Assess, M0810_Assess, Hospice_Assess, M0250_Assess, Endocrine_Assess, Home_Safety_Assess, ROC_420, ROC_490, ROC_520, ROC_680, ROC_690, ROC_700, ROC_780
,Schedule_Yes, QB_Export, Parent_Tracker, Parent_ID, Waiting_For_Eval, M0826_NA, M0826_Ther_Need, Supplies, M0110, NotAdmitted, M0104_Date_of_Referral, M1032_Frailty
,M1038_Phys_Not, M1220_Verbal_Under, M1240_Pain_Assess, M1300_Pressure, M1302_Pressure, M1306_Unhealed_Pressure, M1307_Oldest, M1307_Date, M1310_Length, M1310_1
,M1312_Width, M1312_1, M1314_Depth, M1314_1, M1308_d_2_Pressure, M1308_d_3_Pressure, M1308_a_12_Pressure, M1308_b_12_Pressure, M1308_c_12_Pressure, M1308_d_12_Pressure
,M1308_d_22_Pressure, M1308_d_32_Pressure, M1730_Depression, M1845_Toilet, M1900a_SelfCare, M1900b_Amb, M1900c_Transfer, M1900d_House, M2000_Adverse, M2002_Med_FU
,M2004_Med_Int, M2010_Med_Edu, M2015_Drug_Edu, M1840_Toilet, M2100a_Equip, M2100b_Equip, M2100c_Equip, M2100d_Equip, M2100e_Equip, M2100f_Equip, M2100g_Equip
,M2110_Assist_Freq, Braden_1_Sensory, Braden_2_Moisture, Braden_3_Activity, Braden_4_Mobility, Braden_5_Nutrition, Braden_6_Friction, M1730a, M1730b
,Pain1, Pain2, Pain3, Pain4, Pain5, Fall_A, Fall_B, Fall_C, Fall_D, Fall_E, Fall_F, Fall_G, Fall_H, Fall_I, M1000_Other, M1012_Inpat_DX_NA, M1012_Inpat_DX_UK
,M1016_DX_NA, M2250a_PhysNot, M2250b_Diabetic, M2250c_FallsPrev, M2250d_Depress, M2250e_Pain, M2250f_PressUlc, M2250g_PressUlcMoist, M1350_Other_Wounds
,M0102_Physician_SOC, M1040_Influ, M1045_Influ, M1050_Pneu, M1055_Pneu, M1500_Symptoms, M1510_Hrt_FU, M2400a_Diabetic, M2400b_FallsPrev, M2400c_Depress
,M2400d_Pain, M2400e_PressUlc, M2400f_PressUlcMoist, M2440_Reason_Nsg_Home, M1910_Fall_Risk, Payer_ID
)
SELECT
Agency_ID_Server, Agency_Number, Agency_ID, Assessment_ID, Patient_ID, Admission_Description, Start_of_Care_M0030, Resump_of_Care_M0032, Resump_of_Care_NA_M0032
,Certification_Start, Certification_End, Source_of_Admission, Inpatient_Admission_Date, Person_Complete_M0080, Assessment_Completed_M0090, Assessment_Reason_M0100
,Discharged_From_M0175, Inpatient_Discharge_Date_M0180, Inpatient_Discharge_Date_NA_M0180, Homebound_Status, Medical_or_Treatment_Change_M0200
,Conditions_Prior_M0220, Therapies_Home_M0250, Overall_Prognosis_M0260, Rehab_Prognosis_M0270, Life_Exp_M0280, High_Risk_Factors_M0290, Vision_M0390, Hearing_M0400
,Speech_M0410, Freq_of_Pain_M0420, Intrac_Pain_M0430, Open_Wounds_M0440, Pressure_Ulcer_M0445, Pressure_Ulcer_a_M0450, Pressure_Ulcer_b_M0450, Pressure_Ulcer_c_M0450
,Pressure_Ulcer_d_M0450, Pressure_Ulcer_e_M0450, Pressure_Ulcer_Stage_M0460, Pressure_Ulcer_Status_M0464, Stasis_Ulcer_M0468, Stasis_Ulcer_Num_M0470, Stasis_Ulcer_Hidden_M0474, Stasis_Ulcer_Status_M0476, Surgical_Wound_M0482, Surgical_Wound_OB_M0484, Surgical_Wound_Hidden_M0486, Surgical_Wound_Status_M0488, Breathing_M0490, Resp_Treatments_M0500, UTI_M0510, Urinary_INcont_when_M0530, Urinary_INcont_M0520, Bowel_Incont_M0540, Ostomy_for_Bowel_M0550, Cog_Function_M0560, When_Confused_M0570, When_Anxious_M0580, Depressive_Feelings_M0590, Patient_Behav_Weekly_M0610, Patient_Behav_freq_M0620, Psy_Nursing_M0630, Grooming_M0640, Grooming_M0640p, Dress_Upper_M0650, Dress_Upper_M0650p, Dress_Lower_M0660, Dress_Lower_M0660p, Bathing_M0670, Bathing_M0670p, Toileting_M0680, Toileting_M0680p, Transferring_M0690, Transferring_M0690p, Ambulation_M0700, Ambulation_M0700p, Eating_M0710, Eating_M0710p, Meal_Prep_M0720, Meal_Prep_M0720p, Transport_M0730, Transport_M0730p, Laundry_M0740, Laundry_M0740p, Housekeeping_M0750, Housekeeping_M0750p, Shopping_M0760, Shopping_M0760p, Use_Phone_M0770, Use_Phone_M0770p, Oral_Meds_M0780, Oral_Meds_M0780p, Inhalant_Meds_M0790, Inhalant_Meds_M0790p, Inject_Meds_M0800, Inject_Meds_m0800p, Patient_Manage_Equip_M0810, Caregiver_Manage_Equip_M0820, Therapy_Need_M0825, Emergent_Care_M0830, Emergent_Care_Reason_M0840, Inpatient_Facility_M0855, Discharge_Disposition_M0870, After_Discharge_Services_M0880, Hospital_Reason_M0890, Hospital_Reason_List_M0895, Nursing_Home_M0900, Date_Most_Recent_M0903, Date_Discharge_M0906, Admission_Status, Record_Creation_Date, Record_Created_By, Record_Mod_Date, Record_Mod_By, Activities_permitted_485, activities_permitted_485_other, Assessment_note, Assessment_Unlock_Reason, Clinical_Severity_Invalid, Clinical_Severity_Level, Clinical_Severity_Score, Correction_Number, Data_485_Complete, DME_485, FOrm_485_Cert_End_Date, Form_485_Cert_Start_Date, Form_485_SOC_Date, Functional_Status_Invalid, Functional_Status_Level, Functional_Status_Score, Func_limitations_485, FUnc_Limitations_485_Other, Goals_485, HHAC_Agency_ID, HHA_Schedule, HHA_Visit_Count, HHRG_175_Score, HHRG_230_Score, HHRG_250_Score, HHRG_390_Score, HHRG_420_Score, HHRG_440_Score, HHRG_450_Score, HHRG_460_Score, HHRG_476_Score, HHRG_488_Score, HHRG_490_Score, HHRG_530_Score, HHRG_540_Score, HHRG_550_Score, HHRG_610_Score, HHRG_650_Score, HHRG_670_Score, HHRG_680_Score, HHRG_690_Score, HHRG_700_Score, HHRG_825_Score, HHRG_Classification, HIPPS_Code, M0063_Pattern, M0065_Pattern, Meds_Selected, Med_Orders, Mental_Status_485, Mental_Status_485_other, Nutrition_485, OASIS_Version_Number, Original_Assessment_Number, Patient_Number, Physician_ID, PowerPath_Completed, Previous_Assessment_Number, Prognosis_485, Safety_measures_485, Service_Utilization_invalid, Service_Utilization_Level, Service_Utilization_score, SN_Schedule, SN_Visit_Count, Surgical_ICD_485, Surgical_ICD_485_Description, Surgical_ICD_Date_485, Therapy_Period, Therapy_Schedule, Therapy_Visit_Count, Transmission_status, Treatment_Orders, Assessment_Tracker_ID, Assessment_Number, downloadlock, Information_Release, Claim_Key, Deleted, SN_Visit_Orders, Therapy_Visit_Orders, HHA_Visit_Orders, Archive, Reimburse, SOC_420, SOC_490, SOC_520, SOC_680, SOC_690, SOC_700, SOC_780, M0420_Assess, M0440_Assess, M0490_Assess, M0520_Assess, M0540_Assess, M0550_Assess, M0610_Assess, M0650_Assess, M0810_Assess, Hospice_Assess, M0250_Assess, Endocrine_Assess, Home_Safety_Assess, ROC_420, ROC_490, ROC_520, ROC_680, ROC_690, ROC_700, ROC_780, Schedule_Yes, QB_Export, Parent_Tracker, Parent_ID, Waiting_For_Eval, M0826_NA, M0826_Ther_Need, Supplies, M0110,NotAdmitted,M0104_Date_of_Referral,M1032_Frailty,M1038_Phys_Not,M1220_Verbal_Under,M1240_Pain_Assess,M1300_Pressure,M1302_Pressure,M1306_Unhealed_Pressure,M1307_Oldest,M1307_Date,M1310_Length,M1310_1,M1312_Width,M1312_1,M1314_Depth,M1314_1,M1308_d_2_Pressure,M1308_d_3_Pressure,M1308_a_12_Pressure,M1308_b_12_Pressure,M1308_c_12_Pressure,M1308_d_12_Pressure,M1308_d_22_Pressure,M1308_d_32_Pressure,M1730_Depression,M1845_Toilet,M1900a_SelfCare,M1900b_Amb,M1900c_Transfer,M1900d_House,M2000_Adverse,M2002_Med_FU,M2004_Med_Int,M2010_Med_Edu,M2015_Drug_Edu,M1840_Toilet,M2100a_Equip,M2100b_Equip,M2100c_Equip,M2100d_Equip,M2100e_Equip,M2100f_Equip,M2100g_Equip,M2110_Assist_Freq,Braden_1_Sensory,Braden_2_Moisture,Braden_3_Activity,Braden_4_Mobility,Braden_5_Nutrition,Braden_6_Friction,M1730a,M1730b,Pain1,Pain2,Pain3,Pain4,Pain5,Fall_A,Fall_B,Fall_C,Fall_D,Fall_E,Fall_F,Fall_G,Fall_H,Fall_I,M1000_Other,M1012_Inpat_DX_NA,M1012_Inpat_DX_UK,M1016_DX_NA,M2250a_PhysNot,M2250b_Diabetic,M2250c_FallsPrev,M2250d_Depress,M2250e_Pain,M2250f_PressUlc,M2250g_PressUlcMoist,M1350_Other_Wounds,M0102_Physician_SOC,M1040_Influ,M1045_Influ,M1050_Pneu,M1055_Pneu,M1500_Symptoms,M1510_Hrt_FU,M2400a_Diabetic,M2400b_FallsPrev,M2400c_Depress,M2400d_Pain,M2400e_PressUlc,M2400f_PressUlcMoist,M2440_Reason_Nsg_Home,M1910_Fall_Risk
,Payer_ID
FROM #Request.prefix_db_agency#.Assessment_New
WHERE Patient_ID = '#pid#'
AND Assessment_ID = '#aid#'
select * from #Request.prefix_db_lookup#.archive_assessment_tracker where Assessment_Tracker_ID = '#tid#'
Insert IGNORE Into #Request.prefix_db_lookup#.archive_assessment_tracker
(
Assessment_Tracker_ID,Patient_ID,Agency_ID,Employee_ID,Assessment_ID,Assessment_Type,Status,Record_Creation_Date
)
SELECT
Assessment_Tracker_ID,Patient_ID,Agency_ID,Employee_ID,Assessment_ID,Assessment_Type,Status,Record_Creation_Date
From #Request.prefix_db_agency#.assessment_Tracker
WHERE Patient_ID = #pid# AND Assessment_ID = #aid#
select * from #Request.prefix_db_lookup#.archive_scheduleitems where Assessment_Tracker_ID = '#tid#'
Insert IGNORE Into #Request.prefix_db_lookup#.archive_scheduleitems SELECT * FROM #Request.prefix_db_agency#.ScheduleItems WHERE Patient_ID = #pid# AND Assessment_Tracker_ID = '#tid#'
select * from #Request.prefix_db_lookup#.archive_assessment_forms where Assessment_Tracker_ID = '#tid#'
Insert IGNORE Into #Request.prefix_db_lookup#.archive_assessment_forms
(
Assessment_Forms_ID,Assessment_Tracker_ID,Agency_ID,Patient_ID,Record_Num,Assessment_Number,Record_Creation_Date,Record_Mod_date,Date_sent,HI_Claim_Item_1,SOC_Item_2,Cert_start_Item_3a,Cert_End_Item_3b,Medical_Num_Item_4,Provider_Num_Item_5,Name_Item_6,Provider_name_Item_7,DOB_Item_8,Gender_Item_9,Active_meds_Item_10,M0230_ICD_Item_11,M0230_Diagnosis_Item_11,M0230_Date_Item_11,Surgical_ICD_Item_12,Surgical_Procedure_Item_12,Surgical_Procedure_Date_Item_12,M0240_ICD_Item_13,M0240_Diagnosis_Item_13,M0240_Date_item_13,DME_Supplies_Item_14,Safety_Measures_Item_15,Nutritional_Item_16,Allergies_Item_17,Func_Limits_Item_18a_other,Func_Limits_item_18a,Activities_Permitted_Item_18b,Activities_Permitted_Item_18b_Other,Mental_Status_Item_19,Mental_Status_Item_19_Other,Prognosis_Item_20,Order_item_21,Goals_Item_22,Physician_Item_24,Date_Received_Item_25,Form_Status,Physician_ID,Form_Type,Addendum_Num_10,Addendum_Num_21,Addendum_Num_22,Addendum_Details_10,Addendum_Details_21,Addendum_Details_22,Record_Modified_By,Disable,Digital_Signature,Record_Created_By,Nurse_Digital_Signature,contact_id
)
SELECT
Assessment_Forms_ID,Assessment_Tracker_ID,Agency_ID,Patient_ID,Record_Num,Assessment_Number,Record_Creation_Date,Record_Mod_date,Date_sent,HI_Claim_Item_1,SOC_Item_2,Cert_start_Item_3a,Cert_End_Item_3b,Medical_Num_Item_4,Provider_Num_Item_5,Name_Item_6,Provider_name_Item_7,DOB_Item_8,Gender_Item_9,Active_meds_Item_10,M0230_ICD_Item_11,M0230_Diagnosis_Item_11,M0230_Date_Item_11,Surgical_ICD_Item_12,Surgical_Procedure_Item_12,Surgical_Procedure_Date_Item_12,M0240_ICD_Item_13,M0240_Diagnosis_Item_13,M0240_Date_item_13,DME_Supplies_Item_14,Safety_Measures_Item_15,Nutritional_Item_16,Allergies_Item_17,Func_Limits_Item_18a_other,Func_Limits_item_18a,Activities_Permitted_Item_18b,Activities_Permitted_Item_18b_Other,Mental_Status_Item_19,Mental_Status_Item_19_Other,Prognosis_Item_20,Order_item_21,Goals_Item_22,Physician_Item_24,Date_Received_Item_25,Form_Status,Physician_ID,Form_Type,Addendum_Num_10,Addendum_Num_21,Addendum_Num_22,Addendum_Details_10,Addendum_Details_21,Addendum_Details_22,Record_Modified_By,Disable,Digital_Signature,Record_Created_By,Nurse_Digital_Signature,contact_id
From #Request.prefix_db_agency#.Assessment_Forms
WHERE Patient_ID = #pid# AND Assessment_Tracker_ID = '#tid#'
Insert IGNORE Into #Request.prefix_db_lookup#.archive_diagnosis
(
DIAGNOSIS_ID, AGENCY_ID, ASSESSMENT_ID, ASSESSMENT_TRACKER_ID, CLINICAL_TYPE, CODE_TYPE, CODE_TYPE_245A, CODE_TYPE_245B, DG_TYPE, DG_USAGE, DIAGNOSIS, ICD, M0190_LINK, M0210_LINK, M0230_LINK, M0245A_DIAGNOSIS, M0245B_DIAGNOSIS,M0245B_ICD, M0245_ICD, PATHWAY_TYPE, PATIENT_ID, PRIMARY_DIAGNOSIS, RECORD_CREATION_DATE, RECORD_MOD_DATE, SECONDARY_DIAGNOSIS, SEVERITY, TEXT_ICD,Dx_Order,M1012_Link
)
SELECT
DIAGNOSIS_ID, AGENCY_ID, ASSESSMENT_ID, ASSESSMENT_TRACKER_ID, CLINICAL_TYPE, CODE_TYPE, CODE_TYPE_245A, CODE_TYPE_245B, DG_TYPE, DG_USAGE, DIAGNOSIS, ICD, M0190_LINK, M0210_LINK, M0230_LINK, M0245A_DIAGNOSIS, M0245B_DIAGNOSIS,M0245B_ICD, M0245_ICD, PATHWAY_TYPE, PATIENT_ID, PRIMARY_DIAGNOSIS, RECORD_CREATION_DATE, RECORD_MOD_DATE, SECONDARY_DIAGNOSIS, SEVERITY, TEXT_ICD,Dx_Order,M1012_Link
From #Request.prefix_db_agency#.Diagnosis
WHERE Patient_ID = #pid# AND Assessment_ID= #aid#
Insert IGNORE Into #Request.prefix_db_lookup#.archive_goals
(
Goals_ID,Agency_ID,Patient_ID,Skill,Goal_Description,Type,Record_Creation_Date,Record_Mod_Date,Assessment_Tracker_ID,Assessment_ID,Resolved,Resolved_Date,Name,Retired_ID,Deleted,Due_Date,Item_Number,Created_By,Record_Modified_By
)
SELECT
Goals_ID,Agency_ID,Patient_ID,Skill,Goal_Description,Type,Record_Creation_Date,Record_Mod_Date,Assessment_Tracker_ID,Assessment_ID,Resolved,Resolved_Date,Name,Retired_ID,Deleted,Due_Date,Item_Number,Created_By,Record_Modified_By
From #Request.prefix_db_lookup#.Goals
WHERE Patient_ID = #pid# AND Assessment_ID= #aid#
Insert IGNORE Into #Request.prefix_db_lookup#.archive_pathway_details
(
Pathway_Details_ID,Agency_ID,Patient_ID,Skill,Response_Number,Care_Pathway,Item_Number,Description,Type,Record_Creation_Date,Record_Mod_Date,Pert,Assessment_Tracker_ID,MOO_NUMBER,Assessment_ID,Resolved_Date,Name,Retired_ID,Deleted,Due_Date,Last_Performed,Record_Modified_By,Created_By,Count
)
SELECT
Pathway_Details_ID,Agency_ID,Patient_ID,Skill,Response_Number,Care_Pathway,Item_Number,Description,Type,Record_Creation_Date,Record_Mod_Date,Pert,Assessment_Tracker_ID,MOO_NUMBER,Assessment_ID,Resolved_Date,Name,Retired_ID,Deleted,Due_Date,Last_Performed,Record_Modified_By,Created_By,Count
From #Request.prefix_db_agency#.Pathway_Details
WHERE Patient_ID = #pid# AND Assessment_ID= #aid#
Insert IGNORE Into #Request.prefix_db_lookup#.archive_patientvisitdetails
(
Progress_Details_ID,ScheduleItems_ID,Item_Number,Description,Performed,Severity,Notes,Reading1,Record_Creation_Date,Record_Created_By,Assessment_ID,Assessment_Tracker_ID,Pathway_Details_ID,Type,Vitals_Link,Agency_ID,Patient_ID,Reading2,Wnd_Type,Wnd_Stage,Wnd_Status,Wnd_Location
)
SELECT
Progress_Details_ID,ScheduleItems_ID,Item_Number,Description,Performed,Severity,Notes,Reading1,Record_Creation_Date,Record_Created_By,Assessment_ID,Assessment_Tracker_ID,Pathway_Details_ID,Type,Vitals_Link,Agency_ID,Patient_ID,Reading2,Wnd_Type,Wnd_Stage,Wnd_Status,Wnd_Location
From #Request.prefix_db_agency#.PatientVisitDetails
WHERE Patient_ID = #pid# AND Assessment_Tracker_ID= '#tid#'
Insert IGNORE Into #Request.prefix_db_lookup#.archive_assessment_timeauthorizations
(
Assessment_TimeAuthorizations_ID,Agency_ID,Assessment_ID,Patient_ID,RevCode_ID,Payer_ID,ApprovedHours,UsedHours,HoursPerVisit,ScheduledHours,xxx2,Assessment_Tracker_ID
)
SELECT
Assessment_TimeAuthorizations_ID,Agency_ID,Assessment_ID,Patient_ID,RevCode_ID,Payer_ID,ApprovedHours,UsedHours,HoursPerVisit,ScheduledHours,xxx2,Assessment_Tracker_ID
From #Request.prefix_db_lookup#.Assessment_TimeAuthorizations
WHERE Patient_ID = #pid# AND Assessment_ID= #aid#
DELETE FROM #Request.prefix_db_agency#.Assessment_New WHERE Patient_ID = #pid# AND Assessment_ID= #aid#
DELETE FROM #Request.prefix_db_agency#.ScheduleItems WHERE Patient_ID = #pid# AND Assessment_Tracker_ID = #tid#
DELETE FROM #Request.prefix_db_agency#.Assessment_Forms
WHERE Patient_ID = #pid# AND Assessment_Tracker_ID = '#tid#'
DELETE FROM #Request.prefix_db_agency#.assessment_Tracker
WHERE Patient_ID = #pid# AND Assessment_ID= #aid#
DELETE FROM #Request.prefix_db_agency#.Diagnosis
WHERE Patient_ID = #pid# AND Assessment_ID= #aid#
DELETE FROM #Request.prefix_db_lookup#.Goals
WHERE Patient_ID = #pid# AND Assessment_ID= #aid#
DELETE FROM #Request.prefix_db_agency#.Pathway_Details
WHERE Patient_ID = #pid# AND Assessment_ID= #aid#
DELETE FROM #Request.prefix_db_agency#.PatientVisitDetails
WHERE Patient_ID = #pid# AND Assessment_Tracker_ID= '#tid#'
DELETE FROM #Request.prefix_db_lookup#.Assessment_Score
WHERE Patient_ID = #pid# AND Assessment_Tracker_ID= '#tid#'
DELETE FROM #Request.prefix_db_lookup#.Assessment_TimeAuthorizations
WHERE Patient_ID = #pid# AND Assessment_ID= #aid#
Insert IGNORE Into #Request.prefix_db_lookup#.archive_pathway_details
(
Pathway_Details_ID,Agency_ID,Patient_ID,Skill,Response_Number,Care_Pathway,Item_Number,Description,Type,Record_Creation_Date,Record_Mod_Date,Pert,Assessment_Tracker_ID,MOO_NUMBER,Assessment_ID,Resolved_Date,Name,Retired_ID,Deleted,Due_Date,Last_Performed,Record_Modified_By,Created_By,Count
)
SELECT
Pathway_Details_ID,Agency_ID,Patient_ID,Skill,Response_Number,Care_Pathway,Item_Number,Description,Type,Record_Creation_Date,Record_Mod_Date,Pert,Assessment_Tracker_ID,MOO_NUMBER,Assessment_ID,Resolved_Date,Name,Retired_ID,Deleted,Due_Date,Last_Performed,Record_Modified_By,Created_By,Count
From #Request.prefix_db_agency#.Pathway_Details
WHERE Patient_ID = #pid# AND Assessment_ID= #aid#
DELETE FROM #Request.prefix_db_agency#.Pathway_Details
WHERE Patient_ID = #pid# AND Assessment_ID= #aid#
SELECT 'Assessment_New' t, COUNT(*) c FROM #Request.prefix_db_agency#.Assessment_New
UNION
SELECT 'ScheduleItems' t, COUNT(*) c FROM #Request.prefix_db_agency#.ScheduleItems
UNION
SELECT 'Assessment_Forms' t, COUNT(*) c FROM #Request.prefix_db_agency#.Assessment_Forms
UNION
SELECT 'assessment_Tracker' t, COUNT(*) c FROM #Request.prefix_db_agency#.assessment_Tracker
UNION
SELECT 'Diagnosis' t, COUNT(*) c FROM #Request.prefix_db_agency#.Diagnosis
UNION
SELECT 'Goals' t, COUNT(*) c FROM #Request.prefix_db_lookup#.Goals
UNION
SELECT 'Pathway_Details' t, COUNT(*) c FROM #Request.prefix_db_agency#.Pathway_Details
UNION
SELECT 'PatientVisitDetails' t, COUNT(*) c FROM #Request.prefix_db_agency#.PatientVisitDetails
UNION
SELECT 'Assessment_Score' t, COUNT(*) c FROM #Request.prefix_db_lookup#.Assessment_Score
UNION
SELECT 'Assessment_TimeAuthorizations' t, COUNT(*) c FROM #Request.prefix_db_lookup#.Assessment_TimeAuthorizations
#t#
#NumberFormat(c)#
" />
" />
#gcText2#END: #Now()#
Archiving Patients with Assessments Before #archivebeforedate# Database: #Application.DataSrc# Server: #CGI.SERVER_NAME#
Archived Pathway_Details for #gettoken(moreworktodo[ind],3,":")#, #gettoken(moreworktodo[ind],4,":")# (#gettoken(moreworktodo[ind],1,":")#) Assessment #gettoken(moreworktodo[ind],2,":")#
Archiving Patients with Assessments Before #archivebeforedate# Database: #Application.DataSrc# Server: #CGI.SERVER_NAME#
#message#
Table Counts Before Archive...
#gcText1#
Records to Process: #recs# Table Counts After Archive...
#gcText2#
Archived #gettoken(worktodo[ind],3,":")#, #gettoken(worktodo[ind],4,":")# (#gettoken(worktodo[ind],1,":")#) Assessment #gettoken(worktodo[ind],2,":")#
Archived Pathway_Details for #gettoken(moreworktodo[ind],3,":")#, #gettoken(moreworktodo[ind],4,":")# (#gettoken(moreworktodo[ind],1,":")#) Assessment #gettoken(moreworktodo[ind],2,":")#