Open Dental Forum

I'm looking to modify OD query# 1036 so that it reports the #s for last month and for the same month from last year (ie., if last month is Oct '22 then report same data for Oct '21). As a first step I' tried the below for 'Gross Production' but I'm getting an error around 'SELECT z1.*'. z1 is just a placeholder to make it easy to debug. What am I missing?

/*1036 Special monthly breakdown report. Shows the count of new patients, distinct patient visits, net production, income, and number of broken appointments per month for date range*/
/*Net prod writeoffs based on ins payment date. New patients based on having first completed/scheduled appointment in date range.*/
/*Query code written/modified: 02/20/2015*/

SET @StartDate='2022-10-01';
SET @EndDate='2022-10-31';
SET @LastStartDate='2021-10-07',@LastEndDate='2021-10-31';
SELECT 'NewPatCount' AS 'Title','' AS 'Value'

UNION ALL

SELECT '-----------','----------------'

UNION ALL

SELECT z.*
FROM(
SELECT DATE_FORMAT(DATE(firstApt.FirstVisit),'%M %Y') AS 'MONTH',
COUNT(p.PatNum) AS 'Value'
FROM patient p
INNER JOIN (
SELECT Q.PatNum,Q.FirstVisit AS FirstVisit
FROM(
SELECT apt.PatNum,MIN(apt.AptDateTime) AS FirstVisit
FROM appointment apt
WHERE apt.AptStatus IN(1,2,4)
GROUP BY apt.PatNum
)Q
WHERE DATE(Q.FirstVisit) BETWEEN @StartDate AND @EndDate
) firstApt ON firstApt.PatNum=p.PatNum
GROUP BY DATE_FORMAT(DATE(firstApt.FirstVisit),'%M %Y')
ORDER BY YEAR(DATE(firstApt.FirstVisit)),MONTH(DATE(firstApt.FirstVisit))
)z

UNION ALL

SELECT '',''

UNION ALL

SELECT 'PatCount',''

UNION ALL

SELECT '-----------','----------------'

UNION ALL

SELECT z.*
FROM(
SELECT DATE_FORMAT(DATE(apt.AptDateTime),'%M %Y') AS MONTH,
COUNT(apt.AptNum) AS 'Value'
FROM patient p
INNER JOIN appointment apt ON apt.PatNum=p.PatNum
AND apt.AptStatus=2
AND apt.AptDateTime BETWEEN @StartDate AND @EndDate+INTERVAL 1 DAY
GROUP BY DATE_FORMAT(DATE(apt.AptDateTime),'%M %Y')
ORDER BY YEAR(DATE(apt.AptDateTime)),MONTH(DATE(apt.AptDateTime))
)z

UNION ALL

SELECT '',''

UNION ALL

SELECT 'GrosProd',''

UNION ALL

SELECT '-----------','----------------'

UNION ALL

SELECT z.*
FROM(
SELECT DATE_FORMAT(prod.Date,'%D %M %Y') AS MONTH,
FORMAT(ROUND(SUM(prod.Fee),2),2) AS 'Value'
FROM (
SELECT DATE(pl.ProcDate) AS 'Date',
pl.ProcFee*(pl.UnitQty+pl.BaseUnits)-IFNULL(SUM(cp.WriteOff),0) AS 'Fee',
0 AS 'Adj',
0 AS 'InsW'
FROM procedurelog pl
LEFT JOIN claimproc cp ON pl.ProcNum=cp.ProcNum AND cp.Status='7'
WHERE pl.ProcStatus = '2'
AND pl.ProcDate BETWEEN @StartDate AND @EndDate
GROUP BY pl.ProcNum

UNION ALL

SELECT adj.AdjDate AS 'Date',
0 AS 'Fee',
adj.AdjAmt AS 'Adj',
0 AS 'InsW'
FROM adjustment adj
WHERE adj.AdjDate BETWEEN @StartDate AND @EndDate
) prod
GROUP BY DATE_FORMAT(prod.Date,'%M %Y')
ORDER BY YEAR(prod.Date),MONTH(prod.Date)
)z

SELECT z1.*
FROM(
SELECT DATE_FORMAT(prod.Date,'%D %M %Y') AS MONTH,
FORMAT(ROUND(SUM(prod.Fee),2),2) AS 'Value'
FROM (
SELECT DATE(pl.ProcDate) AS 'Date',
pl.ProcFee*(pl.UnitQty+pl.BaseUnits)-IFNULL(SUM(cp.WriteOff),0) AS 'Fee',
0 AS 'Adj',
0 AS 'InsW'
FROM procedurelog pl
LEFT JOIN claimproc cp ON pl.ProcNum=cp.ProcNum AND cp.Status='7'
WHERE pl.ProcStatus = '2'
AND pl.ProcDate BETWEEN @LastStartDate AND @LastEndDate
GROUP BY pl.ProcNum

UNION ALL

SELECT adj.AdjDate AS 'Date',
0 AS 'Fee',
adj.AdjAmt AS 'Adj',
0 AS 'InsW'
FROM adjustment adj
WHERE adj.AdjDate BETWEEN @LastStartDate AND @LastEndDate


) prod
GROUP BY DATE_FORMAT(prod.Date,'%M %Y')
ORDER BY YEAR(prod.Date),MONTH(prod.Date)
)z1;

Hello,

From a quick scan I can see you are missing a "UNION ALL" in this section:
The two select statements will need to have a "UNION ALL" between them as you are doing in the rest of the query.

Thanks, Alex. The next issue I'm facing is that when I run this query through the command line then I'm getting the error below
This error is due to the line below but the same error will happen for other select statements throughout the query
What's the collate command to add not only for the line below but for other select statements which are used akin to print statements.