UPDATE IGNORE #Request.prefix_db_lookup#.Coding_Request
SET Assigned_to = '#url.assignee#',
Updated_by = '#Session.MM_USID#'
where ID = '#url.coding_id#'
SELECT *
FROM #Request.prefix_db_lookup#.Coding_Request
JOIN #Request.prefix_db_lookup#.Agency on Agency.Agency_ID = Coding_Request.Agency_ID
Where Coding_Request.Deleted <> 1
and Coding_Request.Coding_status != 'Complete'
AND Coding_Request.Agency_ID = '#URL.AID#'
AND Agency.Status = 'active'
AND (message_indicate NOT LIKE '%Identified%' OR message_indicate is null)
-- AND YEAR(Coding_Request.Request_Date) = YEAR(CURRENT_DATE)
-- AND (MONTH(Coding_Request.Request_Date) >= MONTH(CURRENT_DATE - INTERVAL 1 MONTH))
AND Coding_Request.Request_Date >= now() - INTERVAL 2 month
ORDER BY Coding_Request.Created_Date DESC
coding_track = CreateObject("Component", "coding");
tab1 = coding_track.GetpowerpathRequests(1,#URL.AID#);
hpreview = coding_track.GetpowerpathRequests('H_P_Review',#URL.AID#);
eligibility = coding_track.GetpowerpathRequests('eligibility',#URL.AID#);
eligibilitydelay = coding_track.GetpowerpathRequests('eligibilitydelay',#URL.AID#);
authorization = coding_track.GetpowerpathRequests('authorization',#URL.AID#);
authdelay = coding_track.GetpowerpathRequests('authdelay',#URL.AID#);
hpdelay = coding_track.GetpowerpathRequests('hpdelay',#URL.AID#);
codingdelay = coding_track.GetpowerpathRequests('codingdelay',#URL.AID#);
intake_dataentry = coding_track.GetpowerpathRequests('intake_dataentry',#URL.AID#);
intakedelay = coding_track.GetpowerpathRequests('intakedelay',#URL.AID#);
intakedelay_transition = coding_track.GetpowerpathRequests('intakedelay_transition',#URL.AID#);
SELECT count(*) as reccount,MHCB_Entered,IFNULL(SUM(TIME_TO_SEC(TIMEDIFF(now(),MHCB_Entered))),0) as avg_elapse1,Request_Date,Request_Time,
requested_logged_elapsed ,MHCB_Entered,
IFNULL(SUM(ABS(TIME_TO_SEC(TIMEDIFF(MHCB_Entered,(TIMESTAMP(Request_Date,Request_Time)))))),0) as avg_elapse2 FROM #Request.prefix_db_lookup#.Coding_Request
WHERE Coding_Request.Deleted <> 1
AND Coding_Request.Coding_status != 'Complete' AND Coding_status like 'coding'
AND Coding_Request.Request_Date >= now() - INTERVAL 3 month
ORDER BY ID DESC
HHA System Administration
select * from #Request.prefix_db_lookup#.Agency where status='active' ORDER BY agency_id
SELECT pSchedules.*,DATEDIFF(NOW(), pSchedules.Visit_Date) AS diffdays,
pPatients.Pt_First,pPatients.Pt_Last,pPayer.Pay_Name,Agency.Agency_Name,Agency.Agency_Login
FROM #Request.prefix_db_agency#.pSchedules
JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pSchedules.Assmt_ID AND pAssessments.Status = 0
JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAssessments.Patient_ID AND pPatients.Status < 2
JOIN #Request.prefix_db_agency#.pPtPayer ON pPtPayer.PtPayer_ID = pSchedules.PtPayer_ID AND pPtPayer.Status = 0
JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_ID = pPtPayer.Payer_ID AND pPayer.Status = 0
JOIN #Request.prefix_db_lookup#.Agency ON pPatients.Loc_ID = Agency.Agency_ID
WHERE pSchedules.Status = 0
AND pSchedules.Followup_visit = 1
AND pSchedules.Visit_Date < now()
AND pSchedules.Visit_Type like '1 - SOC%'
AND pAssessments.Admit_ID IN ( SELECT pAdmit.Admit_ID FROM #Request.prefix_db_agency#.pAdmit WHERE pAdmit.Status = 0)
AND pSchedules.Invoice_ID =0 and pSchedules.NOA = 0
AND pPayer.NOA_req = 1
AND Agency.svc_dataset = 1
GROUP BY pSchedules.Schedule_ID
ORDER BY pSchedules.record_timestamp DESC
SELECT pAssessments.*,DATEDIFF(NOW(), pAssessments.F176) AS diffdays,
pPatients.Pt_First,pPatients.Pt_Last,
pPayer.Pay_Name,Agency.Agency_Name,Agency.Agency_Login
FROM #Request.prefix_db_agency#.pAssessments
JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAssessments.Patient_ID AND pPatients.Status < 2
JOIN #Request.prefix_db_agency#.pPtPayer ON pPtPayer.Patient_ID = pPatients.Patient_ID AND pPtPayer.Status = 0
JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_ID = pPtPayer.Payer_ID AND pPayer.Status = 0
JOIN #Request.prefix_db_lookup#.Agency ON pPatients.Loc_ID = Agency.Agency_ID
WHERE pAssessments.Status = 0
AND LEFT(pAssessments.F26,1) IN (1,2,3,4,5,6,7,8,9)
AND pAssessments.F197 IS NULL
AND pAssessments.F25 = 1
AND Left(pPayer.M0150,1) IN ('1','2','3','4')
AND Agency.svc_dataset = 1
ORDER BY pAssessments.Record_Creation_Date DESC
SELECT pAssessments.*,DATEDIFF(NOW(), pAssessments.F178) AS diffdays,
DATEDIFF(NOW(),DATE_ADD(pAssessments.F177, INTERVAL 30 DAY)) AS bill_end,
DATE_ADD(pAssessments.F177, INTERVAL 30 DAY) AS bill_period_enddate,
pPatients.Pt_First,pPatients.Pt_Last,
pPayer.Pay_Name,Agency.Agency_Name,Agency.Agency_Login,Invoices.Invoice_ID,
(SELECT COUNT(pSchedules.Invoice_ID) FROM #Request.prefix_db_agency#.pSchedules
WHERE pSchedules.Status = 0
AND pSchedules.Assmt_ID = pAssessments.Assmt_ID
AND pSchedules.Invoice_ID IS NOT NULL
AND pSchedules.Invoice_ID !=0 ) AS invoice_ids
FROM #Request.prefix_db_agency#.pAssessments
JOIN #Request.prefix_db_agency#.pAdmit ON pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.Status = 0
JOIN #Request.prefix_db_agency#.pPatients ON pPatients.Patient_ID = pAssessments.Patient_ID AND pPatients.Status < 2
JOIN #Request.prefix_db_agency#.pPtPayer ON pPtPayer.Patient_ID = pPatients.Patient_ID AND pPtPayer.Status = 0
JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_ID = pPtPayer.Payer_ID AND pPayer.Status = 0
JOIN #Request.prefix_db_lookup#.Agency ON pPatients.Loc_ID = Agency.Agency_ID
LEFT OUTER JOIN #Request.prefix_db_agency#.Invoices ON Invoices.Assmt_ID = pAssessments.Assmt_ID AND Invoices.Deleted = 0
WHERE pAssessments.Status = 0
AND LEFT(pAssessments.F26,1) IN (1,4)
AND (DATE_ADD(pAssessments.F177, INTERVAL 30 DAY) <= now())
AND pAssessments.F25 = 1
ORDER BY pAssessments.Record_Creation_Date DESC
SELECT Eligibility.*,
pPayer.Pay_Name,pPtPayer.Record_Creation_Date,
pPatients.Patient_ID, pPatients.Pt_Last AS PatientLast, pPatients.Pt_First AS PatientFirst
FROM #Request.prefix_db_agency#.Eligibility
JOIN #Request.prefix_db_agency#.pPtPayer ON Eligibility.PtPayer_ID = pPtPayer.PtPayer_ID AND pPtPayer.Status = 0
JOIN #Request.prefix_db_agency#.pPayer ON pPtPayer.Payer_ID = pPayer.Pay_ID AND pPayer.Status = 0 AND pPayer.Eligibility = 1
JOIN #Request.prefix_db_agency#.pPatients ON Eligibility.Patient_ID = pPatients.Patient_ID AND pPatients.Status = 0
JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Patient_ID = Eligibility.Patient_ID AND pAssessments.Status = 0
JOIN hhapowerpath.Agency ON pPatients.Loc_ID = Agency.Agency_ID AND Agency.svc_eligibility = 1
WHERE Eligibility.status = 0
AND pAssessments.Admit_ID NOT IN (SELECT Admit_ID FROM #Request.prefix_db_agency#.pAssessments
WHERE (F26 like '9%' OR F26 like '7%' OR F26 like '8%') AND F176 < now()
AND pAssessments.Status =0)
AND Expiration_Date IS NOT NULL
AND (Expiration_Date < now()
OR Expiration_Date < DATE_ADD(now(), INTERVAL 10 DAY) )
AND pPtPayer.PtPayer_ID NOT IN (SELECT PtPayer_ID FROM #Request.prefix_db_agency#.Eligibility WHERE Expiration_Date > DATE_ADD(now(), INTERVAL 10 DAY))
GROUP BY Eligibility.PtPayer_ID
ORDER BY pPatients.Pt_Last ASC, pPatients.Pt_First ASC