Modifying Query# 1036

For users or potential users.
Post Reply
Drbee
Posts: 1
Joined: Tue Oct 09, 2018 10:13 am

Modifying Query# 1036

Post by Drbee » Tue Nov 15, 2022 10:52 am

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;

agrasley
Posts: 15
Joined: Mon Jan 03, 2022 11:45 am

Re: Modifying Query# 1036

Post by agrasley » Fri Nov 25, 2022 1:03 pm

Hello,

From a quick scan I can see you are missing a "UNION ALL" in this section:

Code: Select 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
The two select statements will need to have a "UNION ALL" between them as you are doing in the rest of the query.
Alex Grasley
Open Dental Software
http://www.opendental.com

juntas
Posts: 5
Joined: Thu Nov 10, 2022 6:25 am

Re: Modifying Query# 1036

Post by juntas » Mon Nov 28, 2022 8:04 am

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

Code: Select all

ERROR 1270 (HY000) at line 12: Illegal mix of collations (latin1_swedish_ci,NUMERIC), (cp850_general_ci,IMPLICIT), (lati
n1_swedish_ci,IMPLICIT) for operation 'between'
This error is due to the line below but the same error will happen for other select statements throughout the query

Code: Select all

SELECT 'NewPatCount' AS 'Title','' AS 'Value'
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.

Post Reply