SELECT
MAX(s.Payment_ID) as Payment_ID,s.PaymentDate,s.Record_Created_By,
(select sum(s.Amount) from #Request.prefix_db_agency#.Payments s
where s.invoice_id = p.invoice_id and s.Deleted <> 1
AND s.PaymentDate >= #CreateODBCDate(url.df)#
AND s.PaymentDate <= #CreateODBCDate(url.dt)#
) as paid,
(s.WriteOffAmount) as WriteOffAmount,
s.PatientPayer_ID ,
(select sum(writeoffamount) from #Request.prefix_db_agency#.Payments
where invoice_id = p.invoice_id and Deleted <> 1
AND s.PaymentDate >= #CreateODBCDate(url.df)#
AND s.PaymentDate <= #CreateODBCDate(url.dt)#
) as wOff,
p.PayerBalance,
(select s.PaymentDate from #Request.prefix_db_agency#.Payments s
WHERE s.invoice_id = p.invoice_id
AND s.PaymentDate >= #CreateODBCDate(url.df)#
AND s.PaymentDate <= #CreateODBCDate(url.dt)#
and s.deleted <> 1 ORDER BY s.PaymentDate DESC LIMIT 0,1) as paid_date,
pa.Patient_ID, pa.Pt_Last,pa.Pt_Agy_ID,
a.F177 AS STARTS, a.F178 AS ENDS , a.F26 AS TYPE, pa.Pt_First, pa.Pt_Agy_ID, pa.Status,
CASE
WHEN DATEDIFF(p.DateSent,a.F177) < 30 THEN a.F177
WHEN DATEDIFF(p.DateSent,a.F177) >= 30 THEN DATE_ADD(a.F177, INTERVAL 30 DAY)
ELSE a.F177
END as START,
CASE
WHEN DATEDIFF(p.DateSent,a.F177) < 30 THEN DATE_ADD(a.F177, INTERVAL 29 DAY)
WHEN DATEDIFF(p.DateSent,a.F177) >= 30 THEN DATE_ADD(a.F177, INTERVAL 59 DAY)
ELSE a.F178
END as END,
DATE_FORMAT(pa.Pt_DOB,'%m/%d/%Y') AS DateOfBirth,
(SELECT Date_Deposit FROM #Request.prefix_db_agency#.pDeposit
WHERE pDeposit.Internal_control_number = p.Internal_control_number
AND pDeposit.Status = 0 ORDER BY Date_Deposit DESC LIMIT 0,1) AS Date_Deposit,
(SELECT Min(Visit_Date) FROM #Request.prefix_db_agency#.pSchedules
where Assmt_ID = p.Assmt_ID AND p.Invoice_ID = pSchedules.Invoice_ID and status = 0 ORDER BY Visit_Date) as MIN,
(SELECT Max(Visit_Date) FROM #Request.prefix_db_agency#.pSchedules
where Assmt_ID = p.Assmt_ID AND p.Invoice_ID = pSchedules.Invoice_ID and status = 0 ORDER BY Visit_Date) as MAX,
p.Invoice_Id, p.Comment, p.Output as POUT,
DATE_FORMAT(p.DateSent,'%Y-%m-%d') AS DateSent,
DATEDIFF(CURDATE(), p.DateSent) AS age,
MAX(DATE_FORMAT(s.PaymentDate,'%Y-%m-%d')) AS PaymentDate,
DATE_FORMAT(p.DateSent,'%m/%d') AS ds,
(p.InvoiceTotal) as InvoiceTotal,p.PayerBalance,
pr.Pay_ID, pr.Pay_Name, pr.Payer_No,pr.Output, p.Output AS PayerOutputType,
(SELECT Invoice_Id FROM #Request.prefix_db_agency#.Invoices WHERE Assmt_ID = p.Assmt_ID
AND InvoiceTotal = '0.00' and Deleted <> 1 LIMIT 1) AS FinalBilling
from
#Request.prefix_db_agency#.Payments s
JOIN #Request.prefix_db_agency#.Invoices p on p.Invoice_ID = s.Invoice_ID
JOIN #Request.prefix_db_agency#.pPatients pa on pa.patient_id = p.patient_id
JOIN #Request.prefix_db_agency#.pPayer pr on pr.Pay_ID = s.patientpayer_id
LEFT OUTER JOIN #Request.prefix_db_agency#.pAssessments a on p.Assmt_ID = a.Assmt_ID
WHERE p.Agency_Id = '#session.agencyid#'
AND pa.Status IN (#Request.patient_status#) AND a.status = 0
AND pr.Pay_ID = '#url.payer_filter#'
AND p.Output like '#url.Output#'
AND s.PaymentDate >= #CreateODBCDate(url.df)#
AND s.PaymentDate <= #CreateODBCDate(url.dt)#
AND pa.Patient_ID = '#url.patient_filter#'
AND Amount > 0
AND s.Deleted <> 1 AND p.Deleted <> 1
AND p.InvoiceTotal > 0
AND s.Amount > 0
AND p.Output is not null
Group BY p.Invoice_Id
ORDER BY Pt_Last, Pt_First ASC
SELECT pSchedules.Schedule_ID, pSchedules.Total_Units,pSchedules.Invoice_ID,pSchedules.Visit_Date,pSchedules.Skill,
pSchedules.Total_Eclaim,
pRevenue_Code.Measure, pRevenue_Code.EClaim_Price ,
vp.Visit_Pay_ID, vp.Schedule_ID as vp_Schedule_ID ,vp.Payment_ID, IFNULL(vp.payment,0) AS payment,
IFNULL(vp.write_offamount,0) AS write_offamount,IFNULL(vp.second_write_offamount,0) AS second_write_offamount,
IFNULL(vp.third_write_offamount,0) AS third_write_offamount, IFNULL(vp.fourth_write_offamount,0) AS fourth_write_offamount,
vp.write_offcode, vp.write_offcodesecond, vp.second_write_offcode,vp.second_write_offcodesecond,
vp.third_write_offcode,
vp.third_write_offcodesecond, vp.fourth_write_offcode, vp.fourth_write_offcodesecond
FROM #Request.prefix_db_agency#.pSchedules
JOIN #Request.prefix_db_agency#.Invoices ON Invoices.Invoice_Id = pSchedules.Invoice_ID
JOIN #Request.prefix_db_agency#.pRevenue_Code ON pRevenue_Code.Rev_CodeID = pSchedules.Rev_ID
LEFT OUTER JOIN #Request.prefix_db_agency#.Visits_payments vp ON vp.Schedule_ID = pSchedules.Schedule_ID
AND vp.Status = 0
WHERE pSchedules.Status = 0 AND Invoices.Deleted <> 1
AND vp.Payment_ID IN ( SELECT Payment_ID FROM #Request.prefix_db_agency#.Payments
WHERE Invoice_ID IN (#ValueList(GetI.Invoice_ID)#)
AND Deleted <> 1
AND PaymentDate >= #CreateODBCDate(url.df)#
AND PaymentDate <= #CreateODBCDate(url.dt)#
)
GROUP BY vp.Visit_Pay_ID
SELECT * FROM Getallschedules
WHERE Invoice_ID = '#GetI.Invoice_ID#'
Click Here and choose "Save Target As..." to download the Exported data file and save it to your computer
No data could be found based on your search criteria