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;
Modifying Query# 1036
Re: Modifying Query# 1036
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.
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
Re: Modifying Query# 1036
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.
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'
Code: Select all
SELECT 'NewPatCount' AS 'Title','' AS 'Value'