DELETE FROM #Request.prefix_db_agency#.LUPA INSERT INTO #Request.prefix_db_agency#.LUPA ( EP_or_FFS,Schedule_ID, Patient_ID, Assmt_ID, PtPayer_ID, SOC_Date, First_or_second_Period, Start_Date, End_Date, Total_Periods, Days_in_Month, HHRG, Total_Visits, Therapy_Visits, Payment, Daily_Rate, CM_Gross, CM_Sequester, CM_VBP, LUPA, CM_LUPA, CM_Outlier, Total_CM, Daily_Adjust, CM_Net, Scheduled_Visits, Actual_Reimbursement_Amount, Payments,discharged_date, GL_Code, record_timestamp) SELECT pPayer.Output,pSchedules.Schedule_ID, pPatients.Patient_ID,pAssessments.Assmt_ID,pPtPayer.PtPayer_ID, pAdmit.M0030_START_CARE_DT, CASE WHEN pSchedules.Visit_Date IS NULL THEN CASE WHEN DATEDIFF(pAssessments.F177,pAssessments.F178) < 30 THEN "FIRST FINAL" WHEN DATEDIFF(pAssessments.F177,pAssessments.F178) >= 30 THEN "SECOND FINAL" ELSE "0" END ELSE CASE WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) < 30 THEN "FIRST FINAL" WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) >= 30 THEN "SECOND FINAL" ELSE "0" END END as First_or_second_Period, CASE WHEN pSchedules.Visit_Date IS NULL THEN CASE WHEN DATEDIFF(pAssessments.F177,pAssessments.F178) < 30 THEN pAssessments.F178 WHEN DATEDIFF(pAssessments.F177,pAssessments.F178) >= 30 THEN DATE_ADD(pAssessments.F178, INTERVAL 30 DAY) ELSE pAssessments.F178 END ELSE CASE WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) < 30 THEN pAssessments.F177 WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) >= 30 THEN DATE_ADD(pAssessments.F177, INTERVAL 30 DAY) ELSE pAssessments.F177 END END as start_date, CASE WHEN pSchedules.Visit_Date IS NULL THEN CASE WHEN DATEDIFF(pAssessments.F177,pAssessments.F178) < 30 THEN DATE_ADD(pAssessments.F178, INTERVAL 29 DAY) WHEN DATEDIFF(pAssessments.F177,pAssessments.F178) >= 30 THEN DATE_ADD(pAssessments.F178, INTERVAL 59 DAY) ELSE pAssessments.F178 END ELSE CASE WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) < 30 THEN DATE_ADD(pAssessments.F177, INTERVAL 29 DAY) WHEN DATEDIFF(pSchedules.Visit_Date,pAssessments.F177) >= 30 THEN DATE_ADD(pAssessments.F177, INTERVAL 59 DAY) ELSE pAssessments.F178 END END as end_date, ROUND((DATEDIFF(NOW(),pAdmit.M0030_START_CARE_DT)/30),0) AS Total_Periods, 0 AS Days_in_Month, 0 AS HHRG, 0 AS Total_Visits, 0 AS Therapy_Visits, 0 AS Payment, 0 AS Daily_Rate, 0 AS CM_Gross, 0 AS CM_Sequester, 0 AS CM_VBP, 0 AS LUPA, 0 AS CM_LUPA, 0 AS CM_Outlier, 0 AS Total_CM, 0 AS Daily_Adjust, 0 AS CM_Net, 0 AS Scheduled_Visits, IFNULL((SELECT SUM(Payments.Amount) FROM #Request.prefix_db_agency#.Payments WHERE Payments.Invoice_ID = Invoices.Invoice_ID AND Payments.PatientPayer_ID = pPayer.Pay_Id AND Invoices.Deleted = 0 AND Payments.Deleted = 0 ),0.00) AS Actual_Reimbursement_Amount, IFNULL((SELECT SUM(Payments.writeoffamount) FROM #Request.prefix_db_agency#.Payments WHERE Payments.Invoice_ID = Invoices.Invoice_ID AND Payments.PatientPayer_ID = pPayer.Pay_Id AND Invoices.Deleted = 0 AND Payments.Deleted = 0 ),0.00) AS Payments, CASE WHEN ( SELECT F176 FROM #Request.prefix_db_agency#.pAssessments pass WHERE (pass.F26 LIKE '9%' OR pass.F26 LIKE '7%' OR pass.F26 LIKE '8%') AND pass.Status = 0 AND pass.Admit_ID = pAdmit.Admit_ID AND pass.Patient_ID = pPatients.Patient_ID AND MONTH(pass.F176) <= MONTH('#form.monthYear#') AND YEAR(pass.F176) <= YEAR('#form.monthYear#') LIMIT 1 ) IS NOT NULL THEN ( SELECT F176 FROM #Request.prefix_db_agency#.pAssessments pass WHERE (pass.F26 LIKE '9%' OR pass.F26 LIKE '7%' OR pass.F26 LIKE '8%') AND pass.Status = 0 AND pass.Admit_ID = pAdmit.Admit_ID AND pass.Patient_ID = pPatients.Patient_ID AND MONTH(pass.F176) <= MONTH('#form.monthYear#') AND YEAR(pass.F176) <= YEAR('#form.monthYear#') LIMIT 1 ) ELSE '2050-01-01' END AS discharged_date, 0 AS GL_Code, now() FROM #Request.prefix_db_agency#.pAssessments JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Admit_ID = pAssessments.Admit_ID AND pAdmit.status = 0 LEFT OUTER JOIN #Request.prefix_db_agency#.pSchedules ON pSchedules.Assmt_ID = pAssessments.Assmt_ID LEFT OUTER JOIN #Request.prefix_db_agency#.Invoices ON (pSchedules.Invoice_ID = Invoices.Invoice_ID OR pSchedules.NOA = Invoices.Invoice_ID) AND Invoices.Deleted = 0 JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pSchedules.Patient_ID JOIN #Request.prefix_db_agency#.pPtPayer ON pSchedules.PtPayer_ID = pPtPayer.PtPayer_ID AND pPtPayer.Status = 0 JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_Id = pPtPayer.Payer_ID AND pPayer.Status = 0 AND pPayer.Output = 'Medicare' WHERE pAssessments.status = 0 AND pPatients.Status IN (0,1,4) AND pSchedules.Assmt_ID != 0 AND pSchedules.status= 0 AND pSchedules.Emp_ID != 0 AND pSchedules.Missed = '0' AND CASE WHEN pSchedules.Visit_Date IS NOT NULL THEN MONTH(pSchedules.Visit_Date) = MONTH('#form.monthYear#') ELSE MONTH(pAssessments.F177) = MONTH('#form.monthYear#') END AND CASE WHEN pSchedules.Visit_Date IS NOT NULL THEN YEAR(pSchedules.Visit_Date) = YEAR('#form.monthYear#') ELSE YEAR(pAssessments.F177) = YEAR('#form.monthYear#') END AND MONTH(pSchedules.Visit_Date) = MONTH(now()) GROUP BY First_or_second_Period,pAssessments.Patient_ID ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC SELECT LUPA.*,pAssessments.F297,pAssessments.F298,pAssessments.F299,pAssessments.F300, pAssessments.Admit_ID,pAssessments.F177 AS Cert_Start,pAssessments.F178 AS Cert_End, IFNULL(CASE WHEN First_or_second_Period = "FIRST FINAL" THEN (SELECT LUPA FROM #Request.prefix_db_lookup#.VBP_Visits WHERE VBP_Visits.HIPPS_Code = pAssessments.F300 AND VBP_Visits.Rank = '2') ELSE (SELECT LUPA FROM #Request.prefix_db_lookup#.VBP_Visits WHERE VBP_Visits.HIPPS_Code = pAssessments.F297 AND VBP_Visits.Rank = '2') END,0) AS LUPA_Threshold FROM #Request.prefix_db_agency#.LUPA JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = LUPA.Assmt_ID WHERE pAssessments.status = 0 AND LUPA.Start_Date IS NOT NULL SELECT LAST_DAY(Now()) AS lastday SELECT pSchedules.* FROM #Request.prefix_db_agency#.pSchedules WHERE pSchedules.Assmt_ID = #Assmt_ID# AND pSchedules.status = 0 AND Visit_Date > '#DateFormat(enddate,"yyyy-mm-dd")#' SELECT * FROM #Request.prefix_db_agency#.pAssessments ass WHERE Admit_ID = #Admit_ID# AND Patient_ID = #Patient_ID# AND status = 0 AND left(F26,1) IN ('9','8','7') >
Start_Date -
End_Date -
Num_days - -
dischargemonth -
month -
enddate -
End_of_Searchingmonth -
num_condition -
SELECT (SELECT pAss.F25 FROM #Request.prefix_db_agency#.pAssessments pAss WHERE pAss.Patient_ID = pAssessments.Patient_ID AND pAss.Admit_ID = pAssessments.Admit_ID AND pAss.F26 = ( CASE WHEN (pSchedules.Visit_Type = "1 - SOC - SN" ) THEN '1 - Start of care - further visits planned' WHEN (pSchedules.Visit_Type = "1 - SOC - PT" ) THEN '1 - Start of care - further visits planned - Physical Therapy' WHEN (pSchedules.Visit_Type = "1 - SOC - OT" ) THEN '1 - Start of care - further visits planned - Occupational Therapy' WHEN (pSchedules.Visit_Type = "3 - ROC - SN" || pSchedules.Visit_Type = "3 - ROC - PT") THEN '3 - Resumption of care (after inpatient stay)' WHEN (pSchedules.Visit_Type = "PT Eval" ) THEN 'PT Adult Evaluation' WHEN (pSchedules.Visit_Type = "OT Eval" ) THEN 'OT Adult Evaluation' WHEN (pSchedules.Visit_Type = "ST Eval" ) THEN 'ST Adult Evaluation' WHEN (pSchedules.Visit_Type = "SN Eval" ) THEN 'SN Adult Evaluation' WHEN (pSchedules.Visit_Type = "MSW Eval" ) THEN 'MSW Evaluation' WHEN (pSchedules.Visit_Type = "PT FU Eval" ) THEN 'PT Followup' WHEN (pSchedules.Visit_Type = "OT FU Eval" ) THEN 'OT Followup' WHEN (pSchedules.Visit_Type = "ST FU Eval" ) THEN 'ST Followup' WHEN (pSchedules.Visit_Type = "MSW FU Eval" ) THEN 'MSW Followup' WHEN (pSchedules.Visit_Type = "SN FU Eval" ) THEN 'SN Followup' WHEN (pSchedules.Visit_Type = "Hospice Comprehensive" ) THEN 'Hospice Comprehensive' WHEN (pSchedules.Visit_Type = "SN Hospice" ) THEN 'SN Hospice' WHEN (pSchedules.Visit_Type = "Chaplain Comprehensive Evaluation" ) THEN 'Chaplain Comprehensive Evaluation' WHEN (pSchedules.Visit_Type = "Hospice Discharge" ) THEN 'Hospice Discharge' WHEN (pSchedules.Visit_Type = "4 - Recertification" ) THEN '4 - Recertification (follow-up) reassessment' WHEN (pSchedules.Visit_Type = "4 - Recertification PT" ) THEN '4 - Recertification (follow-up) reassessment - PT' WHEN (pSchedules.Visit_Type = "4 - Recertification OT" ) THEN '4 - Recertification (follow-up) reassessment - OT' WHEN (pSchedules.Visit_Type = "5 - Other follow-up" ) THEN '5 - Other follow-up' WHEN (pSchedules.Visit_Type = "6 - Transfer no discharge" ) THEN '6 - Transferred to an inpatient facility - patient not discharged from agency' WHEN (pSchedules.Visit_Type = "7 - Transfer discharged" ) THEN '7 - Transferred to an inpatient facility - patient discharged from agency' WHEN (pSchedules.Visit_Type = "8 - Death" ) THEN '8 - Death at home' WHEN (pSchedules.Visit_Type = "9 - Discharge from agency" ) THEN '9 - Discharge from agency' ELSE 0 END ) AND pAss.Status = 0 AND pSchedules.Visit_Date = pAss.F176 ORDER BY F25 ASC LIMIT 0,1) AS assessments_completed, pSchedules.* FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID AND pAssessments.Status =0 WHERE pAssessments.Patient_ID = #Patient_ID# AND pAssessments.Admit_ID = '#Admit_ID#' AND pAssessments.Assmt_ID = #Assmt_ID# AND pAssessments.Status = 0 AND pSchedules.Status = 0 AND pSchedules.Visit_Date >= '#Episode_Visit_Start#' AND pSchedules.Visit_Date <= '#Episode_Visit_End#' SELECT * FROM gettotalvisits WHERE Missed = 0 AND Visit_Date >= '#Episode_Visit_Start#' AND Visit_Date <= '#Episode_Visit_End#' AND ( Visit_Date > #_today# OR ( ( (Visit_Type IS NOT NULL AND Visit_Type <> '') OR Initial_visit = 1 ) AND (assessments_completed IS NULL OR assessments_completed <> 1) ) OR ( (Visit_Type IS NULL OR Visit_Type = '') AND (Initial_visit IS NULL OR Initial_visit <> 1) AND Visit_Date <= #_today# AND (pgnotesdraft IS NULL OR pgnotesdraft <> 2) ) ) SELECT * FROM gettotalvisits WHERE Missed = 0 AND (assessments_completed = 1 OR (pgnotesdraft = 2 AND Followup_visit !=1)) SELECT * FROM gettotalvisits WHERE skill IN ('PT','OT','ST','MSW','HHA') AND (assessments_completed = 1 OR (pgnotesdraft = 2 AND Followup_visit !=1)) ---> SELECT (SELECT pAss.F25 FROM #Request.prefix_db_agency#.pAssessments pAss WHERE pAss.Patient_ID = pAssessments.Patient_ID AND pAss.Admit_ID = pAssessments.Admit_ID AND pAss.F26 = ( CASE WHEN (pSchedules.Visit_Type = "1 - SOC - SN" ) THEN '1 - Start of care - further visits planned' WHEN (pSchedules.Visit_Type = "1 - SOC - PT" ) THEN '1 - Start of care - further visits planned - Physical Therapy' WHEN (pSchedules.Visit_Type = "1 - SOC - OT" ) THEN '1 - Start of care - further visits planned - Occupational Therapy' WHEN (pSchedules.Visit_Type = "3 - ROC - SN" || pSchedules.Visit_Type = "3 - ROC - PT") THEN '3 - Resumption of care (after inpatient stay)' WHEN (pSchedules.Visit_Type = "PT Eval" ) THEN 'PT Adult Evaluation' WHEN (pSchedules.Visit_Type = "OT Eval" ) THEN 'OT Adult Evaluation' WHEN (pSchedules.Visit_Type = "ST Eval" ) THEN 'ST Adult Evaluation' WHEN (pSchedules.Visit_Type = "SN Eval" ) THEN 'SN Adult Evaluation' WHEN (pSchedules.Visit_Type = "MSW Eval" ) THEN 'MSW Evaluation' WHEN (pSchedules.Visit_Type = "PT FU Eval" ) THEN 'PT Followup' WHEN (pSchedules.Visit_Type = "OT FU Eval" ) THEN 'OT Followup' WHEN (pSchedules.Visit_Type = "ST FU Eval" ) THEN 'ST Followup' WHEN (pSchedules.Visit_Type = "MSW FU Eval" ) THEN 'MSW Followup' WHEN (pSchedules.Visit_Type = "SN FU Eval" ) THEN 'SN Followup' WHEN (pSchedules.Visit_Type = "Hospice Comprehensive" ) THEN 'Hospice Comprehensive' WHEN (pSchedules.Visit_Type = "SN Hospice" ) THEN 'SN Hospice' WHEN (pSchedules.Visit_Type = "Chaplain Comprehensive Evaluation" ) THEN 'Chaplain Comprehensive Evaluation' WHEN (pSchedules.Visit_Type = "Hospice Discharge" ) THEN 'Hospice Discharge' WHEN (pSchedules.Visit_Type = "4 - Recertification" ) THEN '4 - Recertification (follow-up) reassessment' WHEN (pSchedules.Visit_Type = "4 - Recertification PT" ) THEN '4 - Recertification (follow-up) reassessment - PT' WHEN (pSchedules.Visit_Type = "4 - Recertification OT" ) THEN '4 - Recertification (follow-up) reassessment - OT' WHEN (pSchedules.Visit_Type = "5 - Other follow-up" ) THEN '5 - Other follow-up' WHEN (pSchedules.Visit_Type = "6 - Transfer no discharge" ) THEN '6 - Transferred to an inpatient facility - patient not discharged from agency' WHEN (pSchedules.Visit_Type = "7 - Transfer discharged" ) THEN '7 - Transferred to an inpatient facility - patient discharged from agency' WHEN (pSchedules.Visit_Type = "8 - Death" ) THEN '8 - Death at home' WHEN (pSchedules.Visit_Type = "9 - Discharge from agency" ) THEN '9 - Discharge from agency' ELSE 0 END ) AND pAss.Status = 0 AND pSchedules.Visit_Date = pAss.F176 ORDER BY F25 ASC LIMIT 0,1) AS assessments_completed, pSchedules.* FROM #Request.prefix_db_agency#.pSchedules JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID AND pAssessments.Status =0 WHERE pAssessments.Patient_ID = #Patient_ID# AND pAssessments.Assmt_ID = #Assmt_ID# AND pAssessments.Status = 0 AND pSchedules.Status = 0 AND Visit_Date >= '#Episode_Visit_Start#' AND Visit_Date <= '#Episode_Visit_End#' AND Missed = 0 SELECT * FROM getallcompleted WHERE Missed = 0 AND (assessments_completed = 1 OR (pgnotesdraft = 2 AND Followup_visit !=1)) SELECT * FROM gettotalvisits WHERE skill IN ( '#skillitem#') AND Missed = 0 AND (assessments_completed = 1 OR (pgnotesdraft = 2 AND Followup_visit !=1)) > --->
Episode Amount -
Daily Rate -
CM Gross Revenue -
CM Sequestration -
Daily Adjustment -
Adjusted Daily Rate -
Num_days -
CM Net Revenue -
minusamount -
Discharge CM_Net -
dischargemonth -
episodemonth -
completed_visits -
LUPA_Threshold -
AND (episodemonth EQ month OR month EQ dischargemonth)) OR url.report_type EQ 'LUPA'> SELECT * FROM gettotalvisits WHERE Missed = 0 AND skill IN ( '#skillitem#') AND (assessments_completed = 1 OR (pgnotesdraft = 2 AND Followup_visit !=1))
Total_A -
Total_B -
CM_Net -
SELECT * FROM gettotalvisits WHERE Missed = 0 AND skill IN ( '#skillitem#') AND (assessments_completed = 1 OR (pgnotesdraft = 2 AND Followup_visit !=1)) UPDATE #Request.prefix_db_agency#.LUPA SET Days_in_Month = '#Request.Days_in_Month#', Num_days = '#Request.Num_days#', HHRG = '#Request.HHRG#', Payment = '#Request.Payment#', Daily_Rate = '#Request.Daily_Rate#', CM_Gross = '#Request.CM_Gross#', Total_Visits = '#Request.Total_Visits#', Therapy_Visits= '#Request.Therapy_Visits#', CM_Sequester = '#Request.CM_Sequester#', Total_CM = '#Request.Total_CM#', Daily_Adjust = '#Request.Daily_Adjust#', CM_Net = '#Request.CM_Net#', LUPA = '#LUPA_Threshold#', CM_LUPA = '#Request.CM_LUPA#', Total_Periods = '#Request.Total_Periods#', Start_Date = '#Request.Start_Date#', Discharge_Date = '#DateFormat(getdischarge.F176,"yyyy-mm-dd")#', completed_visits = '#Request.completed_visits#', SN_Visits = '#Request.SN_Visits#', PT_Visits = '#Request.PT_Visits#', OT_Visits = '#Request.OT_Visits#', ST_Visits = '#Request.ST_Visits#', HHA_Visits = '#Request.HHA_Visits#', MSW_Visits = '#Request.MSW_Visits#' , Estimated_LUPA = '#Request.Estimated_LUPA#', Scheduled_Visits = WHERE DRC_ID = '#DRC_ID#'