SELECT pAuths.*,pPayer.Auth_Req,pPayer.Output FROM #Request.prefix_db_agency#.pAuths
JOIN #Request.prefix_db_agency#.pPtPayer ON pAuths.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
WHERE pAuths.STATUS !=
AND pAuths.AutoAuth_ID =
SELECT pAuths.*,pRevenue_Code.Visit_Type,pRevenue_Code.Rev_Code,pRevenue_Code.HCPCS,pPtPayer.Sub_First,pPayer.Pay_Name,pAssessments.F26,pAssessments.F177,pAssessments.F178,pAssessments.Admit_ID,pPayer.Pay_ID,pRevenue_Code.Description,pPayer.Output,pPayer.auth_measure,pPayer.bypass_compliance,pAdmit.Admit_Type,
CONCAT(pEmployee.Emp_First , ", ", pEmployee.Emp_Last) AS name_by
FROM #Request.prefix_db_agency#.pAuths
JOIN #Request.prefix_db_agency#.pRevenue_Code ON pRevenue_Code.Rev_CodeID = pAuths.Rev_ID AND pRevenue_Code.Status = 0
JOIN #Request.prefix_db_agency#.pPtPayer ON pPtPayer.PtPayer_ID = pAuths.PtPayer_ID AND pPtPayer.Status < 2
JOIN #Request.prefix_db_agency#.pPayer ON pPayer.Pay_ID = pPtPayer.Payer_ID AND pPayer.Status = 0
LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pAuths.Assmt_ID AND pAssessments.Status = 0
LEFT OUTER JOIN #Request.prefix_db_agency#.pAdmit ON pAdmit.Admit_ID = pAssessments.Admit_ID
LEFT OUTER JOIN #Request.prefix_db_lookup#.pEmployee ON pAuths.Created_by = pEmployee.Emp_ID
WHERE pAuths.STATUS <
AND pAuths.Patient_ID =
AND pAuths.Assmt_ID =
AND pAdmit.Admit_ID =
AND pAssessments.Admit_ID IN ( SELECT pAdmit.Admit_ID FROM #Request.prefix_db_agency#.pAdmit
WHERE pAdmit.Status < 2 AND Patient_ID = )
GROUP BY pAuths.AutoAuth_ID
SELECT * FROM #Request.prefix_db_agency#.pAuths
WHERE STATUS !=
AND Auth_ID =
SELECT * FROM #Request.prefix_db_lookup#.pVisit_Frequency
WHERE Visit_No =
ORDER BY ID ASC
SELECT IFNULL(count(*),0) AS totalcount FROM #Request.prefix_db_agency#.pSchedules
WHERE Patient_ID =
AND Assmt_ID =
AND Auth_ID =
AND Status =
AND (Visit_Type is NULL OR (Visit_Type NOT LIKE '6%' AND Visit_Type NOT LIKE '7%'))
AND IFNULL(Missed, 0) = 0
AND IFNULL(Nonbillable, 0) = 0
AND IFNULL(prn_visit, 0) != 1
AND Schedule_ID <>
SELECT IFNULL(count(*),0) AS totalcount FROM #Request.prefix_db_agency#.pSchedules
WHERE Patient_ID =
AND Assmt_ID =
AND Auth_ID =
AND Status =
AND (Visit_Type is NULL OR (Visit_Type NOT LIKE '6%' AND Visit_Type NOT LIKE '7%'))
AND IFNULL(Missed, 0) = 0
AND IFNULL(Nonbillable, 0) = 0
AND IFNULL(prn_visit, 0) = 1
AND Schedule_ID <>
SELECT pAuths.*, pPayer.bypass_compliance, pPayer.auth_measure, pAdmit.Admit_Type
FROM #Request.prefix_db_agency#.pAuths
JOIN #Request.prefix_db_agency#.pPtPayer ON pAuths.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
LEFT JOIN #Request.prefix_db_agency#.pAssessments ON pAuths.Assmt_ID = pAssessments.Assmt_ID AND pAssessments.Status = 0
LEFT JOIN #Request.prefix_db_agency#.pAdmit ON pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.Status = 0
WHERE pAuths.Patient_ID =
AND pAuths.Assmt_ID =
AND pAuths.Auth_ID =
AND pAuths.Status =
ORDER BY pAssessments.AutoAssmt_ID DESC
LIMIT 1
SELECT Auths_weeks_ID, Week_No, Week_Start, Week_End, Total_visits
FROM #Request.prefix_db_agency#.pAuths_weeks
WHERE Auth_ID =
AND Status =
AND DATE(Week_Start) <=
AND DATE(Week_End) >=
ORDER BY Auths_weeks_ID
LIMIT 1
SELECT Auths_weeks_ID, Week_No, Week_Start, Week_End, Total_visits
FROM #Request.prefix_db_agency#.pAuths_weeks
WHERE Auth_ID =
AND Status =
AND Week_No =
ORDER BY Auths_weeks_ID
LIMIT 1
SELECT IFNULL(COUNT(DISTINCT Schedule_ID),0) AS totalcount
FROM #Request.prefix_db_agency#.pSchedules
WHERE Patient_ID =
AND Status =
AND IFNULL(Missed, 0) = 0
AND DATE(Visit_Date) >=
AND DATE(Visit_Date) <=
AND Schedule_ID <>
AND (
Auth_ID =
OR (
Assmt_ID =
AND IFNULL(Auth_ID, 0) = 0
AND (Skill =
OR Skill LIKE )
)
)
SELECT pPayer.M0150, pPtPayer.Util_Force
FROM #Request.prefix_db_agency#.pPtPayer pPtPayer
JOIN #Request.prefix_db_agency#.pPayer pPayer ON pPayer.Pay_ID = pPtPayer.Payer_ID AND pPayer.Status = 0
WHERE pPtPayer.PtPayer_ID =
AND pPtPayer.Status = 0
LIMIT 1
SELECT F300, F297
FROM #Request.prefix_db_agency#.pAssessments
WHERE Assmt_ID =
AND Status = 0
LIMIT 1
SELECT IFNULL(Total_skilled, 0) AS Total_skilled
FROM #Request.prefix_db_lookup#.CW_Visit_Utilization
WHERE HIPPS_Code =
LIMIT 1
SELECT IFNULL(Total_skilled, 0) AS Total_skilled
FROM #Request.prefix_db_lookup#.CW_Visit_Utilization
WHERE HIPPS_Code =
LIMIT 1
SELECT IFNULL(SUM(Total_Visits), 0) AS total_visits
FROM #Request.prefix_db_agency#.pAuths
WHERE Assmt_ID =
AND Status = 0
SELECT IFNULL(Total_Visits, 0) AS Total_Visits
FROM #Request.prefix_db_agency#.pAuths
WHERE Auth_ID =
AND Status = 0
LIMIT 1
SELECT * FROM #Request.prefix_db_agency#.pAuths
WHERE STATUS =
AND Patient_ID =
AND Assmt_ID =
AND Auth_No =
AND PtPayer_ID =
SELECT * FROM #Request.prefix_db_agency#.pAuths
WHERE Auth_ID =
AND Status =
SELECT * FROM #Request.prefix_db_lookup#.pEmployee
WHERE Emp_ID =
AND Status =
AND (Skill_1 IN ('HHA','CNA') OR Skill_2 IN ('HHA','CNA') OR Skill_3 IN ('HHA','CNA'))
AND (Skill_1 IN ('PTA') OR Skill_2 IN ('PTA') OR Skill_3 IN ('PTA'))
AND (Skill_1 IN ('OTA') OR Skill_2 IN ('OTA') OR Skill_3 IN ('OTA'))
SELECT * FROM #Request.prefix_db_agency#.pAssessments
WHERE Assmt_ID =
AND Status =
SELECT * FROM #Request.prefix_db_agency#.pSchedules
WHERE Assmt_ID =
AND Status =
AND Visit_Date BETWEEN '#Request.Date_Start#' AND '#Request.Date_End#'
AND Supervision = 1
AND Skill =
AND Skill =
AND Skill =
SELECT pAuths.* FROM #Request.prefix_db_agency#.pAuths
JOIN #Request.prefix_db_agency#.pAssessments ON pAssessments.Assmt_ID = pAuths.Assmt_ID AND pAssessments.Status = 0
WHERE pAuths.Status =
AND pAuths.Patient_ID =
AND pAuths.Skill IN ( SELECT Visit_Type FROM #Request.prefix_db_agency#.pRevenue_Code
WHERE Rev_CodeID = )
AND pAssessments.Admit_ID IN ( SELECT pAdmit.Admit_ID FROM #Request.prefix_db_agency#.pAdmit
WHERE pAdmit.Status < 2 AND Patient_ID = )
GROUP BY pAuths.Auth_ID
SELECT *
FROM #Request.prefix_db_agency#.pSchedules s
WHERE s.Auth_ID =
AND s.Patient_ID =
AND s.Status =
SELECT * FROM Getvisitcount
WHERE prn_visit = 1
AND (Visit_Type is NULL OR (Visit_Type NOT LIKE '6%' AND Visit_Type NOT LIKE '7%'))
SELECT * FROM Getvisitcount
WHERE Missed > 0
SELECT * FROM Getvisitcount
WHERE Missed = 0
and Visit_Date < #Now()#
SELECT pAssessments.F177,pAssessments.F178
FROM #Request.prefix_db_agency#.pAssessments
WHERE pAssessments.Status= 0
AND pAssessments.Assmt_ID =
// Define start and end dates for the range
startDate = strating_date; // Example start date: August 1, 2024
endDate = ending_date; // Example end date: August 31, 2024
FirstWeekStart = createDateTime(year(startDate), month(startDate), day(startDate), 0, 0, 0);
// Initialize variables
currentWeekStart = createDateTime(year(startDate), month(startDate), day(startDate), 0, 0, 0);
currentWeekStart = dateAdd("d", -DayOfWeek(currentWeekStart) + 1, currentWeekStart); // Start of the week (Sunday)
weeks = []; // Array to hold week data
week_no = 1;
// Loop through each week in the date range
while (currentWeekStart LTE endDate)
{
// Calculate end of the week
currentWeekEnd = dateAdd("d", 6, currentWeekStart); // End of the week (Saturday)
if (currentWeekEnd GT endDate) {
currentWeekEnd = endDate; // Adjust end date if it exceeds the range
}
// Collect dates for the current week
weekDates = [];
dateIter = currentWeekStart;
while (dateIter LTE currentWeekEnd) {
arrayAppend(weekDates, dateFormat(dateIter, "yyyy-mm-dd"));
dateIter = dateAdd("d", 1, dateIter);
}
// Store week information in the weeks array
if (week_no == 1)
{
arrayAppend(weeks, {start = FirstWeekStart, end = currentWeekEnd, dates = weekDates});
}
else
{
arrayAppend(weeks, {start = currentWeekStart, end = currentWeekEnd, dates = weekDates});
}
//arrayAppend(weeks, {start = currentWeekStart, end = currentWeekEnd, dates = weekDates});
week_no = week_no + 1;
// Move to the next week
currentWeekStart = dateAdd("d", 7, currentWeekStart);
}
SELECT * FROM #Request.prefix_db_agency#.pAuths_weeks
WHERE Auth_ID = AND status = 0
SELECT * FROM getauthallweeks
WHERE Week_No = '#i#'
SELECT pAuths.Date_Start, pAuths.Date_End
FROM #Request.prefix_db_agency#.pAuths
WHERE pAuths.Patient_ID =
AND pAuths.Status = 0
AND pAuths.Auth_ID= '#arguments.Auth_ID#'
AND pAuths.Assmt_ID =
SELECT pAuths.*, pPayer.auth_measure, pPayer.bypass_compliance, pAdmit.Admit_Type
FROM #Request.prefix_db_agency#.pAuths
JOIN #Request.prefix_db_agency#.pPtPayer ON pAuths.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
LEFT JOIN #Request.prefix_db_agency#.pAssessments ON pAuths.Assmt_ID = pAssessments.Assmt_ID AND pAssessments.Status = 0
LEFT JOIN #Request.prefix_db_agency#.pAdmit ON pAssessments.Admit_ID = pAdmit.Admit_ID AND pAdmit.Status = 0
WHERE pAuths.Auth_ID =
AND pAuths.Status =
SELECT StartTime, EndTime, Visit_Date
FROM #Request.prefix_db_agency#.pSchedules
WHERE Auth_ID =
AND Status =
AND Missed = 0
AND Schedule_ID <>
SELECT IFNULL(Include_Missed_Visits, 0) AS Include_Missed_Visits,
Date_Start, Date_End
FROM #Request.prefix_db_agency#.pAuths
WHERE Auth_ID =
AND Status =
LIMIT 1
SELECT Date_Start, Date_End
FROM #Request.prefix_db_agency#.pAuths
WHERE Auth_ID =
AND Status =
LIMIT 1
SELECT IFNULL(Total_Units, 0) AS schedule_units, StartTime, EndTime
FROM #Request.prefix_db_agency#.pSchedules
WHERE Auth_ID =
AND status =
AND Patient_ID =
AND Assmt_ID =
AND (Visit_Type IS NULL OR (Visit_Type NOT LIKE '6%' AND Visit_Type NOT LIKE '7%'))
AND IFNULL(Missed, 0) = 0
AND IFNULL(Nonbillable, 0) = 0
AND DATE(Visit_Date) >=
AND DATE(Visit_Date) <=
AND IFNULL(prn_visit, 0) = 1
AND IFNULL(prn_visit, 0) != 1
SELECT Total_Visits, IFNULL(prn_visits, 0) AS prn_visits
FROM #Request.prefix_db_agency#.pAuths
WHERE Auth_ID =
AND Status = 0
SELECT IFNULL(prn_visits, 0) AS prn_visits
FROM #Request.prefix_db_agency#.pAuths
WHERE Auth_ID =
AND Status = 0
SELECT Week_No, Total_visits, Week_Start, Week_End
FROM #Request.prefix_db_agency#.pAuths_weeks
WHERE Auth_ID =
AND Status =
AND Total_visits <> 0
ORDER BY Week_No ASC