few query questions

[babysilvertooth]

Hi all,
Few query questions:
Best report to find out the number of patients seen in a date range. I don't want unique patients, I want total # of appointments. I am looking for past appointments.
Also, any way to find a query for appointments in the future with similar info.

[Tom Zaccaria]

# Count of active patients seen between two dates
SET @pos=0;
SELECT Year(ProcDate) AS Year, Month(ProcDate) AS month,
COUNT(patient.PatNum) AS Patients
from patient, procedurelog
WHERE procedurelog.patnum = patient.patnum
AND patient.patstatus = '0'
AND procedurelog.procstatus = 2
AND procedurelog.procdate > '2022-01-01'
AND procedurelog.procdate < '2022-12-31'
GROUP BY YEAR(ProcDate), Month(ProcDate)
ORDER BY YEAR(ProcDate), Month(ProcDate);


drtmz

[Tom Zaccaria]

Does this help your appointment count in the future?

/*1218 Count of patient appointments for new and exisiting patients per date created in date range. Counts only Scheduled and Completed appointments.*/
SET @Fromdate='2021-12-01',@ToDate='2021-12-31'; /* Enter date range here */

/*--------------------DO NOT MODIFY BELOW THIS LINE--------------------*/
/* Query code written/modified 01/21/2022:ChrisD */
SELECT
LogDateTime AS 'Date Created',
COUNT(CASE WHEN appointment.IsNewPatient=1 THEN appointment.AptNum ELSE NULL END) AS 'NewPatientCount',
COUNT(CASE WHEN appointment.IsNewPatient=0 THEN appointment.AptNum ELSE NULL END) AS 'CurrentPatientCount'
FROM securitylog
INNER JOIN appointment
ON securitylog.FKey = appointment.AptNum
WHERE securitylog.LogDateTime BETWEEN @FromDate AND @ToDate + INTERVAL 1 DAY
AND securitylog.PermType = 25 /*Appointment Creation*/
AND appointment.AptStatus IN (1, 2) /*scheduled and complete*/
GROUP BY DATE(securitylog.LogDateTime)
ORDER BY securitylog.LogDateTime

drtmz