The new patient part boils down to :
Instead it should be:SET @StartDate=curdate() - INTERVAL 1 MONTH, @EndDate=curdate();
SET @ignoreCodes='D9986,D9987';
/*New patients*/
SELECT 'NP' AS 'CountType',pls.ProcDate,pls.PatNum, pls.ProvNum
FROM procedurelog pls
INNER JOIN procedurecode pc ON pc.CodeNum=pls.CodeNum
AND pc.ProcCode NOT IN ('D9986','D9987')
WHERE pls.ProcStatus=2 #Complete
GROUP BY pls.PatNum
HAVING pls.ProcDate=MIN(pls.ProcDate)
AND pls.ProcDate BETWEEN @StartDate AND @EndDate
I don't entirely understand why there's a difference in results. I just found it when comparing against query 1645 New and Lost patient report.SET @StartDate=curdate() - INTERVAL 1 MONTH, @EndDate=curdate();
SET @ignoreCodes='D9986,D9987';
/*New patients*/
SELECT 'NP' AS 'CountType',pl.ProcDate,pl.PatNum, pl.ProvNum
FROM procedurelog pl
INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
AND pc.ProcCode NOT IN (@ignoreCodes)
WHERE pl.ProcStatus=2 #Complete
GROUP BY pl.PatNum
HAVING MIN(pl.ProcDate) BETWEEN @StartDate AND @EndDate