Hi,
Based on claims closed in a week, I'm looking to find patients with balance. I'm able to achieve half of my goal using query#246 but here are 2 issues I'm facing - and looking for help to resolve it.
1. Patients who have outstanding claims or claims that haven't been sent yet get included.
2. Patients, who are the guarantor are getting listed instead of the patient itself.
What's missing?
/*246 Claims of status 'Sent' or 'Received' by Date of Service with PatNum, Amount billed insurance, Amount billed patient and Date sent
Edit Dates to change date of service range*/
SET @FromDate= DATE(ADDDATE(CURDATE(), INTERVAL -7 DAY));
SET @ClaimStatus = 'R'; -- Enter 'R' for received, 'S' for sent, or leave blank to run for all.
/*------------ DO NOT MODIFY BELOW THIS LINE ------------*/
/*Query code written/modified on: 5/6/2009, 02/07/2019, 02/27/2020:MattG*/
SELECT
CONCAT(p.FName,' ',p.LName) as 'Patient/Guarantor Name',
p.EstBalance As 'Balance $',DATE_FORMAT(MAX(DATE(cl.DateReceived)),'%m/%d/%Y') as 'Last Claim Processed',DATE_FORMAT(MAX(DATE(cl.DateSent)),'%m/%d/%Y') as 'Last Claim Sent',DATE_FORMAT(MAX(DATE(cl.DateSentOrig)),'%m/%d/%Y') as 'Date Original Sent',(SELECT DATE_FORMAT(MAX(DATE(DateSent)),'%m/%d/%Y') FROM statement WHERE statement.PatNum=p.PatNum) AS
'Last Stmnt Sent'
FROM claim cl
INNER JOIN patient p
ON p.PatNum=cl.PatNum
WHERE cl.ClaimType<>'PreAuth'
AND cl.DateReceived>=@FromDate
AND p.EstBalance>0
GROUP BY cl.ClaimNum
ORDER BY cl.ClaimNum;